Query that will look for duplicate in two tables.

[John]

I have two tables that were originaly Excel tables and imported them into
Access. Import went well. Both tables consist of columns named - Name,
Department Number, Department Name, and Employee ID. I need to compare the
two tables and have Access give me the NON duplicates.
I ran a Find Unmatched Query using name in both tables. The results showed
almost as many records as are in the original two tables. What I noticed it
found in one table Smith, John and Smith,John. These are the same people but
one person typed the name with the last name and comma space while the other
person who typed the other table omitted the space. Therefore I think the
query is seeing this as two seperate people because they do not match
exactly? Unfortunatly they left the name coulmn with the last ane and first
name not seperated into two separate fields.
Any help would be appreciated.

 

[John W. Vinson]

I have two tables that were originaly Excel tables and imported them into
Access. Import went well. Both tables consist of columns named - Name,
Department Number, Department Name, and Employee ID. I need to compare the
two tables and have Access give me the NON duplicates.
I ran a Find Unmatched Query using name in both tables. The results showed
almost as many records as are in the original two tables. What I noticed it
found in one table Smith, John and Smith,John. These are the same people but
one person typed the name with the last name and comma space while the other
person who typed the other table omitted the space. Therefore I think the
query is seeing this as two seperate people because they do not match
exactly? Unfortunatly they left the name coulmn with the last ane and first
name not seperated into two separate fields.
Any help would be appreciated.

A rather complicated query should do (most of) this for you. Assuming that
your tables are named Table1 and Table2, put this SQL into the SQL view of a
new query (edit the tablenames of course):

SELECT Table1.*
FROM Table1
LEFT JOIN Table2
ON Table2.[Name] LIKE Trim(Left([Table1].[Name], InStr([Table1].[Name], ",") -
1)) & "*"
AND Table2.[Name] LIKE "*" & Trim(Mid([Table1].[Name], InStr([Table1].[Name],
",") + 1)
WHERE Table2.[Name] IS NULL;

This should find record which have a Name in Table1 which does not exist in
Table2. You can then edit the query swapping the tablenames to get the
mismatches in the other direction.

Unfortunately, if there's a Bob Jones in Table1 who called himself Robert
Jones in Table2, there'll be no match. And of course if there are two people
named Jim Smith in Table1 only one of whom is in Table2, you won't find the
discrepancy without a much more complex query.

I hope these records will end up in some normalized tables - there should, for
example, be a single Departments table with a primary key DepartmentID, and an
People table with fields PersonID (primary key), LastName, FirstName,
DepartmentID.

 

[John]

John
Thanks for your help. I entered the SQL statement, but somewhere I must have
typed it wrong because now when I run the query I receive a parameter box
titled Tax List and looking for something to be typed into it. As I stated in
my previous question below. I need to find the values in the Tax List table
that do not have corresponding vlaues in the Annette Gift Card table. Basicly
i am looking for employees who were taxed for a gift card (tax list table)
but did not actually recive a gift card (Annette gift card list - these
employees actually received a gift card and should be taxed).
Here is my query as typed in the SQL editor:
SELECT TaxList.*
FROM TaxList
LEFT JOIN AnnetteGiftCardList
ON AnnetteGiftCardList.[Name] LIKE Trim(Left([TaxList].[Employee_Name],
InStr([TaxList],[Employee_Name], ",") - 1)) & "*"
AND AnnetteGiftCardList.[Name] LIKE "*" &
Trim(Mid([TaxList].[Employee_Name], InStr([TaxList].[Employee_Name], ",") +
1))
WHERE AnnetteGiftCardList.[Name] IS NULL;

I have two tables that were originaly Excel tables and imported them into
Access. Import went well. Both tables consist of columns named - Name,
Department Number, Department Name, and Employee ID. I need to compare the
two tables and have Access give me the NON duplicates.
I ran a Find Unmatched Query using name in both tables. The results showed
almost as many records as are in the original two tables. What I noticed it
found in one table Smith, John and Smith,John. These are the same people but
one person typed the name with the last name and comma space while the other
person who typed the other table omitted the space. Therefore I think the
query is seeing this as two seperate people because they do not match
exactly? Unfortunatly they left the name coulmn with the last ane and first
name not seperated into two separate fields.
Any help would be appreciated.

A rather complicated query should do (most of) this for you. Assuming that
your tables are named Table1 and Table2, put this SQL into the SQL view of a
new query (edit the tablenames of course):

SELECT Table1.*
FROM Table1
LEFT JOIN Table2
ON Table2.[Name] LIKE Trim(Left([Table1].[Name], InStr([Table1].[Name], ",") -
1)) & "*"
AND Table2.[Name] LIKE "*" & Trim(Mid([Table1].[Name], InStr([Table1].[Name],
",") + 1)
WHERE Table2.[Name] IS NULL;

This should find record which have a Name in Table1 which does not exist in
Table2. You can then edit the query swapping the tablenames to get the
mismatches in the other direction.

Unfortunately, if there's a Bob Jones in Table1 who called himself Robert
Jones in Table2, there'll be no match. And of course if there are two people
named Jim Smith in Table1 only one of whom is in Table2, you won't find the
discrepancy without a much more complex query.

I hope these records will end up in some normalized tables - there should, for
example, be a single Departments table with a primary key DepartmentID, and an
People table with fields PersonID (primary key), LastName, FirstName,
DepartmentID.

 

[John W. Vinson]

John
Thanks for your help. I entered the SQL statement, but somewhere I must have
typed it wrong because now when I run the query I receive a parameter box
titled Tax List and looking for something to be typed into it. As I stated in
my previous question below. I need to find the values in the Tax List table
that do not have corresponding vlaues in the Annette Gift Card table. Basicly
i am looking for employees who were taxed for a gift card (tax list table)
but did not actually recive a gift card (Annette gift card list - these
employees actually received a gift card and should be taxed).
Here is my query as typed in the SQL editor:
SELECT TaxList.*
FROM TaxList
LEFT JOIN AnnetteGiftCardList
ON AnnetteGiftCardList.[Name] LIKE Trim(Left([TaxList].[Employee_Name],
InStr([TaxList],[Employee_Name], ",") - 1)) & "*"
AND AnnetteGiftCardList.[Name] LIKE "*" &
Trim(Mid([TaxList].[Employee_Name], InStr([TaxList].[Employee_Name], ",") +
1))
WHERE AnnetteGiftCardList.[Name] IS NULL;

Is the name of the table TaxList? or Tax List? What is the *actual* error
message? What are some actual values from the Employee_Name and Name fields?

You might try putting the Trim expressions into a vacant Field cell just to
see how they're being interpreted.