Comparing Two Tables, show matching data

[Guest]

I have search all through this newsgroup, found posts with missing
information for what I am trying to accomplish.

I have two tables, I want to compare both tables and show me duplicates.
Table1=AP1_Vendor
Field1=VendorName
Field2=VendorNumber

Table2=LAM_Vendor
Field1=Name
Field2=VendorID

I want the field's VendorName and Name to be compared and show me the
duplicates from AP1_Vendor with the added field VendorNumber in with the
results.
Abviously there is many more fields within these tables but I do not care
about those fields to be in the results, just the two mentioned.

Any and All help is appreciated... THANK YOU...

 

[James]

use the query wizard...

 

[Guest]

That was the first thing I did. The wizard lets me compare within one table,
not two.

 

[John Vinson]

I have search all through this newsgroup, found posts with missing
information for what I am trying to accomplish.

I have two tables, I want to compare both tables and show me duplicates.
Table1=AP1_Vendor
Field1=VendorName
Field2=VendorNumber

Table2=LAM_Vendor
Field1=Name
Field2=VendorID

I want the field's VendorName and Name to be compared and show me the
duplicates from AP1_Vendor with the added field VendorNumber in with the
results.

Create a new Query. Add both Table1 and Table2 to the query. Drag the
VendorName field from Table1 onto the Name field in Table2. Select
whichever fields you want to display.

Note that this will find only EXACT duplicates: if [VendorName] is
"McDonnell Douglas Inc" and [Name] is "McDonnell Douglas Inc." they
won't be a match.

John W. Vinson[MVP]

 

[Guest]

John: THANK YOU... But how can I get it to find EXACT? Is there a way?
The table has 2000 records and the query found 65 matches when there is
plenty more.
------------------
=)

I have search all through this newsgroup, found posts with missing
information for what I am trying to accomplish.

I have two tables, I want to compare both tables and show me duplicates.
Table1=AP1_Vendor
Field1=VendorName
Field2=VendorNumber

Table2=LAM_Vendor
Field1=Name
Field2=VendorID

I want the field's VendorName and Name to be compared and show me the
duplicates from AP1_Vendor with the added field VendorNumber in with the
results.

Create a new Query. Add both Table1 and Table2 to the query. Drag the
VendorName field from Table1 onto the Name field in Table2. Select
whichever fields you want to display.

Note that this will find only EXACT duplicates: if [VendorName] is
"McDonnell Douglas Inc" and [Name] is "McDonnell Douglas Inc." they
won't be a match.

John W. Vinson[MVP]

 

[James]

garbage in garbage out.

John: THANK YOU... But how can I get it to find EXACT? Is there a way?
The table has 2000 records and the query found 65 matches when there is
plenty more.
------------------
=)

I have search all through this newsgroup, found posts with missing
information for what I am trying to accomplish.

I have two tables, I want to compare both tables and show me duplicates.
Table1=AP1_Vendor
Field1=VendorName
Field2=VendorNumber

Table2=LAM_Vendor
Field1=Name
Field2=VendorID

I want the field's VendorName and Name to be compared and show me the
duplicates from AP1_Vendor with the added field VendorNumber in with the
results.

Create a new Query. Add both Table1 and Table2 to the query. Drag the
VendorName field from Table1 onto the Name field in Table2. Select
whichever fields you want to display.

Note that this will find only EXACT duplicates: if [VendorName] is
"McDonnell Douglas Inc" and [Name] is "McDonnell Douglas Inc." they
won't be a match.

John W. Vinson[MVP]

 

[John Vinson]

John: THANK YOU... But how can I get it to find EXACT? Is there a way?
The table has 2000 records and the query found 65 matches when there is
plenty more.

It will find exact. It will not, and cannot, find "almost" matches.

Could you post the SQL view of your query, and a couple of examples
that you think should match which aren't found?

John W. Vinson[MVP]

 

[Guest]

John... Before I post this SQL, I want to thank you...

Here is the SQL
SELECT AP1_VendorMaster.VendorName, AP1_VendorMaster.VendorNumber
FROM AP1_VendorMaster INNER JOIN [lam Vendor] ON AP1_VendorMaster.VendorName
= [lam Vendor].Name
WITH OWNERACCESS OPTION;

I have a Vendor name in AP1_VendorMaster as "A. D P INC." *Note there is an
"." at the end of Inc. In LAM_Vendor the same name but typed as "A. D P Inc"
*Note No "." at the end of Inc
Another is AP1_VendorMaster "Action Master" in LAM_Vendor "Action Master &".

 

[John Vinson]

John... Before I post this SQL, I want to thank you...

