query needed to sort/combine other queries

[DrPat]

I am an amateur programmer and use Access to keep track of my medical practice.

I frequently have to send patients out for tests and the data is stored for each test in the patient’s record. What I would like to do is create a query that selects all patient tests done each month, and then further sorted by the facility they were sent to. As an example, I will use MRI’s.

Table name is Patient Data Table, the relevant fields are:
· Patient ID (Primary Key, Autonumber)
· Last Name (Patient’s last name)
· First Name
· C/S MRI Facility (text field chosen from a combo box from table [MRI Facility]
· C/S MRI Scan Date (Date field)
· C/S MRI Scan Done (Yes/No)
· L/S MRI Facility (text field chosen from a combo box from table [MRI Facility]
· L/S MRI Scan Date (Date field)
· L/S MRI Scan Done (Yes/No)
· …And so on for the other body areas

I have a unique query for each type of scan to be done that selects the patients based on Scan Done=Yes.

The format of the report that I have in mind would look something like this:

May 2013
Acme MRI
Patient 1 C/S MRI 5/3/2013
Patient1 L/S MRI 5/3/2013
Patient 2 C/S MRI 5/3/2013
Patient2 L/S MRI 5/3/2013

Standard MRI
Patient 3 C/S MRI 5/2/2013
Patient3 L/S MRI 5/3/2013
Patient 4 C/S MRI 5/5/2013
Patient4 L/S MRI 5/10/2013

…And so on for each facility and each month.

I have tried to use a query to compile all the data from the other queries and the result is dozens of unnecessary and confusing records. I have done my best to learn to ropes of union queries with frustrating results.

Any help available? Did I explain it clearly enough?

 

[plog]

Table and field names should not hold information that you need to sort/use. Instead, that data should be held in a field. This means that "C/S MRI" and "L/S MRI" should be a value in a field, not in the name of the field itself.

You need a new table to hold all that scan data. Those fields prefixed with a scan type (...Facility, ...ScanDate) should go in their own table. This has the added benefit of eliminating that redundant ...ScanDone field (if there's a record, it was done, if not, it wasn't).

Your new table will have this structure:

Scans
PatientID, ScanType, ScanDate, ScanFacility
14, "C/S MRI", 4/16/2013, "City Hospital"
14, "L/S MRI", 5/11/2013, "Acme Open MRI"
13, "C/S MRI", 4/16/2013, "Acme Open MRI"

With that structure your report becomes simpler to create (and a lot of other issues you have or will have) will be avoided.

 

[DrPat]

Thank you, Plog, that thought crossed my mind several times over the past few months. I'm afraid that ship has sailed. I have too many other queries and forms attached to that table. It would take me a year rewrite it..

 

[plog]

In 5 years, will more time be lost rewriting it, or muddling through issues like this?

 

[DrPat]

So I escaped the necessity of the re-write for now, although I know I won’t escape it forever. But here’s how I handled it.
I created a new table duplicating the necessary fields. Additionally, for this table, I added 3 new fields, “area”, “date” and “facility” (all 5 MRI areas, all 5 MRI dates, all 5 MRI facilities).
Then a delete query to wipe the table for this report
I created a delete query to wipe the table clean.
I created 5 append queries to capture all the patients in the database who have had MRI’s.
Then 5 update queries to move all the various areas to my new “area” field.
Then 5 update queries to move all the various dates to my new “date” field.
Then 5 update queries to move all the various facilities to my new “facility” field.
So I ended up with a table of Last Name, First Name, Date, Area, Facility (plus lots of unnecessary trash).
Then a delete query to wipe data which was out of date range and another query to count up the facilities, areas etc. for a subreport.
Then the report, subreport and the macro to run them all
A lot of work? You bet, but one table, 23 queries, 2 reports and a macro was less anxiety provoking than re-writing the whole DB.
Thanks much for the advice regardless. I won't ignore it.
/Pat