joining in MS Access

[karambos]

I'm new to MS Access. I have a very simple database in Access. It consists of 3 tables. Each table has only 1 column, "Serial numbers". This is a fixed width column consisting of 7 numbers with data type "number".

Within each table, each number appears only once. However, some numbers will appear in all three tables, some in only 2 tables and others will be only in 1 table.

I need to find:

• those serial numbers that exist in all 3 tables
• those serials that exist in tables 1&2 but not 3
• those serials that exist in table 1 only; not in tables 2 & 3

I'd be grateful if someone could give me a high level overview of how to do this in Access.


Thanks in advance
~K

[ssanfu]

Using unmatched queries wizard for #2 & #3....

Maybe something like this??

Queries
1)

those serial numbers that exist in all 3 tables

qryMatchInAll3

Add the 3 tables to the query and link on the serial number field


2)

those serials that exist in tables 1&2 but not 3

qryIn1and2 Without Matching Table3

create a query as in #1, but with tables 1 & 2, then use the unmatched query wizard to add qryIn1and2 and table 3



3)

those serials that exist in table 1 only; not in tables 2 & 3

Table1 Without Matching qryIn2And3

create a query as in #1, but with tables 2 & 3, then use the unmatched query wizard to add table 1 and query qryIn2And3


See attached mdb

[June7]

First thing you might want to do is create a 'master' dataset of all possible serial numbers. A UNION query can accomplish:

SELECT SerialNumber FROM table1
UNION SELECT SerialNumber FROM table2
UNION SELECT SerialNumber FROM table3;

Now use that query as source for subsequent queries.

Build a query that joins each of the original 3 tables to the UNION query. Use join type 'Include all records from Query1 ...'.

Where fields from the original 3 tables are blank, the SerialNumber is not in that table.

Can apply filters and sorts to the second query.