3 Databases need to be joined 1

[n3buo]

I have 3 databases that have an Patient ID number that is common to all. I have been trying to join these databases together using a new database with linked tables to the main table of the exsisting 3 databases. When I try to do a join to join the patient ID numbers together, I get multiple entries of the same data i.e. if each databases have 20 records I end up in the joined database with 400 records.

 

[PHV]

When I try to do a join to join the patient ID numbers together
How did you that ?
A starting point (in the SQL pane of the query window):
SELECT *
FROM (Table1 INNER JOIN Table2 ON Table1.PatientID = Table2.PatientID)
INNER JOIN Table3 ON Table1.PatientID = Table3.PatientID;

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

 

[n3buo]

Thanks for the Prompt reply.
tblBHSResults has 67 records
tblBAIResults has 67 records
tblBDIResults has 50 record

When I use the suggested SQL I get 968 records returned
Here's the current SQL
SELECT *
FROM (tblBHSResults INNER JOIN tblBAIResults ON tblBHSResults.ID1 = tblBAIResults.ID1) INNER JOIN tblBDIResults ON tblBHSResults.ID1 = tblBDIResults.ID1;

I have tried multiple types of joins (inner, left, right and Union) and I get the same number of records returned

The 3 databases have the following information
Date
ID1
First name
Last Name
Score.

Dave

 

[PHV]

What are the PrimaryKey of the 3 linked tables ?
To get at worst 204 records you may try this:
SELECT * FROM tblBHSResults
UNION SELECT * FROM tblBAIResults
UNION SELECT * FROM tblBDIResults

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

 

[willir]

You are using a front end to access data on three back end databases.

When I was "playing" with this problem earlier, I noted a similar issue.

In my test example, I had the person, address and phone number in three databases. When I ran the query, one contact who had 2 phone numbers and 2 addresses would get four hits. Basically, a cartesian product.

[tt]
DB1.Name DB2.Address DB3.PhoneNo
Asimov 123 Locus Dr 555-1111
Asimov 123 Excellent Ave 555-1111
Asimov 123 Locus Dr 555-1112
Asimov 123 Excellent Ave 555-1112
[/tt]

This is as expected since there is no way in the above example where the system can know that
123 Locus Dr is paired with 555-1111
123 Excellent Dr is paired with 555-1112

I then added a field, AddressType and PhoneType, and used this as part of the linking criteria. Results were...

[tt]
Name AddrType Address PhType PhoneNo
Asimov Home 123 Locus Dr Home 555-1111
Asimov Work 123 Excellent Ave Work 555-1112
[/tt]

I suspect this may mean you need to further qualify the linkage / relations to match up the results.

...Also, have you edited your relationships to ensure you only retrieve matching records. PHV used INNER joins which does the same thing.

From the menu, "Tools" -> "Relationships". Add the relevant linked tables. Drag the primary key in one table to the foreign key in the other table, ie. PatientID. Right click on the relationship line and select "Edit Relationship". You will not be able to enforce referential integrity, but by clicking on JoinTypes, you can ensure matching records are retrieved.

Hope we got you pointed in the right way.
Richard