I don't know if there are any templates for this, but it’s not difficult to
set it up.
First you need a table of all the people involved, Employees say, with
columns for EmployeeID (which can be an autonumber), FirstName, LastName and
their address details etc.
Next you need a Calendar table. This is simply a table of unique dates over
whatever period you are concerned with. You can create one by serially
filling down a column in Excel and importing it into Access as a table or you
can do it more flexibly with code using the following function This is the
DAO version but I also have an ADO one if you prefer. Either should work:
''''code begins''''
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
''''code ends''''
Paste the function into a standard module in your database and call it, e.g.
from the debug window (press Ctrl+G to open this) by passing the name for the
table, the start and end dates and a list of days of the week by number if
you don't want all days of the week included, so say you only want Mondays to
Fridays for this year and up to the end of 2010 say, you'd call it like so:
MakeCalendar_DAO "WorkdaysCalendar",#01/01/2007#,#12/31/2010#,2,3,4,5,6
If you wanted it to include all days of the week use a zero:
MakeCalendar_DAO "WorkdaysCalendar",#01/01/2007#,#12/31/2008#,0
This should be entered as a single line; it might have broken over two in
your newsgroup reader.
You next need a Roster table to model the many-to-many relationship between
Employees and Calendar. This table will have columns EmployeeID and CalDate
and one or more columns to record when in the day the employee is scheduled
to work, so this could be a text column, TimeOfDay say, with possible values
such as AM, PM or two date/time columns to enter the end and start of the
employee's work session.
For data entry you'd use an Employees form with a subform based on the
Roster table, linked to the parent form on EmployeeID. The subform would
have a combo box bound to the CalDate column with a RowSource listing the
dates in order:
SELECT CalDate FROM WorkdaysCalendar, ORDER BY CalDate;
You can if you wish set its DefaultValue to Date() then it will show the
current date by default in a new record so the user won't have far to scroll
to a nearby date in the list.
You'd also have one or more text boxes on the subform for the time of day
worked or the start and end times.
You could if you wished reverse this arrangement by having a form based on
the WorkdaysCalendar table and a subform, again based on the Roster table, in
it, so that you can then select employees for each day rather than vice
versa. In this case you'd have a combo box in the subform bound to the
EmployeeID field (but showing the employee names) rather than to the CalDate
field.
For reporting you can join the tables in a query. So for a report over a
date range the query would be something like this:
PARAMETERS [Start Date:] DATETIME, [End Date:] DATETIME;
SELECT FirstName, LastName, WorkdaysCalendar.CalDate, TimeOfDay
FROM WorkdaysCalendar INNER JOIN (Employees INNER JOIN Roster
ON Employees.EmployeeID = Roster.EmployeeID)
ON WorkdaysCalendar.Caldate = Roster.CalDate
WHERE WorkdaysCalendar.CalDate BETWEEN
[Start Date:] AND [End Date:];
Group the report based on the query first on CalDate and then order it by
TimeOfDay. Include the CalDate in the CalDate group header and the Firstname
and LastName (and any other employee columns you've included in the query)
along with the TimeOfDay in the detail section. When you open the report
you'll be prompted to enter the start and end dates of the range of dates you
want included in the report.
That might sound rather long winded, but I think I'd probably find it
quicker to do than to describe!
Ken Sheridan
Stafford, England
FLeur said:
Hi all,
And thank you,
I would like to create a roster in Access. it is a fortnightly roster. I
figured if i put there names and address in the database, then i can put a
form in and say that person works mon morn... then i can create a report...
printing out the months roster??
And i would like all this in a template...
I have checked out most of the websites... unfortuatly i only have access
2003..
thank you
