Finding Unmatched Records with Two Different Data Types

[Lori]

After working on this for three weeks I finally realized I have been
trying to find unmatched records (unsuccessfully) based on two
different data types.

Table one is a simple look up table with two fields: Week#
(AutoNumber) & Week (Date)

Table two is a timesheet table with the PPE field pulling from the
above Week field.
I finally realized it's not a date field here - it's a number field.

Is there anything I can do to correct this and get the results I need
which are: What timesheets are missing?

I'm asking this because I'm assuming this (the different field types)
is the reason I'm not getting the results I need.

Thanks for any help!
Lori

 

[Tom Ellison]

Dear Lori:

Can you look at the data and see which timesheets are missing? If so, on
what basis can you tell?

Is it the case that you have a list of employees and a list of weeks, and
you expect a timesheet for every combination?

Tom Ellison

 

[Lori]

Hi Tom

Yes, I can look and tell that some employees haven't turned in
timesheets. I'd like to be able to print a report to hand to them so
they'll know which ones they haven't done. Every employee should have a
timesheet for every week but often fall behind.

 

[Tom Ellison]

Dear Lori:

It sounds very much like what I expected. A cross product of the employee
table with a table of weeks would be the perfect starting point. If it were
my project, would prefer to have starting and ending dates available for
each employee so I could omit those weeks where the person were not an
employee. Also, I would look hard a vacation or sick time to see if I could
eliminate having those weeks come up.

Once you have the matrix of all employees with all weeks, filling in is
relatively simple.

If you want my assistance in coding this, please show me your table names
and the names of the relevant columns as a starting point.

The table of weeks should have two integer columns, year and week.

Tom Ellison