Missing dates?

Randy · Aug 1, 2005

Access 2000. I have an employee db. where employee timesheets are entered.
I need a query that will return missing dates or if a timesheet from a
particular date that is missing. I have a field for entering the date
[Currrent_date] and a field where I enter [CID] "First Day" and "Last Day"
This is the employees first day and last day in this office. What is the
best approach...Thanks..Randy

Allen Browne · Aug 2, 2005

Randy, the missing dates have to come from somewhere, so you need a table of
all dates.

If you already have a roster of who should be on when, that would work. Or
you could create a table of all dates, and then possibly remove the public
holidays when noone works.

You can then create a query that:
- Selects all dates from this table in the date range.
- Includes the Employee table (no join, i.e. a cartesian product.)
- Uses a subquery to finds the timesheets that don't exist for the employee
+ date

The WHERE clause of your query will include this kind of thing:
WHERE NOT EXIST (SELECT TimesheeID FROM tblTimeSheet
WHERE tblTimesheet.EmployeeID = tblEmployee.EmployeeID
AND tblTimesheet.WorkDate = tblDate.TheDate)

If subqueries are new, Microsoft has an introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you need to programmatically fill a table with dates, create a table
named (say) "tblDate", with one date/time field named "theDate" as primary
key. Then you can add 10 years of dates like this:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2005# To #12/31/2014#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

Randy · Aug 2, 2005

Allen, I tried your suggestion below but I come up with no result in the
query. Here is a copy of the sql statement. "IDRa" is the timesheet tbl,
primary key is "IDRa_ID". The employee tbl is "EMP" where all the
employees and employee id numbers [EID] are listed. Thanks for your
help...Randy

SELECT tblDate.theDate
FROM tblDate, IDRa
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Allen Browne said:
Randy, the missing dates have to come from somewhere, so you need a table
of all dates.

If you already have a roster of who should be on when, that would work. Or
you could create a table of all dates, and then possibly remove the public
holidays when noone works.

You can then create a query that:
- Selects all dates from this table in the date range.
- Includes the Employee table (no join, i.e. a cartesian product.)
- Uses a subquery to finds the timesheets that don't exist for the
employee + date

The WHERE clause of your query will include this kind of thing:
WHERE NOT EXIST (SELECT TimesheeID FROM tblTimeSheet
WHERE tblTimesheet.EmployeeID = tblEmployee.EmployeeID
AND tblTimesheet.WorkDate = tblDate.TheDate)

If subqueries are new, Microsoft has an introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you need to programmatically fill a table with dates, create a table
named (say) "tblDate", with one date/time field named "theDate" as primary
key. Then you can add 10 years of dates like this:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2005# To #12/31/2014#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:

Access 2000. I have an employee db. where employee timesheets are
entered. I need a query that will return missing dates or if a timesheet
from a particular date that is missing. I have a field for entering the
date [Currrent_date] and a field where I enter [CID] "First Day" and
"Last Day" This is the employees first day and last day in this office.
What is the best approach...Thanks..Randy

Click to expand...

Allen Browne · Aug 3, 2005

The cartesian product needs to be on every combination of date and employee.

Try:
SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:
Allen, I tried your suggestion below but I come up with no result in the
query. Here is a copy of the sql statement. "IDRa" is the timesheet tbl,
primary key is "IDRa_ID". The employee tbl is "EMP" where all the
employees and employee id numbers [EID] are listed. Thanks for your
help...Randy

SELECT tblDate.theDate
FROM tblDate, IDRa
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Allen Browne said:

Randy, the missing dates have to come from somewhere, so you need a table
of all dates.

If you already have a roster of who should be on when, that would work.
Or you could create a table of all dates, and then possibly remove the
public holidays when noone works.

You can then create a query that:
- Selects all dates from this table in the date range.
- Includes the Employee table (no join, i.e. a cartesian product.)
- Uses a subquery to finds the timesheets that don't exist for the
employee + date

