Query for date range

[BRZ-Ryan]

So you're telling me I could leave the query as it was, add in the table with just three 5 dates and people table, and this would make it work add I'm trying to get it to work?

[June7]

Leave which query 'as it was'?

I am suggesting 3 parts to this process.

1. generate a dataset of all dates, either by DISTINCT query or by code writing to a temp table

2. generate a dataset of all possible date/person pairs, either by Cartesian query or VBA code writing to a temp table

3. generate a dataset of all date/person pairs and other data desired from the data table, either by query or VBA code writing to a temp table

[BRZ-Ryan]

As it was meaning it showing nothing for if an employee did not make an entry fire a given day, as noted in post #1.

If there's no real performance benefit I may leave it as is bc I'm confused when you say to create a dataset of all people/date combinations. The only negative to the way I'm doing it now is if I have 50 employees, the query may be slow since its writing 250 records. If you care to explain more I'm all ears.

[June7]

If you want the report to show a record for each person for each day and have it show 0 if there is no record in the data table, then need a dataset of all possible date and person combinations. Every date must be paired with every person.

Then join that dataset to the data table (join type "Include all records from dates/people ...") and where a person has no record for a date in the data table, the data table fields will be blank.

Don't know how to be more explicit with instructions. Did you even attempt the queries as described? Sometimes seeing is understanding.

[BRZ-Ryan]

I guess I don't understand the lingo entirely. When you say create a dataset of all dates/people then join it, do you mean a separate query or what? And would this query be a simple Select * From Dates, People;?? Remember, I'm still learning and thanks again for your help.

[June7]

The 3 steps I outlined involve 3 datasets, each is either a table or a query, the latter two use the dataset produced in the step before them.

So, yes, the dataset of all possible date/person pairs (step 2) is a simple SELECT query. Use the query builder as described.

[BRZ-Ryan]

I completely understand now. .

Is it possible to do this with a single query via nesting? Or is it better to create a query object for all names/times, then query from that from my report query?

[June7]

Can nest if you want. Could build the SQL directly in the report RecordSource property. Can't say it's better either way.

The all names/times in step 2 not what the report will be based on.

Step 3 is the final dataset for report.

[BRZ-Ryan]

I think it's done, and in a single query. I just need to run a query when the report button is clicked to populate tblDay with the seleted date and 4 days after. Thanks again!

tblDay = Table with the 5 dates
tblPPL = Table of names
tblData = self explanatory

SELECT PplDay.RepName, PplDay.TheDate, Nz(tblData.Item1,0), Nz(tblData.Item2,0), Nz(tblData.Item3,0)
FROM (SELECT tblDay.[TheDate], tblPPL.[RepName] FROM tblDay, tblPPL) AS PplDay 
LEFT JOIN tblData ON (PplDay.RepName = tblData.[RepName]) AND (PplDay.TheDate = tblData.[TheDate])
GROUP BY PplDay.RepName, PplDay.TheDate, Nz(tblData.Item1,0), Nz(tblData.Item2,0), Nz(tblData.Item3,0);

In my test, I populated data for Ryan only for all days except 1/30/14.