How Merging 3 lists with similar client data?

[tdaccess]

I have three lists in Microsoft Excel that I have imported into Microsoft Access into three tables. All three tables have staff information with at least 4 common fields: UserID, LastName, FirstName, and Position. A user record may exist in only one list or all three lists. I need to generate a query that will merge all the lists together and provide unique records for each user. Here is an example of what the data looks like in Access after I import it:



Minimal FieldList of all tables: UserID, LastName, FirstName, Position

List_A
1001, Duck, Daffy, Crane Operator
1002, Mouse, Mickey, Head Honcho
1003, Mouse, Minnie, Miss Head Honcho
1004, Dawg, Dippy, Safety Inspector
1005, Dawg, Pluto, Safety Team
1007, Duck, Huey, Bean Counter Apprentice

List_B
1003, Mouse, Minnie, Miss Head Honcho
1004, Dawg, Dip, Safety Inspector
1006, Duck, Donald, Bean Counter
1007, Duck, Huey, Bean Counter Apprentice
1008, Duck, Louie, Bean Counter Apprentice
1009, Duck, Dewey, Bean Counter Apprentice

List_C
1004, Dawg, Dippy,
1009, Duck, Dewey, Bean Counter Apprentice II
1010, Pelekai, Lilo, Human Resource Chief
1011, Pan, Peter, Motor Pool Chief
1012, Jasmine, Princess, Chief of Security
1013, White, Snow, Chaplain

From the example I need to resolve the following problems in addition to merging the list together:
1. User Record "1004, Dawg, Dippy, Safety Inspector" is in all three lists.
2. In List_B, "Dippy Dawg" has the name of "Dip Dawg" but the UserID is still the same.
3. In List_C "Dippy Dawg" does not have a position.
4. Minnie Mouse is in List_A and List_B, but NOT List_C.
5. Dewey Duck is in List_B and List_C, but the position descriptions are slightly different.

I want the results to look like this:
qryResultList:
UserID, LastName, FirstName, Position, InList_A, InList_B, InList_C
1001, Duck, Daffy, Crane Operator, T, F, F
1002, Mouse, Mickey, Head Honcho, T, F, F
1003, Mouse, Minnie, Miss Head Honcho, T, T, F
1004, Dawg, Dippy, Safety Inspector, T, T, T
1005, Dawg, Pluto, Safety Team, T, F, F
1006, Duck, Donald, Bean Counter, F, T, F
1007, Duck, Huey, Bean Counter Apprentice, T, T, F
1008, Duck, Louie, Bean Counter Apprentice, F, T, F
... (and so on)

I have created little queries and then added them all together, but I just cannot seem to get the final result that I want. Is it possible to do this with just SQL syntax? I want to keep this dynamic because this is a routine process. But for now, I just want to get the final results.

I hope to hear back from someone soon.
Sincerely,
Too Cool For School

[ajetrumpet]

Too Cool For School,

Interesting name. do you have a real one?

do you know how to find exact duplicate records and delete them?? it requires a call to a vba procedure. by the way, doing this dynamically probably won't happen, simply because scenarios like this are just messes. you have to do some cleanup on your own sometimes. Nothing everything is automated just yet!

[tdaccess]

>> Interesting name. do you have a real one?
aka FluffyCone...

>> you have to do some cleanup on your own sometimes
Thanks for the candid answer. I was thinking I could just code my way into a solution/answer. I guess it would be better to put everything into a new table and clean it up.

I found was able to create a list of unique UserIDs with something like this:

SELECT distinct UserID
FROM (
SELECT List_A.UserID FROM List_A
UNION ALL
SELECT List_B.UserID FROM List_B
UNION ALL
SELECT List_C.UserID FROM List_C
) as Distinct_UserID;

Then I thought I would piece in the missing data based on the UserID that I have.
For example select the items I have from List_A, then filling the missing items with List_B, and then with List_C. Does this sound like an efficient way to do this?

I also wanted to identify what list the UserID exist in because that is important in the final report.

I'm dealing with about 300 records total and 171 unique records.

Thanks,
FluffyCone... My dog's name was Fluffy and I use to live on Cone Drive.

[tdaccess]

I think these two queries provide the same results:

queryOne:
SELECT distinct UserID
FROM (
SELECT List_A.UserID FROM List_A
UNION ALL
SELECT List_B.UserID FROM List_B
UNION ALL
SELECT List_C.UserID FROM List_C
) as Distinct_UserID;

queryTwo:
SELECT List_A.UserID FROM List_A
UNION
SELECT List_B.UserID FROM List_B
UNION
SELECT List_C.UserID FROM List_C;