Seating chart

[Leo]

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

 

[KenSheridan via AccessMonster.com]

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

 

[Klatuu]

You are committing spreadsheet in Access. Your table should not have a field
for every shift and station. It should have a field for Shift and a field
for Station. The station and seat should be indentified in the fields. You
are doing that in your other table, so this table you don't really need.

 

[Leo]

Well I do have a union query that extrapolates (more like salvages) data and
into a vertual table called "tblEXPUNION" and drops into the following
fields: Id, LastName, FirstName, DateOfTreatment, Shift and Station. How
do I write a SQL command to Select as SH1ST1 from tblEXPUNION WHERE Station
=1 and Shift=1? Perhaps this will help to recover from the spread-sheet
conundrum!
Thanks
Leo

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

--



.

 

[KenSheridan via AccessMonster.com]

Leo:

It sounds like you've returned a table equivalent, if you take away the
LastName and FirstName columns, to the PatientSchedule table in the model I
suggested. Its an easy task to create and fill the Shifts and Stations
tables from this, and even the patients table, though I'd assume you do have
that already. I'd recommend that you use your union query to build a
PatientSchedule table, and use that in future for data input. You'd then
have a solid logical model appropriate to a database, rather than your
current faux spreadsheet.

But as regards your question, are you saying you want to return a result
table with DateOfTreatment as a 'row heading' and SH1ST1…..SH4ST40 as 'column
headings' with the patient names at the row/column intersections? If so you
need a crosstab query. Off the top of my head something like this:

TRANSFORM MAX(Id)
SELECT DateOfTreatment
FROM tblEXPUNION
GROUP BY DateOfTreatment
PIVOT "SH" & Shift & "ST" & Station
IN ("SH1ST1","SH1ST2",<and so on to>"SH4ST40");

The MAX function is used here as an arbitrary aggregation operator; any
aggregation function could in fact be used. Note that the patient names are
not returned at the intersections but the Id values. Two patients could have
the same name. As it happens I was once present at a clinic when two
patients with exactly the same first and last names, both female and with the
same date of birth arrived. I overheard the staff discussing the confusion
which had arisen from this. You could in a form based on the crosstab query,
as well as having a text boxes bound to SH1ST1 columns to show the Id
values, also use combo boxes for the SH1ST1 etc control to show the patient's
names by hiding the bound first column of the controls' RowSource in the
usual way (the control wizard can set this up for you if necessary).

One possible fly in the ointment with this is that if you want all available
dates returning and there is a date with no rows in tblEXPUNION then it would
not be returned by the crosstab query. The solution would be to have a
'calendar' table of all available dates and LEFT JOIN this to tblEXPUNION on
DateOfTreatment, returning the date column from the calendar table rather
than that from tblEXPUNION.

Ken Sheridan
Stafford, England

Well I do have a union query that extrapolates (more like salvages) data and
into a vertual table called "tblEXPUNION" and drops into the following
fields: Id, LastName, FirstName, DateOfTreatment, Shift and Station. How
do I write a SQL command to Select as SH1ST1 from tblEXPUNION WHERE Station
=1 and Shift=1? Perhaps this will help to recover from the spread-sheet
conundrum!
Thanks
Leo

Leo:

[quoted text clipped - 116 lines]