How Do I Combine multiple tables into 1 single query

[pcrosby78]

i have multiple tables in a file for my companies mailing list. The different tables represent slightly different mailing list.

However i must dedupe all the list at once. is there a simple way to run a query to make a large list of all the tables then run a deduping query on that list?

I have tried to do what i just explained but it keeps wanting me to set up relationships so i think i have been doing something wrong.

Can anyone please advice a novice on a simple solution for this?

-Thank You

 

[petersdaniel]

You need to perform a union query. This will combine both tables into on consolidated view

Example:

SELECT AgentID, Code FROM NewAgents UNION SELECT AgentID, Code FROM OldAgents;

petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[pcrosby78]

where exactly do i type that code in?

If i goto the design view do i enter it in a specific field?

 

[petersdaniel]

In Query designer, you need to go to SQL view mode and manually type it in. Unfortunately, Access does not provide a Union wizard.

To make the process less tedious, you can do a standard select query for each table in the query designer, shift over to SQL view, copy and then paste the SQLs together in one, adding the UNION statement between the SQL statements. (PS: watch out for the placement of the ";" when you do this...)

Good Luck petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein

 

[pcrosby78]

ok thanks i got it to work correctly... so a dedupping query of a query on multiple tables will still alow the user to delete a duplicate from the deduping query and also have it removed from the original table?

-THANKS!!!!