The WHERE clause of your query will include this kind of thing:
WHERE NOT EXIST (SELECT TimesheeID FROM tblTimeSheet
WHERE tblTimesheet.EmployeeID = tblEmployee.EmployeeID
AND tblTimesheet.WorkDate = tblDate.TheDate)

If subqueries are new, Microsoft has an introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you need to programmatically fill a table with dates, create a table
named (say) "tblDate", with one date/time field named "theDate" as
primary key. Then you can add 10 years of dates like this:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2005# To #12/31/2014#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

Randy said:

Access 2000. I have an employee db. where employee timesheets are
entered. I need a query that will return missing dates or if a timesheet
from a particular date that is missing. I have a field for entering the
date [Currrent_date] and a field where I enter [CID] "First Day" and
"Last Day" This is the employees first day and last day in this office.
What is the best approach...Thanks..Randy

Click to expand...

Click to expand...

Randy · Aug 3, 2005

I tried this as you suggested. the query takes about 30 seconds to run, then
there is no result. Heres what I have now.

SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ((((SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.theDate))=False));

Allen Browne said:
The cartesian product needs to be on every combination of date and
employee.

Try:
SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:

Allen, I tried your suggestion below but I come up with no result in the
query. Here is a copy of the sql statement. "IDRa" is the timesheet
tbl, primary key is "IDRa_ID". The employee tbl is "EMP" where all the
employees and employee id numbers [EID] are listed. Thanks for your
help...Randy

SELECT tblDate.theDate
FROM tblDate, IDRa
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Allen Browne said:

Randy, the missing dates have to come from somewhere, so you need a
table of all dates.

If you already have a roster of who should be on when, that would work.
Or you could create a table of all dates, and then possibly remove the
public holidays when noone works.

You can then create a query that:
- Selects all dates from this table in the date range.
- Includes the Employee table (no join, i.e. a cartesian product.)
- Uses a subquery to finds the timesheets that don't exist for the
employee + date

The WHERE clause of your query will include this kind of thing:
WHERE NOT EXIST (SELECT TimesheeID FROM tblTimeSheet
WHERE tblTimesheet.EmployeeID = tblEmployee.EmployeeID
AND tblTimesheet.WorkDate = tblDate.TheDate)

If subqueries are new, Microsoft has an introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you need to programmatically fill a table with dates, create a table
named (say) "tblDate", with one date/time field named "theDate" as
primary key. Then you can add 10 years of dates like this:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2005# To #12/31/2014#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

Access 2000. I have an employee db. where employee timesheets are
entered. I need a query that will return missing dates or if a
timesheet from a particular date that is missing. I have a field for
entering the date [Currrent_date] and a field where I enter [CID]
"First Day" and "Last Day" This is the employees first day and last day
in this office. What is the best approach...Thanks..Randy

Click to expand...

Click to expand...

Allen Browne · Aug 4, 2005

You dropped the EXISTS from the start of the WHERE clause.

You probably want to limit the date range in the main query to just one
period as well. This will dramatically decrease the number of records Access
has to process. For eample, if you have 2000 dates (5+ years) and 100
employees, the Cartesian product generates 200,000 records, whereas if you
limit it to the dates for last week it has 700 records to process. And of
course the subquery has to run for each one, so that magnifies the
difference.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:
I tried this as you suggested. the query takes about 30 seconds to run,
then there is no result. Heres what I have now.

SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ((((SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.theDate))=False));

Allen Browne said:

The cartesian product needs to be on every combination of date and
employee.

Try:
SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ...

Randy said:

Allen, I tried your suggestion below but I come up with no result in the
query. Here is a copy of the sql statement. "IDRa" is the timesheet
tbl, primary key is "IDRa_ID". The employee tbl is "EMP" where all the
employees and employee id numbers [EID] are listed. Thanks for your
help...Randy

