Missing Data Queries?

[sweekes283]

I'm looking for a way to report on missing data. I've got a database
that requires daily entry of 81 processes by individuals. I'm looking
for a way to report which processes did not enter data for dates within
a given range.

To make things worse, data is only entered Monday through Saturday, so
I can't just use a query that looks for holes in a series. Though I
could make that work (by excluding Weekday([Date])=1), those queries
all return just one missing date, I'm after all of them.

Another thought is to create a temporary table with all dates within
the users input and use that to determine missing records, but I know
of no way to generate that sort of data. And a table with a list of
all dates seems to clumsy to be the best route.

Lastly, if I assume that on a given day, somebody would enter data,
then I can compare a list of all dates that are in the table to see
which processes do not have data. Though not 100% accurate, I think it
would do the trick.


If anyone has any ideas, I would appreciate it.

 

[[MVP] S.Clark]

Without knowing your table structures, it's tough to give a guess. (Is data
flat file or normalized?)

With VBA, you could step through the data to determine the info you desire,
too.

 

[sweekes283]

Tables are split up as much as was feasible...

Data Table contains RecordDate, ProcessID and RegionID as keys, 10 data
fields and 4 more IDs.

There are then Process tables, Region Tables, and 4 template tables (to
match the other ID's).

I'm after a query that displays All the dates within a given time frame
and the processes (by region) that do not have data entered for those
dates. Weekends and holidays are easy enough to deal with, it's just
building this query that's got me stumped.

I've been looking through a lot of responses here and it looks like the
'clumsy' date table seems to be the best solution. Having trouble
implementing it though.

Thanks again for any input!