Duplicate Data when querying 3 or more tables 1

[kcbeckwith]

Hi Everyone,

I could really use some help! I have a database that tracks lab results and it has about 8 tables in it. I have all the tables set up as a one to many. There all related back to the first table, which has the patient's first name, last name, and other data that would only have one record. My queries work fine when I use two tables, but my problem is that when I create a query that would use three of the tables, then I get duplicate data in my queries and reports. I have looked through many access books and even search through many web sites and can't seem to figure it out. Here's is some example of a couple of my tables and how I have them set up.

TblPatient - This table is the "1" of Many 1:m

PatientAuto - Primary Key
FirstName
LastName
Patient Id
Doctor
Weight

TblLab1 - Many

Lab1Id - Primary Key
Lab1PatientId
HBA1CDATE
HBA1CDATA
TRIGDATE
TRIGDATE

TblLab2 - Many
Lab2Id - Primary Key
HDLDATE
HDLDATA
MICRODATE
MICRODATA

 

[lespaul]

to get rid of the duplicates add the DISTINCT keyword to your query:

SELECT DISINCT Field1, Field2 From tableName

However, if you are using INNER JOINS and joining into multiple tables, then you will be missing information if the patient doesn't have a record in all the tables. To over come this, use a LEFT or RIGHT join.

For more information on Joins, check out 'Understanding SQL Joins'

HTH

Leslie

Leslie

 

[JonFer]

Definitely look at your join conditions and add any missing criteria for the joins. Adding DISTINCT to remove duplicates should be the last step you take and generally only if your table structure doesn't allow you to remove the duplicates with the join conditions.


John

http://www.interautomateconsulting.com

 

[dhookom]

A query that features two or more "one-to-many" relationships from the same "one" table should never be used and has no place in any application I have ever written. This type of relationship can best be displayed using one or more subforms (or subreports).

There may be times when a single query makes sense but I have never seen it.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

Duane - If the 2 "many" tables have links between them, it would be fine; I have written queries like this. You may also want a cross join result for use with a separate outer join query.



John

http://www.interautomateconsulting.com

 

[kcbeckwith]

Duane,

I'm not sure I understand you. Are you saying that my relationships are not set up correctly and thatis why my queries are not working right?

 

[PHV]

In your first post, how is related TblLab2 to the other tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

kc - How do you know which records in tblLab2 relate to a patient? I only see a patient ID field in tblLab1.

What is the difference between data in tblLab1 and tblLab2?


John

http://www.interautomateconsulting.com

 

[dhookom]

KC,
I think your relationships are probably correct. I just don't understand what you would expect to see in a query joining the three tables.

Assume you have a patient table (tblPatient) and a patient may have multiple symptoms (tblPtSymptoms) and multiple treatments (tblPtTreatments). If the Treament records are related to the symptoms then you should be able to create a single query that makes sense. However, if tblPtSymptoms and tblPtTreatments are not directly related to each other then combining all three tables into one query will create a cartesian query which has little value.

As I stated earlier, this may be desirous in certain situations but this is very rare. Normally this data would be displayed in form/subform(s) and report/subreport(s). If a patient had only one symptom or one treatment then the query might work.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kcbeckwith]

Duane,

The reason I would want to use 3 tables in a query is because sometimes I need a report that would have data from all three tables. I'm fairly new at Access, but I thought the best way to set up a database was to split your data up into different tables and create a relationship between them and then create queries to run reports. When I created this database, I linked all the tables back to the main table (TblPatient) because that was the only way I could figure how to set up the relationships.(All my reports would need information from the main table because it has FirstName, Lastname, etc.) None of the other tables link to each others because they have nothing in common to link them. (That's why I'm wonder if I have my relationships set up right.) I do understand why when I create a query with three tables I'm getting cartesian data, I just don't know how to fix the problem. I have spent many hours trying to redo the relationships and still not sure if that is my problem. Would a Union Query resolve my problem or am I heading in the wrong direction?

 

[dhookom]

ckbeckwith,
It sounds like your tables are probably correct. The only concept you are missing is how to use subreports on main reports. Each subreport can have its own record source and you can set a relationship between the main and sub reports by setting the Link Master/Child properties.

For instance, you would create a main report based only on your main table. Then, create and add subreports (they are reports) based on the related tables.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

what's the difference between the data in Lab1 and Lab2? Have you read 'The Fundamentals of Relational Database Design'? Like PHV and JonFer said, there is no PatientID in Lab2?



Leslie

 

[kcbeckwith]

I apologize. Lab2 does have a field with PatientId in it. I accidently forgot to list it. I have read many books and searched the Internet on database design and cannot figure how out how to get my reports not to duplicate data when I use 3 tables. Everthing works find with 2 tables, once I go into the report and set it up to group on patientId and do not show duplicates. I understand why it happens on the reports, but cannot figure out how to fix it. I tried using the subreports like Duane suggested, but can't seem to get it to work, yet. (I'm still trying.) My reports always list Patient Id, First Name, Last Name, which are in the first table and I might need information in 2 other tables for a report. The reports usually are not for one patient. It would be for all patients with a certain criteria. I have also tried setting up grouping in the reports and do not show duplicates under certain fields and still cannot get it to work with 3 tables.

 

[dhookom]

Create subreports (they are just reports) based on your related tables. Place them in the detail section of a report that is based on a tblPatient. Set the link master/child properties to PatientAuto and the related table (foreign key) in the other tables.

Don't even think of trying to create a report based on the three tables together.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kcbeckwith]

Duane,

Is there a way to get the headers to show in the sub-report?

 

[dhookom]

All headers except page headers will display in a subreport. Move your page header controls to a report or group header if you want them to display when the report is used as a subreport.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kcbeckwith]

Duane,

Thanks for your help! The subreports worked. I would never thought of using subreports. Thanks again.