First a little background theory might help. Relational databases like
Access represent each 'entity type' as a table and each 'attribute type' of
the entity type as a column in the table. The relationships between entity
types are achieved by means of values in one table matching values in
another. The columns which hold these values are known as 'keys', and where
two tables are related one-to-many the 'primary key' of the referenced
(One-side) table is referenced by a 'foreign key' in the referencing table.
Primary keys have unique values, but foreign keys can be duplicated in the
table.
Sometimes entity types will be related many-to-many, e.g. each patient will
attend more than one session of a clinic, but each session will be attended
by more than one patient. In a case like this the Patients and Session
tables don't have foreign keys, but a third table, Attendances say, will have
two foreign keys, PatientID and SessionDate for instance, each referencing
the primary keys of the other tables. The many-to-many relationship has been
resolved into two one-to-many relationships like this:
Patients----<Attendances>----Sessions
So your scenario falls into this last category. You have a patients entity
type , so you'll have a Patients table with columns PatientID (an autonumber
is suitable for this as it merely needs to be a unique number), FirstName,
LastName, DateOfBirth, AtendanceDay etc.
For the Sessions entity type, i.e. the set of all possible days on which
patients can attend the hospice for day care you can create a table with a
column of date/time data type which includes whatever dates over a period of
time, e.g. the next 5 or 10 years you anticipate using the database (it can
easily be extended later if necessary). You might want to put other columns
in this table if there are other 'attributes' you need to record about each
date. Or you might relate this table to another one such as a table of types
of treatments or activities via a third table in the same way as its related
to Patients. For the moment, however, lets concentrate purely on recording
attendances by patients on the relevant SessionDates without considering
things like treatment or activity types.
I'll come back to how to set up the Sessions table later, but the heart of
the database is really the Attendances table. As I said this would have
columns PatientID and SessionDate as foreign keys. Together these form the
composite primary key of the table as the two values in combination will
always be unique. Other columns of this table can record other attributes of
each patient's attendance at a session if you wish. This could be the times
of arrival and departure on the day for instance. This table itself might
reference other tables by means of foreign key columns, e.g. a RoomID column
referencing a Rooms table if a patient's attendance is related to a
particular room. Or it could be related via yet another table to more than
one room if a patient's attendance is in more than one room location during
the day. This table could itself have columns to record the attributes of
the patient's attendances in each room on the day; times come to mind again.
These are only examples of what might be incorporated in the database of
course, and your requirements will no doubt differ.
For data entry the default day of attendance per patient can be used to
automatically assign patients to particular dates. This could be done on a
weekly basis for instance. This would require a little bit of code writing
to insert rows into the Attendances table, but that's something we can help
with. The data entry would be via forms, which would hide all the techie
stuff from the user, so if designed well should be easy for users with no
database experience to work with.
As it happens I have done quite a lot with two institutions similar to you
own over the last year or so, in one case a residential home , in the other a
medical institution. These have been quite detailed activity and treatment
scheduling applications, and probably go a long way beyond what you have in
mind at present. Nevertheless less they will almost certainly contain a lot
which can be very easily adapted to suit your situation. If you'd like to
mail me at:
kenwsheridan<at>yahoo<dot>co<dot>uk
I can let you have a copy of one of the files so you can see the sort of
things which can be done. The file will not be the actual application used
by the institution in question, but my working copy which my contact in the
institution draws upon in building the working application.
Finally to create the Sessions table you can use the following procedure:
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 & _
"(SessionDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (SessionDate))"
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 & "(SessionDate) " & _
"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 & "(SessionDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If
End Function
You'd paste this into a standard module, save the module under a fifferent
name to the procedure, e.g. mdlCalendar, and say you wanted to create a table
Sessions for the next 10 years, excluding weekends from the table, then you'd
call it like so:
MakeCalendar_DAO("Sessions",#01/01/2008#, #12/31/2017#, 2,3,4,5,6
which you can do from the debug window (aka Immediate Window) which you can
open by pressing Ctrl+G.
Ken Sheridan
Stafford, England
