Find Missing Date

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

I have an import that runs weekly that imports time/attendance info from our
time clock. There is a field called "PunchDate" that is imported that shows
the date when they employee punched in and out. If the employee is absent one
day then there will be no record for them that day. What I'm trying to do is
create a function or query that will show any days not punched for a specific
employee. So it'll have to look at this table and find where there is a gap
in any given week. I'll also need to tie it in to my tblHolidays since if
it's a holiday it won't show a punch for that day. I also need to exclude
weekends as well. How would I set this up?
 
My ISP helped me set up my email, Newsgroups, and transfer Addresses,
however none could help me transfer my Local Folders(from Outlook Express to
Vista). Any suggestions?
Thanks
 
Ralph said:
My ISP helped me set up my email, Newsgroups, and transfer Addresses,
however none could help me transfer my Local Folders(from Outlook Express
to Vista). Any suggestions?
Thanks
Click to expand...

Your question may be better answered in a newsgroup appropriate to your
problem. This newsgroup is dedicated to the Microsoft Access database
product.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
I'm in the right newsgroup. The person who responded to my post is in the
wrong group.
 
I would first run a query to find the missing dates. Probably the easiest
way is to have a table with all the dates in a year. That's easily made in
Excel by entering a few dates and dragging it down. You can also write code
to build a recordset to pull all the dates between your start and end date
and fill a temp table. I have some code on my website which counts only
business days, and it could probably be modified to find the missing dates.
Then I'd check to see if those dates were on a weekend or in the holiday
table.

http://www.datastrat.com/Code/GetBusinessDay.txt
 
Are you saying to create a table with all the dates in a year along with
using your code on your website? Or are those two different ways of getting
this info?

What I would want to do is run a query for a specifc month and then have it
tell me which days there were no punches and for which employees.

How hard would it be to modify your code to get this to work?
 
Firstly build a calendar table with the following function:

Public Function MakeCalendar_DAO(strtable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strtable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strtable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strtable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strtable & _
"(CalDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (CalDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(CalDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(CalDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function


To build a calendar table named WorkdaysCalendar of all weekdays from 2008
to 2018 say, call the function like so:

MakeCalendar_DAO "WorkdaysCalendar",#2008-01-01#,#2018-12-31#,2,3,4,5,6

Then delete all holiday dates from the table with:

DELETE *
FROM WorkdaysCalendar
WHERE EXISTS
(SELECT *
FROM tblHolidays
WHERE tblHolidays.HolidayDate = WorkdaysCalendar.CalDate);

You can repeat this periodically as new holidays are added to the holidays
table.

You can then return the unpunched weekday/non-holiday days per employee,
e.g. for employee number 42 this year, with a query like this:

SELECT DISTINCT EmployeeID, CalDate
FROM TimeClock AS TC1, WorkdaysCalendar
WHERE YEAR(CalDate) = 2008
AND EmployeeID = 42
AND NOT EXISTS
(SELECT *
FROM TimeClock As TC2
WHERE TC2.EmployeeID = TC1.EmployeeID
AND TC2.PunchDate = WorkdaysCalendar.CalDate)
ORDER BY CalDate;

Ken Sheridan
Stafford, England
 
What I do is have three tables: a Years table with one row for each year of
interest (in a field named YearNumber), a Months table with 12 rows (1 for
each month, in a field named MonthNumber) and a Days table with 31 rows (1
for each day, in a field named DayNumber).

You can then create a query that uses Cartesian joins to generate each day.

The query I usually use is

SELECT DateSerial(
[YearNumber],
[MonthNumber],
[DayNumber]) AS WhatDate,
Years.YearNumber,
Months.MonthNumber,
Days.DayNumber
FROM Days,Months, Years
WHERE (((IsDate(
[YearNumber] & "-" &
[MonthNumber] & "-" & [DayNumber]))=True))
ORDER BY 1

although John Spencer pointed out to me that you can get by with

SELECT DISTINCT DateSerial(
[YearNumber],
[MonthNumber],
[DayNumber]) AS WhatDate
FROM Days,Months, Years

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Actually, Ken Sheridan's Calendar Function looks really good to create a
table. While I wouldn't have written a function to do that since Excel
dragging is so fast, now that he's written it, it's faster (g) to use than
dragging out and importing a table.

Thanks, Ken

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Douglas J. Steele said:
What I do is have three tables: a Years table with one row for each year
of interest (in a field named YearNumber), a Months table with 12 rows (1
for each month, in a field named MonthNumber) and a Days table with 31
rows (1 for each day, in a field named DayNumber).

You can then create a query that uses Cartesian joins to generate each
day.

The query I usually use is

SELECT DateSerial(
[YearNumber],
[MonthNumber],
[DayNumber]) AS WhatDate,
Years.YearNumber,
Months.MonthNumber,
Days.DayNumber
FROM Days,Months, Years
WHERE (((IsDate(
[YearNumber] & "-" &
[MonthNumber] & "-" & [DayNumber]))=True))
ORDER BY 1

although John Spencer pointed out to me that you can get by with

SELECT DISTINCT DateSerial(
[YearNumber],
[MonthNumber],
[DayNumber]) AS WhatDate
FROM Days,Months, Years

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Arvin Meyer said:
Two different ways.

Modifying my code would be the most elegant way, and the most time
consuming. You can build a table with 10 years of dates in Excel, then
import that table into Access in a matter of 10 minutes or less. My guess
is that the date table would run faster too. It's just kind of clunky
having a table with every date in it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
 
Thank you for creating the code Ken. Greatly appreciated!
Thank you also to Arvin & Doug too for their input. It's always appreciated.
 
Hi Ken

Thanks for this code, just what I'm looking for :-). I have a query though
if that is ok... ?

Sometimes it works and not others?

I've only got as far as creating the calendar routine so far, I use this in
code:

Dim dtStart As Date, dtEnd As Date, msg

dtStart = Format(Me![StartDate], "mm\/dd\/yyyy")
dtEnd = Format(Me![EndDate], "mm\/dd\/yyyy")

MakeCalendar_DAO "tblCalendar", dtStart, dtEnd, 2, 3, 4, 5, 6

dtStart and dtEnd come from my form. If I choose 31/12/09 start date and end
of March as EndDate and press my button which runs the code above, it works
and populates the data correctly in my table. If I choose 04/01/10 though it
doesn't work, there are no errors but the table is empty?

I have tried various dates to see if I can find a logical pattern but so far
it seems random. Any ideas why intermittently it just creates an empty table?

Thanks....
Sue
 
hughess7 said:
Hi Ken

Thanks for this code, just what I'm looking for :-). I have a query though
if that is ok... ?

Sometimes it works and not others?

I've only got as far as creating the calendar routine so far, I use this
in
code:

Dim dtStart As Date, dtEnd As Date, msg

dtStart = Format(Me![StartDate], "mm\/dd\/yyyy")
dtEnd = Format(Me![EndDate], "mm\/dd\/yyyy")

MakeCalendar_DAO "tblCalendar", dtStart, dtEnd, 2, 3, 4, 5, 6

dtStart and dtEnd come from my form. If I choose 31/12/09 start date and
end
of March as EndDate and press my button which runs the code above, it
works
and populates the data correctly in my table. If I choose 04/01/10 though
it
doesn't work, there are no errors but the table is empty?

I have tried various dates to see if I can find a logical pattern but so
far
it seems random. Any ideas why intermittently it just creates an empty
table?

Thanks....
Sue



Ken Sheridan said:
Firstly build a calendar table with the following function:

Public Function MakeCalendar_DAO(strtable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strtable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strtable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strtable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strtable & _
"(CalDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (CalDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(CalDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(CalDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function


To build a calendar table named WorkdaysCalendar of all weekdays from
2008
to 2018 say, call the function like so:

MakeCalendar_DAO "WorkdaysCalendar",#2008-01-01#,#2018-12-31#,2,3,4,5,6

Then delete all holiday dates from the table with:

DELETE *
FROM WorkdaysCalendar
WHERE EXISTS
(SELECT *
FROM tblHolidays
WHERE tblHolidays.HolidayDate = WorkdaysCalendar.CalDate);

You can repeat this periodically as new holidays are added to the
holidays
table.

You can then return the unpunched weekday/non-holiday days per employee,
e.g. for employee number 42 this year, with a query like this:

SELECT DISTINCT EmployeeID, CalDate
FROM TimeClock AS TC1, WorkdaysCalendar
WHERE YEAR(CalDate) = 2008
AND EmployeeID = 42
AND NOT EXISTS
(SELECT *
FROM TimeClock As TC2
WHERE TC2.EmployeeID = TC1.EmployeeID
AND TC2.PunchDate = WorkdaysCalendar.CalDate)
ORDER BY CalDate;

Ken Sheridan
Stafford, England
Click to expand...
Click to expand...
 
Back
Top