SELECT tblDate.theDate
FROM tblDate, IDRa
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Randy, the missing dates have to come from somewhere, so you need a
table of all dates.

If you already have a roster of who should be on when, that would work.
Or you could create a table of all dates, and then possibly remove the
public holidays when noone works.

You can then create a query that:
- Selects all dates from this table in the date range.
- Includes the Employee table (no join, i.e. a cartesian product.)
- Uses a subquery to finds the timesheets that don't exist for the
employee + date

The WHERE clause of your query will include this kind of thing:
WHERE NOT EXIST (SELECT TimesheeID FROM tblTimeSheet
WHERE tblTimesheet.EmployeeID = tblEmployee.EmployeeID
AND tblTimesheet.WorkDate = tblDate.TheDate)

If subqueries are new, Microsoft has an introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you need to programmatically fill a table with dates, create a table
named (say) "tblDate", with one date/time field named "theDate" as
primary key. Then you can add 10 years of dates like this:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2005# To #12/31/2014#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

Access 2000. I have an employee db. where employee timesheets are
entered. I need a query that will return missing dates or if a
timesheet from a particular date that is missing. I have a field for
entering the date [Currrent_date] and a field where I enter [CID]
"First Day" and "Last Day" This is the employees first day and last
day in this office. What is the best approach...Thanks..Randy

Click to expand...

Click to expand...

Randy · Aug 4, 2005

I get the header of "theDate" and "EID" but with no results. I should
have a result of 7/26/05 and EID of 2045 but it is blank. Heres a copy of
the code..Thanks..Randy

SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Allen Browne said:
You dropped the EXISTS from the start of the WHERE clause.

You probably want to limit the date range in the main query to just one
period as well. This will dramatically decrease the number of records
Access has to process. For eample, if you have 2000 dates (5+ years) and
100 employees, the Cartesian product generates 200,000 records, whereas if
you limit it to the dates for last week it has 700 records to process. And
of course the subquery has to run for each one, so that magnifies the
difference.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:

I tried this as you suggested. the query takes about 30 seconds to run,
then there is no result. Heres what I have now.

SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ((((SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.theDate))=False));

Allen Browne said:

The cartesian product needs to be on every combination of date and
employee.

Try:
SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ...

Allen, I tried your suggestion below but I come up with no result in
the query. Here is a copy of the sql statement. "IDRa" is the
timesheet tbl, primary key is "IDRa_ID". The employee tbl is "EMP"
where all the employees and employee id numbers [EID] are listed.
Thanks for your help...Randy

SELECT tblDate.theDate
FROM tblDate, IDRa
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Randy, the missing dates have to come from somewhere, so you need a
table of all dates.

If you already have a roster of who should be on when, that would
work. Or you could create a table of all dates, and then possibly
remove the public holidays when noone works.

You can then create a query that:
- Selects all dates from this table in the date range.
- Includes the Employee table (no join, i.e. a cartesian product.)
- Uses a subquery to finds the timesheets that don't exist for the
employee + date

The WHERE clause of your query will include this kind of thing:
WHERE NOT EXIST (SELECT TimesheeID FROM tblTimeSheet
WHERE tblTimesheet.EmployeeID = tblEmployee.EmployeeID
AND tblTimesheet.WorkDate = tblDate.TheDate)

If subqueries are new, Microsoft has an introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you need to programmatically fill a table with dates, create a
table named (say) "tblDate", with one date/time field named "theDate"
as primary key. Then you can add 10 years of dates like this:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2005# To #12/31/2014#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

Access 2000. I have an employee db. where employee timesheets are
entered. I need a query that will return missing dates or if a
timesheet from a particular date that is missing. I have a field for
entering the date [Currrent_date] and a field where I enter [CID]
"First Day" and "Last Day" This is the employees first day and last
day in this office. What is the best approach...Thanks..Randy

Click to expand...

Click to expand...

