Find 'missing' records

[Malc]

I have a database which holds timesheet data for a group
of people working on several projects.

There is a Projects table (code, name, etc.), a User table
(id, name, etc.) and a Timesheet table which references
the user id, the project code and the days spent on a
project by a user for a particular week, using Saturday's
date as week ending.

What I would like to do is to query the timesheet to find
if any records are missing, i.e. details were not entered
for a user for a particular week, and print out that users
details for rectification.

I hope this makes sense and thanks in advance.

Malc

 

[[MVP] S. Clark]

You'll need to post the structure of the timesheet table to get an accurate
answer.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[malc]

As requested the structure of the timesheet table, hope
this is what you wanted.

Malc

Field Name Data Type Desc
----------------------------------------------------------
idnum Text Users ID number
week Date/Time week ending the Saturday
projcode Text code of project worked on
location Text UK/EC/nonEC
chargeable Text Yes/No
activity Text type of work done
sun Number quarters of day worked
mon Number
tues Number
wed Number
thur Number
fri Number
sat Number
totdays Number total days for week

The idnum is linked to the ID num in the User table and
the projcode is linked to the projcode in the Projects
table)

 

[[MVP] S. Clark]

I'm going to refresh everyone's memory, in that this is your original
request:
"What I would like to do is to query the timesheet to find if any records
are missing, i.e. details were not entered for a user for a particular week"

I translate this to mean either,
1. Find missing week numbers(1-52) for each Employee idnum
2. Find missing day values within each week value
3. Both.

If the answer is #1, then create a table that has all of the possible week
numbers. Perform an outer join to find all of the unmatched values from the
weeks table to the data table, the hitch being that you'll need to do this
per employee.

If the answer is #2, then I would create a VBA proc that steps through and
finds any unwritten values. To be honest, if this is the answer, then I
would restructure the table so that you simply record the date and hours,
and not the Sunday through Saturday values.

If the answer is #3, then you've got lots of work to do. Good luck.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-