how to build weekly report even when no data exist for a week

[dunagh]

Hello,
I am trying to figure out how to build a weekly report to count the number of computers that have entered a defined process. I have four processes and I need the report to show counts for each process even if it's zero. The report is supposed to show the history of each process and not just the current week. Is this possible to do in a single query? Or do I have to make multiple queries?

I can't post the db but I can tell you that I have a table that holds the history of each workstation including the workstationName, date of the record, the phase of the workstation, and the status of the phase. Another table holds the phase codes and phase statuses for lookup purposes. Attached is an example of the report I am trying to build.

Thanks.

 

[plog]

A database can only report on data it contains. It doesn't see gaps in its data and fill them in.

With that in mind, this is how you achieve your report: you create a table to hold all the dates you want to report on:

ReportingPeriods
ReportDate
3/7/2013
3/14/2013
3/21/2013

I assume you have a query built already which produced that .pdf. You take that query and use it in another query along with the above ReportingPeriods table I described. Create a LEFT JOIN from the ReportingPeriods (show all from ReportingPeriods) into the query you have and then use this new query as the query to base any reports off of. It will have at least one record for every row in ReportingPeriods.

 

[dunagh]

Thanks for the answer plog. I was thinking this to be the case but hopping that there was some function builtin to Access that I didn't know about. I guess they will just have to live without having the zeros.