Consoldate data from 3 different tables in same database

I have a database with 3 tables. All 3 tables contain the same fields.

One of the fields is named "Email Address" and another one of the fields is named "Source" (which in each table is the name of the table itself).

My goal is to consolidate the 3 tables into 1 table based on a unique identifier (in this case Email Address) and identify the Sources where the same email address appears in each of the 3 tables.

I tried using an Append query, but this did not seem to work. Can someone please help?

Thanks. This doesn't seem to quite do what I need it to do. It combined all 3 tables which is good, but it has duplicate rows based on email address.

What I need next is to eliminate the duplicates but add an indicator showing which tables each of the duplicates appeared in. Is this possible?

I am attaching a sample database here.

You will see that based on email address, there are duplicate records between table 1 and table 2 ([email protected]), table 2 and table 3 ([email protected]), and table 1 and table 3 ([email protected]).

But you can see that the value in the field named "Source" is different.

What I need to do is combine the 3 tables, remove the duplicates so that there is only one line per unique email address, but have an indicator showing, for example, that [email protected] is in both Table 1 and Table 2.

Does this make sense?

Originally Posted by alansidman

I understand what you are looking for. See if the attached does what you want. I had to create several Left Join queries and then a Union Query and finally filtered the Union Query to get the results. Look it over closely and advise if this achieves what you want.

Thanks for posting your solution. I was able to consolidate my tables in the same database.