Show Multiple Tables in Query

[cheechootrain]

Hello,



I was wondering if anyone knows how to show multiple tables in one query without duplications. I've attached a mock database of what I am working on. Basically, I want to show all of the numbers from different tables regarding one patient. But when I do using the Patient ID, it repeats the information over and over again. Is this the right way? Or is there a better way? I know I can use forms, but I ultimately want to transfer this database to excel so I can use graphs to describe the trends of the patients' results. My real database has about 300 columns, for all 5 tables.

Thanks for your help!

Cheechoo

[June7]

300 columns!?!? Tables have a limit of 256 fields. It's rare to see them all used. Often an indication of non-normalized data structure.

"Duplication" will happen when joining 'master' (or parent) table to multiple 'child' tables (child tables have many records associated with master). Better to do a report with subreports for each of the related child tables.

Or can do aggregate query on each of the child tables and then join the queries to master.

Your sample tables have only one record for each patient. This should not result in "duplication".

SELECT Patients.*, [Lung Volumes].*, PH.*, CT.*
FROM [Lung Volumes] RIGHT JOIN (CT RIGHT JOIN (Patients LEFT JOIN PH ON Patients.[Patient ID] = PH.[Patient ID]) ON CT.[Patient ID] = Patients.[Patient ID]) ON [Lung Volumes].[Patient ID] = Patients.[Patient ID];

PatientID fields in CT and LungVolumes should be changed to number type to match the PatientID primary key in Patients.

Advise no spaces or special characters/punctuation (underscore is exception) in names nor reserved words as names. Better would be PatientID or Patient_ID.

[cheechootrain]

300 columns!?!? Tables have a limit of 256 fields. It's rare to see them all used. Often an indication of non-normalized data structure.

"Duplication" will happen when joining 'master' (or parent) table to multiple 'child' tables (child tables have many records associated with master). Better to do a report with subreports for each of the related child tables.

Or can do aggregate query on each of the child tables and then join the queries to master.

Your sample tables have only one record for each patient. This should not result in "duplication".

SELECT Patients.*, [Lung Volumes].*, PH.*, CT.*
FROM [Lung Volumes] RIGHT JOIN (CT RIGHT JOIN (Patients LEFT JOIN PH ON Patients.[Patient ID] = PH.[Patient ID]) ON CT.[Patient ID] = Patients.[Patient ID]) ON [Lung Volumes].[Patient ID] = Patients.[Patient ID];

PatientID fields in CT and LungVolumes should be changed to number type.

Advise no spaces or special characters/punctuation in names nor reserved words as names. Better would be PatientID or Patient_ID.

Haha, I mean I have almost total of 300. So maybe 80 to 90 per table.

Yeah, I tried using report. But I do like the query idea. Thanks!!

[June7]

That's still a lot of fields but more reasonable. You did not provide that structure for analysis.

[KathyL]

You’re more likely going to transfer only a table or query result to excel for a graph, not the entire database.
Joining a parent table to a child table will always show the repetition of information, as you do have a one-to-many relationship. Think about it, how else could you show the data in a datasheet where you match on a one-to-many relationship? One side will have to show it’s data repeated.
But if you take that query to a report, you can tell the report to suppress printing of the duplicates.

[June7]

Kathy, I think user is including several 'many' side tables in the join so the duplication is beyond just repetition of the parent info.

[KathyL]

"Kathy, I think user is including several 'many' side tables in the join so the duplication is beyond just repetition of the parent info."

Yikes. I might then tell the original OP... you can't join many-to-many tables, not directly. Somewhere in the mix, it will need a parent table for one-to-many links.

[June7]

That's the issue. They are joining several 'many' side child tables to the 'one' side parent. Post 2 explains that doesn't work nice.

[cheechootrain]

You're both right. I just did a report to print off data for one patient. For graphing, I ran a separate query for each table and exported individually to excel and did copy and paste.

Thanks for the help!

[June7]

Access graphing was not adequate for your needs? I have many graphs in forms and reports.