I need to build a database and am coming against a brick wall trying to
figure out how to set it up.
It tracks 4 clients at a group home. We have to account for every 15
minutes, the staff to client ratio. We need to setup default values for m-f
and different for the weekends. And the number of staff on duty for each of
those 15 minute increments. I need this for every day for the entire year. My
biggest problem with this is how to set it up so that the date/time/client is
not duplicated.Any suggestions anyone can give me would be really helpful
I'd suggest the following tables:
TimePeriods
Period Date/Time
Staff
StaffID
LastName
FirstName
<etc>
Clients
ClientID
LastName
FirstName
<other useful info about the client>
StaffRota
StaffID
StartTime
EndTime
ClientRota
ClientID
StartTime
EndTime
You can use the following (untested, air code) VBA function to fill in
the TimePeriods table:
Public Sub FillTimePeriods(Yearno As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vTime As Date
Dim iDay As Integer
Dim iMinute As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("TimePeriods", vbOpenDynaset)
' step through each day of the year
For iDay = 1 To DatePart("y", DateSerial(Yearno, 12, 31)
' step through each quarter-hour in the day
For iMinute = 0 To 1440 Step 15 ' 1440 minutes in a day
vTime = DateAdd("n", iMinute, DateSerial(Yearno, 1, iDay))
rs.AddNew
rs!TimePeriod = vTime
rs.Update
Next iMinute
Next iDay
End Sub
Copy the function into a new Module; save it as basTime (any name
other than FillTimePeriods actually); and type
FillTimePeriods(2004)
in the Immediate window to load up all timesteps for 2004 (or any
chosen year).
Fill Staff with the identities of your staff members; and Clients
likewise.
StaffRota and ClientRota would be filled using a Form; you would put
in the time that the staff member came on shift in StartTime and the
time they went off in EndTime. Similarly for ClientRota, you'ld put
the date and time the client came under care in StartTime, and the
time they left the home in EndTime; if they're fulltime residents,
this might be #1/1/2004 00:00:00# and #12/31/2004 23:45:00#
respectively.
You can then set up a Query to calculate the staff to client ratio at
any time by using a "non equi join": e.g. to count staff on site at
any TimePeriod you could use
SELECT Count(*)
FROM StaffRota
INNER JOIN TimePeriods
ON TimePeriods.TimePeriod >= StaffRota.StartTime
AND TimePeriods.TimePeriod < StaffRota.EndTime
GROUP BY TimePeriod;
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps