Include non-existant entries in report

[charles.kendricks]

I'm creating a simple timesheet app which uses a form to collect
employees hours and store them in a table. The table only stores data
for the days that the client actually worked. My problem is that I
want to generate a report (weekly time sheet) that shows a record for
every workday. How do I generate an entry for the employee in the
report for which there is no entry in the dependant table or query?

 

[Guest]

Have you tried setting a default value of 0 [zero] for the hour fields? Or
would that not work?

 

[Douglas J. Steele]

Unfortunately, Access isn't omniscient: it can only report on data that's
there.

You can try creating a query that uses a Right Join to connect your existing
table to a table containing all the workdays so that a row with a Null value
will appear for those rows that don't actually exist in your table.

 

[charles.kendricks]

Unfortunately, Access isn't omniscient: it can only report on data that's
there.

You can try creating a query that uses a Right Join to connect your existing
table to a table containing all the workdays so that a row with a Null value
will appear for those rows that don't actually exist in your table.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)






- Show quoted text -

Of course..I don't know why I didn't think of utilizing a blank table
of dates and doing an outer join....Thanks Douglas

 

[Guest]

This is, as Doug suggests, one of the many situations where an auxiliary
'calendar' table comes in useful (there are many other uses such as
calculating the number of workdays between dates, excluding weekdays and
public holidays, the latter recorded in a Boolean column in the table). A
simple way of creating a sequential calendar table is to sequentially fill
down a column in Excel and then import it into Access as a table, but the
following function is more flexible, allowing you to specify which days of
the week to include. Note that it uses ADOX so be sure you have a reference
to the Microsoft Extensions for DDL and Security (Tools | References on the
VBA toolbar)

Public Function MakeCalendar(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 cmd As ADODB.Command
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

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

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
cmd.CommandText = strSQL
cmd.Execute

' refresh database window
Application.RefreshDatabaseWindow

' refresh catalog
cat.Tables.Refresh

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

cmd.CommandText = strSQL
cmd.Execute
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
cmd.CommandText = strSQL
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
cmd.CommandText = strSQL
cmd.Execute
End If
Next varDay
Next dtmDate
End If

Set cmd = Nothing

End Function

So to create a table WorkdaysCalendar with dates for Monday-Friday from the
start of this year to the end of 2010 say you'd call it like so:

MakeCalendar("WorkdaysCalendar",#01/01/2007#, #12/31/2010#, 2,3,4,5,6

To record public holidays or concessionary non-working weekdays you'd add a
Boolean (Yes/No) column to the table, PubHol say.

You can then outer join your timesheet table on its date column to the
calDate column from WokdaysCalendar, e.g. for a week, excluding any public
holidays, the start date of the week being entered as a parameter:

PARAMETERS [Week starts:] DATETIME;
SELECT CalDate, EmployeeID, HoursWorked
FROM WorkdaysCalendar LEFT JOIN Timesheet
ON TimeSheet.DateWorked = WorkdaysCalendar.calDate
WHERE NOT PubHol
AND Caldate BETWEEN [Week starts:] AND [Week starts:]+6;

Note that its prudent to declare date time parameters as otherwise a date
entered in short date format could be interpreted as an arithmetical
expression and give the wrong result.

Ken Sheridan
Stafford, England

 

[Dirk Goldgar]

Of course..I don't know why I didn't think of utilizing a blank table
of dates and doing an outer join....Thanks Douglas

You don't even need a table of dates. You just need a table with
records numbered 0 to 6 (for a 7-day period), and a function expression
that returns the date of the first day of the week you're interested in.
I don't remember offhand what that expression is, but it's been posted
many times.

Then you can have a query that selects all the records from the numbered
table and returns a calculated field that is computed by adding the
number field to the first-day date; e.g.,

qryWeekDates
-------------------
SELECT FirstDay(Date())+[DayNumber] As WeekDate
FROM DayNumbers

With that query defined, you can outer join your timesheet table to
qryWeekDates to get the effect you want.