Query for date range

[BRZ-Ryan]

I'm working on a count query to count totals for a given date range. The user inputs the Monday date for the given week, and the query should run a count query for Monday-Friday for that week. All is well except for if the person had no entry for a given day, it leaves out that day from the query. I'd like it to still show the day, with 0's in for placeholders.

I created a separate table which I can fill with the given week, if that helps. I was thinking if I can do a FOR EACH or some type of loop where it pulls the date from tblWeekDays and then reads the DATA table for that date. If no date found, return 0's. I could fill in tblWeekDays with the dates when the report is ran via VBA.



This may be tricky because on a missing date it will need to pull the DATA.PersonsName from an adjacent record, if one exists

-Table=tblWeekDays
-Field=DayDate 
Val=1/27/2014
Val=1/28/2014
Val=1/29/2014
Val=1/30/2014
Val=1/31/2014

SELECT Sum(DATA.SoldItemA) AS SumOfA, Sum(DATA.SoldItemB) As SumOfB[etc..]
FROM DATA
WHERE (((DATA.Date) Between [forms]![txtCalendar] And DateAdd("d",5,[forms]![txtCalendar])))
GROUP BY DATA.PersonsName, DATA.EmpID_Number, DATA.Date, DATA.DateRange, DATA.Comments;

[June7]

This requires the same technique described starting with post 23 of your other thread https://www.accessforums.net/queries...17/index2.html

Need a master dataset of all possible date/person pairs.

Need a dataset of all dates. This can be created by query from the data table if there is at least one record for each date, otherwise use a table. Then create dataset of all possible date/person pairs with a query that includes both data sources without a join clause - results in a Cartesian relation where every record from each table joins with every record of the other table.

Join the date/person dataset to the data table by linking on the date and person fields, join type "Include all records from date/person and only those from [data] that match".

[BRZ-Ryan]

Do you know of a good way to accomplish this based on the current table configuration?

This db was created years ago, and the data table has tens of thousands of records. Everything works great with the report, except it has a missing day if the person had no entries for the given week day. When you say a master dataset of all possible date/person pairs, does this mean for each person, I need to have a record their name and the corresponding dates?:

Assuming I only needed to work with 2/25 and 2/16...

Ryan;2/25/14
Ryan;2/26/14
Bill;2/25/14
Bill;2/26/14
Mike;2/25/14
Mike;2/25/14

A table already exists with every users name, but the date part I am not sure how to implement. I guess I could run a query to build this type of table when the report is ran?

[June7]

Think I covered all those points in my previous post.

Yes, code could dynamically generate the date records for specified range. This is using a 'temp' table - temp because records are purged each time process is run. VBA could even process all the data into a temp table to produce the desired final output results. This would involve opening and looping recordsets and using conditional structures.

[BRZ-Ryan]

You're right, and thank you!

Ok I did what you said and it is working like I want. The only thing I need to figure out is how to create this master dataset table for when I run the report. When the report is ran, the user is first prompted for the monday date for when they want the report (it then runs it for Mon-Fri). So I will need to query my people table, for each person write to DayDates table their name and each of the 5 dates. Can you help with this one?

[June7]

You don't have to write people to DayDates table. Beyond creating the all dates dataset with VBA, everything should be possible with Access query objects.

As stated, if there is at least one record in data for every day, then VBA is not needed. The query would be:

SELECT DISTINCT [date field] FROM [data tablename];

However, if you know there are gaps in the dates because no one has a record, then look at VBA to generate the dates table. If you want to complicate the VBA by also writing records for each possible pair, can be done but why not just build queries?

[BRZ-Ryan]

I created a test db creating a days table with a single name 5 times with a day date for each (Ryan;2/3/14,Ryan;2/4/14.. etc), and 4 data records in the data table (all but one day). I joined the fields as you recommended (name from days table to name in data table, date from days table to date from data table) and when I ran the query it was perfect, showing a 0 for the missing day.

I am confused with how to link the fields in the real db. I just looked through the data table and for the most part there are no missing days since multiple people make an entry on a given day--the only exception is if we had a snow day or a holiday. Since this is the case I'd prefer to use a separate dates table.

Your recommendation was to create a temp table with VBA? How would I go about processing this query through VBA?

[BRZ-Ryan]

I put this together and it's not functioning the same as my test table...it still has a missing day. My tblWeekDays table has each reps name 5 times, with Mon-Fri date on each consequtively. Here's what query design created:

FROM tblWeekDays LEFT JOIN DATA ON (tblWeekDays.RepName = DATA.UnderwriterName) AND (tblWeekDays.DayDate = DATA.Date)
WHERE (((DATA.Date) Between [forms]![Uw Totals]![calendar0] And DateAdd("d",5,[forms]![Uw Totals]![calendar0])))
GROUP BY DATA.UnderwriterName, DATA.Badge_Number, DATA.Date, DATA.DateRange, DATA.Comments;

[June7]

So you want 0 records for snow days and holidays and weekends?

The temp table isn't created with VBA, you create the table with necessary fields and it just sits there, waiting.

The VBA procedure would delete records from the temp table and write new records for the given date range. Like:

Dim dteDay As Date
CurrentDb.Execute "DELETE FROM temptablename;"
dteDay = Me.calendar0
While dteDay <= Me.calendar0 + 5
CurrentDb.Execute "INSERT INTO temptablename(Days) VALUES(#" & dteDay & "#)"
dteDay = dteDay + 1
Wend

Not seeing anything wrong with your query although not sure why you have GROUP BY.

[BRZ-Ryan]

I fixed it by removing the WHERE clause, I guess that was confusing it. It's not necessary since my days table will have the dates I am querying.

[June7]

Yes, that would be superfluous if using already restricted dataset, however, would not have thought it would fail.

[BRZ-Ryan]

Ok I think this is solved for the most part. One more thing just to be sure. .since I need the persons name for the missing day entries I will need to include names in my daydates table, correct? So it'll have 5 entries for each person-- each person having all 5 dates being reported on.

[June7]

That's one way to go about it.

I earlier suggested a Cartesian query that includes DayDates and people tables to achieve the same.

[BRZ-Ryan]

How do you create this query?

[June7]

Just pull both tables into the query designer but don't create link, no join clause. See post 2.