Solved Compare Two Queries

[Hecronis]

My company is changing phone systems this week. Some people already have direct dial phone numbers with our old system that we are trying to port over to the new system. With this new system however all of the people who don't currently have a DID number will be getting one. We are trying to compare the list of numbers and names in the old system with the numbers and names in the new system. We are trying to make sure the numbers that get ported over are assigned to the correct people. I can't figure out how to create one query that shows a side by side comparison of all records from both tables. I know not all of the data is going to match up and that's what we are trying to sort through. I have uploaded a copy of the DB. The data that's in there is all test stuff so no sensitive information, but should have some scenarios that I believe we are going to run into (Misspelled names, numbers not matching up, etc.). Thank you in advance for your help with this.

 

[theDBguy]

You could try something like:

SELECT Old_ID, Old_Name, Old_DID, New_Name, New_DID
FROM Old
INNER JOIN New
ON Old.Old_ID=New.New_ID

Hope that helps...

 

[Hecronis]

The only problem is that the Old_ID numbers and New_ID numbers are probably not going to match up with the names. That's why I'm trying to join them by name instead of ID number. Query2 and Query3 in the example DB are kind of how I need the list to be. Showing where the names match up and blank spaces where the names don't. I just don't know how to combine the 2 queries. Whenever I do it doesn't display the data correctly.

 

[plog]

I can't get to your database, says the version is too new. Can you save it as an earlier version and repost?

 

[theDBguy]

The only problem is that the Old_ID numbers and New_ID numbers are probably not going to match up with the names. That's why I'm trying to join them by name instead of ID number. Query2 and Query3 in the example DB are kind of how I need the list to be. Showing where the names match up and blank spaces where the names don't. I just don't know how to combine the 2 queries. Whenever I do it doesn't display the data correctly.

But you said names could be misspelled. If so, how would you know which ones to compare against each other? Perhaps, you could try using the SoundEx function. Does the original table have a primary key?

 

[Pat Hartman]

In order to match two tables, you MUST have a UNIQUE field on which to match. Are you positive that there isn't a unique field available such as EmployeeID or perhaps their logon Name?

If you have to match on names, you get what you get. Garbage in, garbage out.

To account for names in one list but not the other, you need what is called a Full Outer Join. Access does not support this directly. To simulate one you need three queries.
1. Left join tblA to tblB
2. Right join tblA to tblB
3. Union qry1 and qry2.

So, where the left and right joins both return a record, the union will merge them so you will end up with:

tblA/tblB
tblA/null
null/tblB

the first type will be matches in both tables the second will be record in tblA but not in tblB. The third will be record in tblB but not in tblA

 

[Hecronis]

In order to match two tables, you MUST have a UNIQUE field on which to match. Are you positive that there isn't a unique field available such as EmployeeID or perhaps their logon Name?

If you have to match on names, you get what you get. Garbage in, garbage out.

To account for names in one list but not the other, you need what is called a Full Outer Join. Access does not support this directly. To simulate one you need three queries.
1. Left join tblA to tblB
2. Right join tblA to tblB
3. Union qry1 and qry2.

So, where the left and right joins both return a record, the union will merge them so you will end up with:

tblA/tblB
tblA/null
null/tblB

the first type will be matches in both tables the second will be record in tblA but not in tblB. The third will be record in tblB but not in tblA

This worked! I've never used a union before, but it's exactly what I was trying to do.