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?

rfIPS wrote:
>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?

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200606/1

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.


"OfficeDev18 via AccessMonster.com" wrote:

> How about
>
> SELECT File1.Address1, File2.Address1 FROM File1 INNER JOIN File2 ON File1.
> Name = File2.Name WHERE File1.Address1 <> File2.Address1;
>
> Helpful?
>
> rfIPS wrote:
> >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?
>
> --
> Sam
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...eries/200606/1
>

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);

rfIPS wrote:
>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
>>
>[quoted text clipped - 12 lines]
>> >exact copy but changed only one. I'm confused. Can anyone help me figure this
>> >out?

--
Casey
College Student

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200606/1

Yes, just use

SELECT QC.* FROM QC .....

Sam

rfIPS wrote:
>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
>>
>[quoted text clipped - 12 lines]
>> >exact copy but changed only one. I'm confused. Can anyone help me figure this
>> >out?

--
Sam

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200606/1

Cool!

Thx, and thx to everyone else that replied.

"OfficeDev18 via AccessMonster.com" wrote:

> Yes, just use
>
> SELECT QC.* FROM QC .....
>
> Sam
>
> rfIPS wrote:
> >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
> >>
> >[quoted text clipped - 12 lines]
> >> >exact copy but changed only one. I'm confused. Can anyone help me figure this
> >> >out?
>
> --
> Sam
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...eries/200606/1
>