How to create "sequence" query in access?

[Glenn]

hello all,

I'm writing a timesheet application using Access 2000. One of the
requirements is that the users are forced to enter complete timesheets,
meaning 8 hours every workday.

example (simplified) 'timesheet'

19 nov, glenn, 8h
20 nov, glenn, 8h
24 nov, glenn, 4h
26 nov, glenn, 8h
27 nov, glenn, 8h

the aim is to get a list with following data:
21 nov, glenn, 8h
24 nov, glenn, 4h

aka the unjustified absence table

I can easily check if an ENTERED day is complete, because a record
exists with that datestamp.

The problem is finding dates for which NO data has been entered.

I would like to use as much as possible a query-based approach instead
of resorting completely to VBA.

A solution could be if it were able to create "sequence" queries in
Access. A query that creates all values in between certain start and end
values.
In this case a query 'Calendar' that would yield records
27 nov
26 nov
25 nov
24 nov
....
19 nov

The dates which totally lack in the timesheet could then be found by
SELECT * FROM Calendar LEFT JOIN Timesheet
ON Calendar.date = Timesheet.date
WHERE Timesheet.Date is NULL

I can of course create a temporary table "Calendar', but then all the
trouble cleaning that up again I rather miss.

Suggestions are welcome
regards
glenn

 

[John Vinson]

I can of course create a temporary table "Calendar', but then all the
trouble cleaning that up again I rather miss.

Why not create a permanent table Calendar with dates from now for the
next ten years? 3653 records, each with a single 4-byte field: tiny.

Create it in Excel, the 'fill down' feature will let you build it very
easily.

 

[-]

Why not create a permanent table Calendar with dates from now for the
next ten years? 3653 records, each with a single 4-byte field: tiny.

Create it in Excel, the 'fill down' feature will let you build it very
easily.

I have to admit that will be the easiest.
after posting the message, I retried google with a flash of inspiration
(= "access query missing data"). Creating the table came out as being
the only viable solution.

thank you
glenn