[AccessD] Comparing tables question

You don't have to use the groupby queries. I mentioned them because they
show the two lists with one instance of each pattern/well combo. In fact, in
my query below I ignored them and wrote the query directly using the tables.
Sorry for the confusion.
Jim Hale

-----Original Message-----
From: Hale, Jim [mailto:Jim.Hale at fleetpride.com]
Sent: Wednesday, June 22, 2005 9:50 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Comparing tables question


Try this:
1) Create a groupby query on the fields pattern and well for table one
2)Create a groupby query on the fields pattern and well for table two
3)use the unmatched query wizard to create the needed query
4) the only trick is, after the wizard has created the query, go into design
view and make the second outer join(the wizard only creates one join)
5) you will wnd up with something like this

SELECT Table1.Patterns, Table1.Wells
FROM Table1 LEFT JOIN Table2 ON (Table1.Wells = Table2.Wells) AND
(Table1.Patterns = Table2.Patterns)
WHERE (((Table2.Patterns) Is Null));

the resulting query produces a data set of pattern/well combos not found in
table 2. HTH
Jim Hale
-----Original Message-----
From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com]
Sent: Wednesday, June 22, 2005 9: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

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.