missing date query

[erlyn]

I've got a query that lists the number of records opened
each week (week starts on Monday). I'd like to "fill in"
the empty weeks when no activity occured for statistical
information.

Does anyone know how to do this? I was thinking of
creating another table with the dates of each Monday and
comparing the two queries, but I can't seem to figure out
that either...

Please help!
Thanks

 

[John Vinson]

Does anyone know how to do this? I was thinking of
creating another table with the dates of each Monday and
comparing the two queries, but I can't seem to figure out
that either...

You're on the right track. If your main table has a record for each
Monday date (or no record at all for that week), simply join the
AllMondays table to your table, joining by the date field; select the
Join line in the query and choose the option "Show all records in
AllMondays and matching records in <your table>".

Timesaver hint: to populate your Monday table, use Excel. Create a
spreadsheet page, type in the first Monday date you want to use, and
use the "Fill Down" feature to add as many dates as you want; then use
File... Get External Data... Import in Access to import the
spreadsheet to a table. Set the datefield as the Primary Key.

All Mondays... shudder!!!! I've never been one to say 'Thank God it's
Monday' but I've known people who do!