[AccessD] Comparing tables question

The process is infinitely easier if you have a single field PK in each table
- such as an autonumber.

1) Create a join on the two tables, joining pattern in one table to pattern
in the other table, and well in one table to well in the other table.  In
this query pull the PKs (autonumber) so that you have the PKID of each
record from each table where you DO HAVE A MATCH.

2) Save that query.

3) Now create a second "not in" query, where you pull the PK of the table
that you want to know is NOT IN the result set.  Join to the corresponding
PK in the result saved.  Make the join an outer join, all records in the
table you want to see the results in.  Now pull the PK of the saved query
and set a "not null" in the selection area.

The query should now show you only the records that are "not in" the saved
query created in step 1.

This kind of query is quite common and you will most likely eventually
become quite adept at creating them.


John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, June 22, 2005 10:05 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Comparing tables question


I have two tables both of which have the fields Pattern and Well. The field
pattern will appear several times with a different corresponding well. I
need to compare each unique pattern well combination in table1 to see if the
same unique combination exists in table2 and output a list of the ones that
have no match. I am at a loss of how to do this. Thanks for ideas.

 

Chester Kaup

Engineering Technician

Kinder Morgan CO2 Company, LLP

Office (432) 688-3797

FAX (432) 688-3799

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com