Here is the SQL
SELECT AP1_VendorMaster.VendorName, AP1_VendorMaster.VendorNumber
FROM AP1_VendorMaster INNER JOIN [lam Vendor] ON AP1_VendorMaster.VendorName
= [lam Vendor].Name
WITH OWNERACCESS OPTION;

That would appear to be correct.

I have a Vendor name in AP1_VendorMaster as "A. D P INC." *Note there is an
"." at the end of Inc. In LAM_Vendor the same name but typed as "A. D P Inc"
*Note No "." at the end of Inc
Another is AP1_VendorMaster "Action Master" in LAM_Vendor "Action Master &".

So the query is working exactly as I would expect. The two text
strings are different, therefore they don't match, therefore the
records aren't retrieved!

Remember - you're dealing with a mindless, stupid computer. It can
only take things literally. If you ask "are these the same" about two
strings, it cannot apply human intuition - it can only compare them
byte by byte and say "yes" or "no".

I suspect you could have "A. D. P. Inc." or "ADP Inc" or "Atcheson
Design Professionals Inc." as names for this same company; you or I
can look at these and speculate that they might (or might not!) be the
same, but a computer cannot.

I don't know of anything straightforward that would let you do these
"not quite matches". I suppose you could write VBA code to strip out
everything nonalphanumeric and match on those values (ADPINC frex) but
even that will miss some "matches".

John W. Vinson[MVP]

 

[Guest]

Thanks... I printed both lists and went through each table and edited one of
the lists to match the other. It was a teadest thing but non of the less, it
worked. Thank you for your help John...
--------------
=)

John... Before I post this SQL, I want to thank you...

Here is the SQL
SELECT AP1_VendorMaster.VendorName, AP1_VendorMaster.VendorNumber
FROM AP1_VendorMaster INNER JOIN [lam Vendor] ON AP1_VendorMaster.VendorName
= [lam Vendor].Name
WITH OWNERACCESS OPTION;

That would appear to be correct.

I have a Vendor name in AP1_VendorMaster as "A. D P INC." *Note there is an
"." at the end of Inc. In LAM_Vendor the same name but typed as "A. D P Inc"
*Note No "." at the end of Inc
Another is AP1_VendorMaster "Action Master" in LAM_Vendor "Action Master &".

So the query is working exactly as I would expect. The two text
strings are different, therefore they don't match, therefore the
records aren't retrieved!

Remember - you're dealing with a mindless, stupid computer. It can
only take things literally. If you ask "are these the same" about two
strings, it cannot apply human intuition - it can only compare them
byte by byte and say "yes" or "no".

I suspect you could have "A. D. P. Inc." or "ADP Inc" or "Atcheson
Design Professionals Inc." as names for this same company; you or I
can look at these and speculate that they might (or might not!) be the
same, but a computer cannot.

I don't know of anything straightforward that would let you do these
"not quite matches". I suppose you could write VBA code to strip out
everything nonalphanumeric and match on those values (ADPINC frex) but
even that will miss some "matches".

John W. Vinson[MVP]

 

[Guest]

Hello

one question, could you not use wild cards in the query beside the fields in
your criteria? for example:

table : Customer
field : Name
criteria: [Customer2]![name]*

any thoughts?

Thanks... I printed both lists and went through each table and edited one of
the lists to match the other. It was a teadest thing but non of the less, it
worked. Thank you for your help John...
--------------
=)

John... Before I post this SQL, I want to thank you...

Here is the SQL
SELECT AP1_VendorMaster.VendorName, AP1_VendorMaster.VendorNumber
FROM AP1_VendorMaster INNER JOIN [lam Vendor] ON AP1_VendorMaster.VendorName
= [lam Vendor].Name
WITH OWNERACCESS OPTION;

That would appear to be correct.

I have a Vendor name in AP1_VendorMaster as "A. D P INC." *Note there is an
"." at the end of Inc. In LAM_Vendor the same name but typed as "A. D P Inc"
*Note No "." at the end of Inc
Another is AP1_VendorMaster "Action Master" in LAM_Vendor "Action Master &".

So the query is working exactly as I would expect. The two text
strings are different, therefore they don't match, therefore the
records aren't retrieved!

Remember - you're dealing with a mindless, stupid computer. It can
only take things literally. If you ask "are these the same" about two
strings, it cannot apply human intuition - it can only compare them
byte by byte and say "yes" or "no".

I suspect you could have "A. D. P. Inc." or "ADP Inc" or "Atcheson
Design Professionals Inc." as names for this same company; you or I
can look at these and speculate that they might (or might not!) be the
same, but a computer cannot.

I don't know of anything straightforward that would let you do these
"not quite matches". I suppose you could write VBA code to strip out
everything nonalphanumeric and match on those values (ADPINC frex) but
even that will miss some "matches".

John W. Vinson[MVP]