How To Display Information From Multiple Tables For One Patient ID

[cheechootrain]

Hi,




I was wondering if there is a way to show information from multiple tables that are linked via "Patient ID" on one page without repeats. I have tried doing a query but I get a lot of duplicates.

For example, I want it to show

Patient ID ; Date Peformed ; Testing ; FVC (Lung Volumes tbl); FVC Predicted (Lung Volumes tbl) ; FVC Actual (Lung Volumes tbl); PH (ABG tbl); CO2 (ABG tbl); CO3 (ABG tbl)

All in one page via report or query so I can analyze the results and draw charts/graphs.

The database I have attached is a smaller version of what I am working on but the principle is the same. I know I can do a Form with the Patients table and do subform for the ABG and Lung Volumes to show up and use their Patient ID as the link, but that's the most I can do.

Also, I have tried exporting the tables into excel and doing copy and paste but that's too inefficient and time consuming. I have two additional tables and about 25 data columns with 20 patients total for my larger version.

Thanks for any help!


Emily

[ItsMe]

Have you looked at the properties of your relationship within your query?

You can right click the line btween the two tables and click the "Join Proerties" to view the properties.
.

[NTC]

One gets repeats in a join where there are multiple valid records in one table or the other. If the repeats are absolutely the same data in every field - then you can eliminate those by making the select query into DISTINCT. You right click in design view and in the properties select 'Unique Values' = yes .....be sure to right click when your cursor is in a blank area of the upper half of query design view....

if the data is not absolutely identical in every field of the duplicate records then changing to DISTINCT won't do anything as they are not truly duplicates.... you then have to consider your presentation on how to best present multiple records - this can be done with grouping if you use a report object, or with an inset sub form if using forms....

hope this helps

[Dal Jeanis]

The main question is, what information do you want to see in your query? Do you want to see the average, the latest, the highest reading, or what?

Hmm. Reviewing the data, my guess is that your "duplicates" are happening because you want the ABG records and lung records from each date/testing to be linked to each other, and showed on a single line, but you haven't told Access that particular detail. So Access is linking every possible combination of one ABG record and one lung record for the same patient, just like you told it.

To solve this, link the ABG table and the lung table by their Date and/or their Testing phase. Probably you should use just the Testing phase, in case someday a patient gets the two tests on different days. If you linked the tables on both fields, those results would not match so they would disappear from the report.

[June7]

If tables have one-to-many or many-to-many relationship, use subforms and subreports to display each of the dependent tables. Otherwise, do aggregate queries of the 'many' side tables and join those to the primary.

Or as Dal points out, use compound joins to connect records (patient ID and Testing phase, assuming patient has record in both tables).

[Dal Jeanis]

From the report layout the OP listed, and the table layouts in the sample, it looks like she wants one line per date (which is equivalent to one line per round of testing). It would probably be better normalized to take the testing phases into a separate table, but it will work as-is for the application to just link the two results tables on the testing phase, as well as the patient ID.

[cheechootrain]

From the report layout the OP listed, and the table layouts in the sample, it looks like she wants one line per date (which is equivalent to one line per round of testing). It would probably be better normalized to take the testing phases into a separate table, but it will work as-is for the application to just link the two results tables on the testing phase, as well as the patient ID.

Yes! I want one line per testing period. However, the dates are not always the same for each table. For example, ABG might be taken a day earlier than Lung Volumes, etc. The testing is constant for all but the dates differ for a few, hence the different tables.

So if I run a query for patient John, it should show:

Testing ; FVC ; PH ; CO2 ; CO3 ; FVC Predicted ; etc.

for that specific patient or I can just do that for all patients. I know how to use the filter function for a query. It's just that I get multiple sets of the same data because it's true for each of the tables.


Thanks for all your replies! You all have been really helpful. I'll keep working on it

[cheechootrain]

The main question is, what information do you want to see in your query? Do you want to see the average, the latest, the highest reading, or what?

Hmm. Reviewing the data, my guess is that your "duplicates" are happening because you want the ABG records and lung records from each date/testing to be linked to each other, and showed on a single line, but you haven't told Access that particular detail. So Access is linking every possible combination of one ABG record and one lung record for the same patient, just like you told it.

To solve this, link the ABG table and the lung table by their Date and/or their Testing phase. Probably you should use just the Testing phase, in case someday a patient gets the two tests on different days. If you linked the tables on both fields, those results would not match so they would disappear from the report.

OMG!!! You are a genius. I just did a simple relationship link of the testing phase and it worked! Thank you sooooooooooooooo much!!!

[Dal Jeanis]

You're welcome.

Rule 1 is "know your data".

Rule 2 is, "know your outcome". (Describe to yourself as closely as possible what you WANT Access to do.)

After those two, you just determine the proper relationships to specifically explain that desired outcome to Access, and test test test.

By the way, Rule 0 - which comes before Rule 1 - is back up your database early and often, and always do your testing in a junk copy, not the live production database.