Linking multiple tables

[Sripalkumar]

Hi,

I am trying to create a query which links around 13 tables. Each of the
tables have a unique ID which is similar for all tables. It is a patient
identification number. The problem is not all tables contain the same number
of rows, i.e., there is some missing data.

In the query, I am selecting select columns of each table. As I am building
this, I noticed that I seem to lose some rows. For ex: I started with 10150
rows (from table 1) and now I am down to 10100 (after linking 10 tables).
Table 1 has 10150 rows and I have linked table 1 primary key (ID) to rest of
the tables primary key. How can I ensure that I do not lose any rows? What
am I doing wrong here.....Pls help.
thanks,
Sripal.

 

[KARL DEWEY]

First create a union query named PatientID_All using all your tables like
this --
SELECT PatientID
From Table1
UNION SELECT PatientID
From Table2
UNION SELECT PatientID
From Table3
.....
UNION SELECT PatientID
From Table12
UNION SELECT PatientID
From Table13;

Then in the query design view put PatientID_All and all 13 of the tables.
Click on PatientID of PatientID_All query and drag to each of the other
table PatientID fields. Then one by one click on the connecting line,
double click, and select option to see all records of PatientID_All query and
only those that match of the connected table.

 

[Sripalkumar]

This might sound stupid...how do I create a union query?

 

[KARL DEWEY]

A union query has to be created in SQL view.

A union can only be viewed in SQL view.

First create a select query in design view using your first table.

Then click on VIEW - SQL View. You will see something like this --
SELECT PatientID
From Table1;

Remove the semicolon from the end. Copy, paste, and edit table names to
have all 13 tables as I posted earlier. Close with a semicolon.

There are two types of union queries - UNION SELECT and UNION ALL
SELECT the difference being the first drops duplicates which is what you
want.

 

[Sripalkumar]

Thank you so much. I actually was able to figure it out. Now the issue I
have is a bit complex. I have the previous query with all the linkage that I
described before. It took me quite sometime to select individual fields of
each table. Now with the method you suggested, I will have to redo this all
over again. Is there a way to redeem this, i.e., somehow remove the link
from Table 1 to all tables and replace by patientID_all link. We should be
able to do this in SQL...cant we.

 

[Sripalkumar]

Figured it out. Thanks. I copied the sql language from the prior
query...created a new query and linked it as you said and in the SELECT part
pasted all the fields I had before. Works like a charm.
thanks,
Sripal