Comparing tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to compare two tables to find any records where one of the fields does
not have the same information, i.e., different addresses for the same
contact. I've tried using the Find Unmatched Records Wizard but I am doing
something wrong because it isn't working properly. For example, as a test, I
made a copy of a table, QC, and called it QC2. I then changed one of the
records to reflect a different address. When I run the Unmatched Records
query I get a list of 10 different records. How can that be if I have made an
exact copy but changed only one. I'm confused. Can anyone help me figure this
out?
 
How about

SELECT File1.Address1, File2.Address1 FROM File1 INNER JOIN File2 ON File1.
Name = File2.Name WHERE File1.Address1 <> File2.Address1;

Helpful?
 
Thanks so much for your reply. That definitely helped. What I ended up with
was:

SELECT QC.contact, QC2.contact, QC.company, QC2.company, QC.Address1,
QC2.Address1, QC.city, QC2.city, QC.state, QC2.state, QC.zip, QC2.zip
FROM QC
INNER JOIN QC2
ON (QC.Contact = QC2.Contact AND QC.company=QC2.company)
WHERE (QC.Address1 <> QC2.Address1)
OR (QC.zip <> QC2.zip)
;

I have very little experience with SQL so, another question: In the first
line, where I am selecting the fields to include in the table, is there a
command where I can select all the fields (instead of me adding each one?)

Thanks again.
 
INETERSECT would work but access does not use it. I recreated some tables
doing what you are trying and what I did what opened the query in design mode
and just dragged and dropped and let access do the rest. First you need to
display the two tables you are comparing. Then you would click on Contact
within the QC table and drag it onto the Contact within the QC2 table. Do
that for all of the fields, matching them up with the field they need to be
compaired against and it will automatically fill in the fields for you. I did
mine and this is the code that popped out and gave me the correct results.

SELECT a.*
FROM a INNER JOIN b ON (a.Zip = b.Zip) AND (a.State = b.State) AND (a.Address
= b.Address) AND (a.Name = b.Name) AND (a.AID = b.bid);
Thanks so much for your reply. That definitely helped. What I ended up with
was:

SELECT QC.contact, QC2.contact, QC.company, QC2.company, QC.Address1,
QC2.Address1, QC.city, QC2.city, QC.state, QC2.state, QC.zip, QC2.zip
FROM QC
INNER JOIN QC2
ON (QC.Contact = QC2.Contact AND QC.company=QC2.company)
WHERE (QC.Address1 <> QC2.Address1)
OR (QC.zip <> QC2.zip)
;

I have very little experience with SQL so, another question: In the first
line, where I am selecting the fields to include in the table, is there a
command where I can select all the fields (instead of me adding each one?)

Thanks again.
How about
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Yes, just use

SELECT QC.* FROM QC .....

Sam
Thanks so much for your reply. That definitely helped. What I ended up with
was:

SELECT QC.contact, QC2.contact, QC.company, QC2.company, QC.Address1,
QC2.Address1, QC.city, QC2.city, QC.state, QC2.state, QC.zip, QC2.zip
FROM QC
INNER JOIN QC2
ON (QC.Contact = QC2.Contact AND QC.company=QC2.company)
WHERE (QC.Address1 <> QC2.Address1)
OR (QC.zip <> QC2.zip)
;

I have very little experience with SQL so, another question: In the first
line, where I am selecting the fields to include in the table, is there a
command where I can select all the fields (instead of me adding each one?)

Thanks again.
How about
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Cool!

Thx, and thx to everyone else that replied.

OfficeDev18 via AccessMonster.com said:
Yes, just use

SELECT QC.* FROM QC .....

Sam
Thanks so much for your reply. That definitely helped. What I ended up with
was:

SELECT QC.contact, QC2.contact, QC.company, QC2.company, QC.Address1,
QC2.Address1, QC.city, QC2.city, QC.state, QC2.state, QC.zip, QC2.zip
FROM QC
INNER JOIN QC2
ON (QC.Contact = QC2.Contact AND QC.company=QC2.company)
WHERE (QC.Address1 <> QC2.Address1)
OR (QC.zip <> QC2.zip)
;

I have very little experience with SQL so, another question: In the first
line, where I am selecting the fields to include in the table, is there a
command where I can select all the fields (instead of me adding each one?)

Thanks again.
How about
Click to expand...
[quoted text clipped - 12 lines]
exact copy but changed only one. I'm confused. Can anyone help me figure this
out?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top