Daniel:
Its not advisable to work directly with tables; forms and reports should be
the interfaces with the data. The former give you control, the latter enable
better presentation. Access provides wizards for you to create simple forms
and reports, and these can be adapted quite easily. We can always help of
course.
I think your problem essentially stems from flaws in the 'logical model';
that's the way the data is structured in related tables. As you are using
data imported from elsewhere you probably can't do a lot to remedy that,
however, but you can mitigate it.
A relational database uses tables, each of which represents an 'entity
type'. The tables' columns represent attributes of the entity type, and
different entity types are related to each other via 'keys', columns with
values which map rows in one table to another.
I get the impression that you are dealing with one table; is that right?
Obviously 'Patients' is one entity type. 'Admission Events' seems to be
another. You say that there are multiple rows per admission event, so there
is a third entity type; I'll call it 'Admission Sub-events' but I'm sure
there's a more accurate descriptive term.
A fundamental principle of the database relational model is that there is no
'redundancy', i.e. each 'fact' is contained once and once only in the
database, e.g. the fact that patient 42's first name is Ken and last name is
Sheridan would be stored once only, in a row in a Patients table. This would
prevent inconsistent data being entered, e.g. me being Ken in one row and
Kenneth in another (databases are pretty dumb so don't know that ken and
Kenneth are one and the same).
So in a properly structured database you'd have there tables Patients,
AdmissionEvents and AdmissionSubEvents. The first two are related via
PatientID columns, the latter two by AdmissionEventID columns. Now, as far
as I can gather a discharge date is an attribute of an admission event,
rather than of an admission sub-event. Do this would be a column in the
AdmissionEvents table, not the AdmissionSubEvents table, i.e. this 'fact' is
stored only once. I'm not sure from my limited knowledge of the reality
behind your database which table admission date would be in; you can judge
that for yourself.
You could 'decompose' your table into three tables. Its not too difficult
to do in fact via a few 'append' and 'update' queries. Or you could try and
get the single table into a consistent sate, so you don't have any
contradictory values in it. This is what I was suggesting by means of an
update query to get all the discharge dates per patient per admission event
the same. This would eliminate the contradictions of having some rows with
the default discharge date of 7 June 2079, and some with the actual discharge
date. To automate this you'd need to create a form based on your table
(which I'm calling Admissions, so you'll need to change some things below to
reflect the real name of the table). The form wizard will do this for you
and create a nice attractive form to work with. What you then need to do is
execute an update query which changes the discharge dates for the current
patient when you update the data in the form, i.e. when you enter a real
discharge date.
Before we look at how that's done in the form lets first consider the update
query. I'm going to give you this in SQL rather than try and describe how to
do it in design view, so what you'll need to do to create the query is open
the query designer in the usual way, don't add any table, switch to SQL view
and then paste in the SQL below in place of what's there already. You'll
then have to amend it so that the table and column (field) names are your
actual ones.
What the query will do is reference the form you created to get the patient
and the discharge date, so I'm going to assume you've saved the form as
frmAdmissions, that there's a text box control on it bound to a column called
PatientID (table and column names are not case-sensitive BTW) in the
Admissions table and that this is Text data type, and that there is also a
text box bound to a column Discharge_Date in the same table and this is
Date/Time data type. In each case the name of the text box is the same as
the underlying column to which its bound.
When a query references a control on a form this is a 'parameter'. Its
always a good idea to declare parameters at the start of a query so there is
no confusion over their data type (a date entered as 10/24/2008 could be
misinterpreted as an arithmetical expression otherwise). So the update query
would be like this:
PARAMETERS
Forms![frmAdmissions]![PatientID] TEXT,
Forms![frmAdmissions]![Discharge_Date] DATETIME;
UPDATE [Admissions] ([Discharge_Date])
SET [Discharge_Date] = Forms![frmAdmissions]![Discharge_Date]
WHERE [PatientID] = Forms![frmAdmissions]![PatientID];
Amend the table/column names as necessary and save the query as
qryDischargeDatesUpdate for instance.
One qualm I have about this is that a patient could be represented more than
once in the table, not as different admission sub-events, but as different
admission events. If this were the case there would need to be some means of
referencing the admission event rather than the patient so that the rows for
that admission event only were updated not all rows for the patient.
However, putting that to one side for the moment lets go back to the
frmAdmissions form.
All that's necessary here is to add some code to the form's AfterUpdate
event procedure to update all rows for the patient if a date other than 7
June 2079 has been entered as the discharge date.
To do this select open the form in design view and open its properties sheet
if its not already open. Then select the form by clicking on the small black
square in its top left corner. Then select the AfterUpdate event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the following lines of code between these two
existing lines:
On Error Goto Err_Handler
' temporarily turn off warning messages
DoCmd.SetWarnings False
' execute the update query
DoCmd.OpenQuery "qryDischargeDatesUpdate"
Exit_Here:
' turn warning messages back on
DoCmd.SetWarnings True
Exit Sub
Err_Handler
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
Once you have all the discharge dates per patient consistent in all rows
then the first query I sent you should work in that it will return all
patients where the date you are interested in falls between the admission and
discharge dates. You can amend the query so that you can enter any date as a
parameter at runtime rather than hard coding a date in the query:
PARAMETERS [Enter date:] DATETIME;
SELECT COUNT(*)
FROM
(SELECT DISTINCT patientID
FROM Admissions
WHERE [Enter date:]
BETWEEN admit_date AND discharge_date);
Create the query in the same way as I described above.
Finally, before you try anything like this its absolutely imperative that
you BACK UP the table until you are satisfied that its working properly.
We are not too far from Stratford here, so can get to see the Royal
Shakespeare Company performances relatively easily. In fact tourists
occasionally confuse Stratford and Stafford and turn up here looking for
Shakespeare's birthplace!
Ken Sheridan
Stafford, England
Daniel Collison said:
Ken:
Thanks again for your assistance. FYI, I've limited programming experience,
so I'm struggling a bit with the SQL statements. The database includes no
forms or reports. I created it merely to massage a dataset extract from
another source.
I am trying to obtain client counts associated with different points in
time. For this particular scenario, I am trying to identify all clients who
were hospitalized on 6/30/08. Perhaps it would make sense to create an
update query to update all record discharge_dates associated with admission
episode with the actual discharge_date...but I'm at a loss as to how to go
about doing this.
Thanks...any assistance would be appreciated.
(By the way, I noticed that you live in England. I was in London for
several days last November...and missed Patrick Stewart in a production of
"Othello". Had a great time though. I'd like to return someday and hike
along the some of the coastal trails! DC)
Ken Sheridan said:
Would in not be better to update all rows for the patient in question where
the discharge date = the default of 7 June 2079 to the actual discharge date
when a real one is entered? It would just need a simple update query
executed in the form's AfterUpdate event procedure.
But with the present practice don't you just need:
SELECT COUNT(*)
FROM
(SELECT DISTINCT patientID
FROM Admissions
WHERE #6/30/2008#
BETWEEN admit_date AND discharge_date
AND discharge_date <> #06/7/2079#);
Ken Sheridan
Stafford, England
:
Ken:
Thanks for your quick response. Unfortunately, it's not as simple as it
appears. Every admission episode includes one or more records. The majority
of those records include an "open" discharge date of 6/7/2079. For patients
that have actually been discharged, a record is included which shows the
actual discharge date, i.e. 5/4/08 or 6/2/08.
The query that results from your sshows virtually all client records,
because the vast majority of clients have admission episode records that
include 6/6/2079.
Any thoughts?
:
Isn't it simply a question of identifying those rows where #6/30/2008# falls
between the admission and discharge dates? Count the distinct patients like
so:
SELECT COUNT(*)
FROM
(SELECT DISTINCT patientID
FROM Admissions
WHERE #6/30/2008#
BETWEEN admit_date AND discharge_date);
Ken Sheridan
Stafford, England
:
I am trying to identify a count of clients who were present in a facility as
of 6/30/08. Each client has one or more admission episodes defined by an
admit_date and a discharge_date. One admission episode can have one or more
records associated with it. Clients whose cases are open, i.e. who have not
been discharged are assigned a discharge date of 6/6/2079. I believe the
universe of records includes three scenarios:
1) To identify a count of clients in a facility on 6/30/08, I first created
a query to exclude all admission episodes with a discharge_date less than
6/30/08 using the following criteria against the discharge_date:
<#6/30/2008#.
2) I also created a query to identify all episodes with a discharge of
greater than 6/30/08 using the following criteria against the discharge_date
field: >=#7/1/2008# And <>#6/6/2079#.
3) I have not been successful identifying only those admission episodes with
a discharge_date of 6/6/2079. How do I limit the query to only shows
admission episodes with a discharge_date of 6/6/2079?
I tried to skirt the problem by creating a query to only show admission
episodes that were NOT equal to query 1. For instance, in a query I have
added the field "ID Admissions Episode". I entered the following criteria
for the field: Not [qry Discharges <6/30/08 #2]![Admission Episode] .
Alternatively, I tried the same criteria with <> . Using one or the other
criteria returned 0 results for the query.
Any suggestions on how I can exclude Admissions episodes that have a
discharge date of less than 6/30/08?
Thanks,
