Access Combining Tables

[jlyn123]

I am not sure what the best way to combine multiple tables into one table without causes duplicates is. I appended the tables to the main table but when I wanted to append again (with all the new rows) it pulled in everything that was already in the table. I dont want any duplicates - just a full list of all the items. I looked at a union query; however, there are 4 tables and they all have different amounts of columns so the query wont work. I cant do them separate either...so I am not sure what to do. I dont want to run a query to find the duplicates and delete that way - there will be too many and inefficient. I want a way to pull in either the new rows or delete the current main table and replace with each tables current listing. Any ideas?

 

[sheetspread]

How about an update query?

 

[xenou]

I looked at a union query; however, there are 4 tables and they all have different amounts of columns so the query wont work. I cant do them separate either...so I am not sure what to do.

if your tables have different amounts of columns how would you put them in one table? For instance if one table has columns a, b and the other has columns c, d, e then what would you do if you were combining them into one table?

 

[sheetspread]

I was wondering that too

 

[jlyn123]

Not all the columns are required to make up the master table. Just need a full listing of all linecodes but not all business require the same information that would be stored in the tables.

I dont tihkn an updaet query would work?

 

[sheetspread]

Then a union of select queries with the fields you choose (as long as they line up).

 

[xenou]

yes, union sounds right:

select MyField from MyTable1
union
select MyField from MyTable2
union
select MyField from MyTable3
union
select MyField from MyTable4

The above query can be used as the basis for a make table query or an append query if you want to store the results in a table. Note that Union will remove duplicates by default (if by duplicates you mean all the fields in each row - some people confuse this with duplicates in only one of the fields).