Matching date in two tables

[Secret Squirrel]

I have a table where I track my on-time deliveries and another table where I
have a list of all the work days for a particular month. What I want to do is
create a query that will list all the days of the month from "tblAugust" and
show a count of all the deliveries from "tblDeliveries" for each day. The
problem is when I link these two tables together in a query it only shows the
days that had deliveries. How can I get it to show all the days and just put
a "0" for the days that had no deliveries?

tblAugust fields:

Month
Day

tblDeliveries:

ShipDate

The above are the fields I'm using for the query.

 

[Pat Hartman \(MVP\)]

You need to use a left join. However, you should be using a single table
with 31 rows rather than a table for each month. With a table for each
month, you would need 12 queries. You can add selection criteria to limit
the number of days to 28-31 depending on the month.

Where DayNum <= Day(LstDayMnth(Date()))

One of my date functions:
Public Function LstDayMnth(InDate As Date) As Date
LstDayMnth = DateSerial(Year(InDate), Month(InDate) + 1, 0)
End Function

If you want to run the query for any time period except the current month,
you will need to use a date parameter rather than the Date() function.

 

[Secret Squirrel]

Here's what I created for my query. How would I add your functionality to it?
I do have a single table that will have all the days of the year in it so I
can just query off the one table.

SELECT tblAugust.Day, qryLeadTimeTotalDeliveriesC3.VARIANCE,
qryLeadTimeTotalDeliveriesC3.MONTHCOUNT
FROM tblAugust LEFT JOIN qryLeadTimeTotalDeliveriesC3 ON tblAugust.Day =
qryLeadTimeTotalDeliveriesC3.FLSHIPDATE
GROUP BY tblAugust.Day, qryLeadTimeTotalDeliveriesC3.VARIANCE,
qryLeadTimeTotalDeliveriesC3.MONTHCOUNT
HAVING (((qryLeadTimeTotalDeliveriesC3.MONTHCOUNT)="August"));

When I run it I want it to list all the days of August from tblAugust and
then have it count all the variances for each day in the month of August. And
the days that have no variances I want it to show a "0". But the way I have
it set up it only lists the days that have a variance. I am using the left
join.

 

[Pat Hartman \(MVP\)]

I would break this into two queries.

query1:
Select VarianceDate, Day(VarianceDate) as MonthDay , Sum(VarianceAmt) as
SumOfVarianceAmt
From YourTable
Where VarianceDate Between [Enter start date] and [Enter end date]
Group By VarianceDate, Day(VarianceDate) as MonthDay ;
query2:
Select yourMonthTable.DayOfMonth, Nz(query1SumOfVarianceAmt,0) as
VarianceAmt
From yourMonthTable Left join query1 On query1.MonthDay =
yourMonthTable.DayOfMonth
Where yourMonthTable.DayOfMonth <= Day(LstDayMnth(query1.VarianceDate)

query1 summarizes the variance data. Then query2 joins the summarized data
to a table with 31 records and brings back a row for each day even if there
is no variance for the day. The Nz() function will convert nulls to 0 so
that the day will show 0 rather than nothing.