Allen Browne · Aug 5, 2005

Randy, I can't see what's wrong with that. I'm working on the assumptions
that:
a) The fields in the different tables are of the same data type and siz
b) The date fields have not time component stored in them.
c) EMP and IDRa are tables (not queries) so Access can't get the data types
wrong.

Here's another approach:
1. Create the query containing just:
SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP;
Save.

2. Use the unmatched query wizard to find the record in that query that are
not in IDRa.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Randy said:
I get the header of "theDate" and "EID" but with no results. I should
have a result of 7/26/05 and EID of 2045 but it is blank. Heres a copy of
the code..Thanks..Randy

SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Allen Browne said:

You dropped the EXISTS from the start of the WHERE clause.

You probably want to limit the date range in the main query to just one
period as well. This will dramatically decrease the number of records
Access has to process. For eample, if you have 2000 dates (5+ years) and
100 employees, the Cartesian product generates 200,000 records, whereas
if you limit it to the dates for last week it has 700 records to process.
And of course the subquery has to run for each one, so that magnifies the
difference.

Randy said:

I tried this as you suggested. the query takes about 30 seconds to run,
then there is no result. Heres what I have now.

SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ((((SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.theDate))=False));

The cartesian product needs to be on every combination of date and
employee.

Try:
SELECT tblDate.theDate, EMP.EID
FROM tblDate, EMP
WHERE ...

Allen, I tried your suggestion below but I come up with no result in
the query. Here is a copy of the sql statement. "IDRa" is the
timesheet tbl, primary key is "IDRa_ID". The employee tbl is "EMP"
where all the employees and employee id numbers [EID] are listed.
Thanks for your help...Randy

SELECT tblDate.theDate
FROM tblDate, IDRa
WHERE NOT EXIST (SELECT IDRa_ID FROM IDRa
WHERE IDRa.EID = EMP.EID
AND IDRa.Current_Date = tblDate.TheDate);

Randy, the missing dates have to come from somewhere, so you need a
table of all dates.

If you already have a roster of who should be on when, that would
work. Or you could create a table of all dates, and then possibly
remove the public holidays when noone works.

You can then create a query that:
- Selects all dates from this table in the date range.
- Includes the Employee table (no join, i.e. a cartesian product.)
- Uses a subquery to finds the timesheets that don't exist for the
employee + date

The WHERE clause of your query will include this kind of thing:
WHERE NOT EXIST (SELECT TimesheeID FROM tblTimeSheet
WHERE tblTimesheet.EmployeeID = tblEmployee.EmployeeID
AND tblTimesheet.WorkDate = tblDate.TheDate)

If subqueries are new, Microsoft has an introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

If you need to programmatically fill a table with dates, create a
table named (say) "tblDate", with one date/time field named "theDate"
as primary key. Then you can add 10 years of dates like this:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2005# To #12/31/2014#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

Access 2000. I have an employee db. where employee timesheets are
entered. I need a query that will return missing dates or if a
timesheet from a particular date that is missing. I have a field
for entering the date [Currrent_date] and a field where I enter
[CID] "First Day" and "Last Day" This is the employees first day and
last day in this office. What is the best approach...Thanks..Randy

Click to expand...

Click to expand...

Using Between in queries	4	Aug 16, 2005
Help with query criteria. 2nd posting	8	Sep 5, 2005
Unmatched in two fields	1	Sep 23, 2008
Find missing or duplicate records by date	1	Mar 9, 2006
Training schedule -need qry to return all employees for that month	1	Jun 26, 2008
dates in queries	3	Oct 7, 2009
query for Dates between today and 30 days back	4	Dec 4, 2007
Create Temporary Tables in a SQL Statement?	9	Oct 21, 2006

Missing dates?

Randy

Allen Browne

Randy

Allen Browne

Randy

Allen Browne

Randy

Allen Browne

Ask a Question

Similar Threads