Leo:
What you have in the case of your tblSeating table is more akin to a
spreadsheet, not a relational database table. A relational database
represents each entity type as a table, with columns representing the
attributes of the entity type. You have entity types Shifts, Stations,
Patients and PatientSchedule, so these should be modelled with tables like
the following:
Shifts
….Shift (primary key)
So this table will have four rows.
Stations
….Station (primary key)
So this table will have forty rows.
Patients
….PatientID (primary key)
….Firstname
….LastName
….DoB
….etc
PatientSchedule
…..Shift
…..Station
…..PatientID
…..ScheduleDate
The primary key of the last table is a composite one made up of all four
columns as in combination these must have distinct values. This table is in
fact modelling a many-to-many relationship between Shifts, Stations and
Patients, so should be related to each of the other three tables on Shift,
Station, and PatientID respectively, with referential integrity enforced in
each case.
You can then show the patients per shift for a specific date with a query
like this:
PARAMETERS [Enter date:] DATETIME;
SELECT Shift, Station, [Enter date:] AS [Schedule date],
(SELECT FirstName & " " & LastName
FROM Patients INNER JOIN PatientSchedule
ON Patients.PatientID = PatientSchedule.PatientID
WHERE PatientSchedule.Shift = Shifts.Shift
AND PatientSchedule.Station = Stations.Station
AND ScheduleDate = [Enter date:])
AS PatientName
FROM Shifts, Stations
ORDER BY Shift, Station;
Firstly note that the parameter is declared. This is always a good idea, but
particularly so with date/time parameters as a date parameter entered in
short date format might otherwise be incorrectly interpreted as an
arithmetical expression and give the wrong results.
The Shifts and Stations are included in the query without being joined. This
returns the Cartesian product of the two tables, i.e. each row in one is
joined to each row in the other.
The subquery returns the patient's names from the Patients table INNER JOINed
to the PatientSchedule table on PatientID where the shift and station match
the shift and station returned by each row of the outer query and where the
schedule date is the date entered by the user at the [Enter date:] parameter
prompt. Consequently where the subquery returns no row the computed
PatientName column will be Null. By using the query as a form's (or a
report's) RecordSource when the form is opened the user will be prompted for
the date and the form will show all shifts and stations, but with patient
names only for those assigned to a shift/station for the date in question.
This assumes that all shifts/stations can have a patient assigned on the day.
If differing subsets of shifts/stations are available on different dates,
however, then this would need to be modelled with another table:
ShiftStations
…..Shift
…..Station
…..ScheduleDate
Again all columns constitute the primary key. This table would be related to
PatientSchedule on these three columns, with referential integrity enforced.
This would ensure that a patient can only be assigned to a valid
shift/station/scheduledate. In this scenario the ShiftStations can be joined
to the PatientSchedule and Patients tables in LEFT OUTER JOINs:
PARAMETERS [Enter date:] DateTime;
SELECT ShiftStations.Shift, ShiftStations.Station,
[Enter date:] AS [Schedule date],
Patients.FirstName & " " & Patients.LastName AS PatientName
FROM (ShiftStations LEFT JOIN PatientSchedule
ON (ShiftStations.ScheduleDate = PatientSchedule.ScheduleDate)
AND (ShiftStations.Station = PatientSchedule.Station)
AND (ShiftStations.Shift = PatientSchedule.Shift))
LEFT JOIN Patients ON PatientSchedule.PatientID = Patients.PatientID
WHERE (((ShiftStations.ScheduleDate)=[Enter date:]))
ORDER BY ShiftStations.Shift, ShiftStations.Station;
The reason LEFT OUTER JOINs cannot be used in the first query is that the
restriction by date would be on a table on the right side of the join. This
cannot be done as it in effect turns it into an INNER JOIN, so the
shift/station values without patients would not be returned. This is why a
subquery is used in that query.
Ken Sheridan
Stafford, England
I have a form called 'frmSeating' based on a table called 'tblSeating' and it
has fields 'SH1ST1' (Shift1Station1) through 'SH4ST40' corresponding to
Shift1 through 4 and Stations 1 through 40. I have another table called Schd
that lists the patients and their shift and stations. Based on the user
selecting the date (both tables have a date field) I want the frmSeating
display the data appropriately. How do I accomplish this so that if there is
no patient scheduled for particular shift and station that field will be left
blank. Remember I want the user to select only the date.
Please help.
Thanks
Leo
--
Message posted via AccessMonster.com
.
