How to check for missing required daily entries (records)

[lmac]

I have a table called EmployeeDailyClockin where user enters their
daily clock-in and clock out times along with other info for their work
day. Users are required to have a daily entry for each Monday-Friday of
the work week. Currently, I run a report for each user, look for
missing daily entries, and let user know they have missing date entries
that they need to enter. How can I automate this so that I can run a
query or something that will check each user's entries to see if any
Monday-Friday dates are missing?

I created a 2nd table called RequiredEntryDates that has each required
entry date for 2006 (206 records):

Day..............Date
Monday.........01/02/06
Tuesday........01/03/06
Wednesday....01/04/06
Thursday........etc
Friday............etc
Monday..........01/09/06
Tuesday.........etc

I tried to use a "Find Unmatched Query" to compare the
RequiredEntryDates table to the EmployeeDailyClockin table to check for
missing dates but it is not working.

Any suggestions on how to make this work?

 

[Allen Browne]

The dates have to come from somewhere, so create a table that contains all
the workdays. It will have one Date/Time field named TheDate, marked as
primary key. Save the table with the name tblDate. If you wish, use the code
at the end of this post to populate the table with all the weekdays, and
then delete the entries for dates you do not operate (such as Christmas
day.)

Now, I presume you already have:
- an Employee table with an EmployeeID field, and
- an EmployeeDailyClockin table, with fields:
EmployeeID
WorkDate
...

Create a query that uses your Employee table and tblDate. There should be no
line joining these two tables in the upper pane of query design. Drag the
EmployeeID field and the TheDate field into the grid. Save the query. It
gives you every possible combination of employee and date (called a
Cartesian Product.)

You can now use the Unmatched Query Wizard to find the records in this table
that do not have a match in the EmployeeDailyClockin table.

This is the code to populate your tblDate will all dates except weekends:
Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
If Weekday(dt, vbMonday) <= 5 Then
.AddNew
!TheDate = dt
.Update
End If
Next
End With
rs.Close
Set rs = Nothing
End Function

 

[George]

Dear Allen

Found your code very useful for my needs.

I have a small question. I have created a command button on a form and on
the Click event I used = MakeDates(), which gives me an error message. How
can I overcome this?

Thank you in advance


Ο χÏήστης "Allen Browne" έγγÏαψε:

 

[Allen Browne]

Did you create the table named tblDate, with the date/time field named
TheDate?

I assume you have posted this code into a standard module in Access. In the
code window, choose Compile from the Debug menu to make sure Access
undertands it.

When it compiles okay, open the Immediate Window (Ctrl+G) and enter:
? MakeDates(#1/1/2006#, #12/31/2006#)
or whatever dates you want to cover. If it executes okay, you can see the
dates in the table.

If it fails, please post the error number and message.

 

[lmac]

Thanks for the reply Allen. Since I had already created the
RequiredWorkDates table that had each Monday-Friday date for 2006, I
didn't have to use your MakeDates code. So, I followed your steps and
everything works fine except when I run the MissingDates query, right
now it shows all missing dates as 03/27/06-12/29/06 (it is somehow
skipping 03/23-03/24). I have been playing around with both query
criteria to get it to only look at the current month's worth of dates
but cannot get it to work. I don't want the report showing missing
dates earlier than 03/01/06 or greater than the current date. I can't
seem to get this to work when I enter any date criteria. What am I
doing wrong???

 

[Allen Browne]

In the "unmatched" query, put the limiting date criteria under the date
field form the table of dates, not the field in your EmployClockin table.

The criteria row will read:
Between #3/1/2006# And Date()

 

[George]

Thank you so much Allen,

It worked just fine.



Ο χÏήστης "Allen Browne" έγγÏαψε:

 

[lmac]

Allen,

That is the exact date criteria I used in the "unmatched" query under
the RequiredDate column:

The unmatched results query design looks like this:

Field:.....Employee........................RequiredDate...............................WorkDate
Table:....qryRequiredWorkDates.....qryRequiredWorkDates..................tblEmployeeDailyClockin
Sort:
Show......Yes................................Yes..............................................No
Criteria:.........................................Between #3/1/2006#
And Date()......Is Null
or:

When I run the query, I get no records at all even though there are
missing dates from 03/01/06-03/22/06. When I created the query, the Is
Null was automatically in the criteria under WorkDate. That is correct
?right? because I'm looking for missing dates in the main Employee
Daily Clock-in table that do not match the Required Work Dates query
that has each employee with each required work date (Cartesian Product)?

 

[lmac]

Allen,
That is the exact date criteria I used in the "unmatched" query under
the RequiredDate column:
The unmatched results query design looks like this:
Field:.....Employee........................RequiredDate............................WorkDate

Table:....qryRequiredWorkDates.....qryRequiredWorkDates...............tblEmployeeDailyClockin

Sort:
Show......Yes................................Yes...........................................No

Criteria:.........................................Between
#3/1/2006#...................And Date()......Is Null
or:
When I run the query, I get no records at all even though there are
missing
dates from 03/01/06-03/22/06. When I created the query, the Is
Null was automatically in the criteria under WorkDate. That is correct

?right? because I'm looking for missing dates in the main Employee
Daily Clock-in table that do not match the Required Work Dates query
that has each employee with each required work date (Cartesian
Product)?

 

[lmac]

Allen,
That is the exact date criteria I used in the "unmatched" query
under
the RequiredDate column:

The unmatched results query design looks like this:

Field:.....Employee................RequiredDate..............WorkDate
Table:....qryRequiredWorkDates.....qryRequiredWorkDates.....tblEmployeeDailyClockin
Sort:
Show:.....Yes......................Yes.......................No
Criteria:...................Between #3/1/2006# And Date()..Is Null
or:

When I run the query, I get no records at all even though there are
missing dates from 03/01/06-03/22/06. When I created the query, the
Is
Null was automatically in the criteria under WorkDate. That is
correct
right because I'm looking for missing dates in the main Employee
Daily Clock-in table that do not match the Required Work Dates query
that has each employee with each required work date (Cartesian
Product)?

 

[lmac]

Allen,
That is the exact date criteria I used in the "unmatched" query
under the RequiredDate column:

The unmatched results query design looks like this:

Field:.....Employee................RequiredDate..............WorkDate
Table:....qryRequiredWorkDates.....qryRequiredWorkDates.....tblEmployeeDail­yClockin
Sort:
Show:.....Yes......................Yes.......................No
Criteria:...................Between #3/1/2006# And Date()..Is Null
or:

When I run the query, I get no records at all even though there are
misisng dates from 3/1-3/22. When I created the query, the Is Null was
automatically in the criteria under WorkDate. This is correct right
because I'm looking for missing dates in the main Employee Daily
Clock-in table that do not match the Required Work Dates query that has
each employee with each required work date (Cartesian Product)?