Merge or Combine Tabels

[Kelly]

I have been asked to clean up a very complicated Access database. I have two
tables, with the identical headings, different information.
--Table1 is all the customers in one part of town
--Table2 is all the customers in another part of town
(why, they did this, Im not sure)

Each table is very large and I need to combine them or merge them togeher,
either merge table2 into table1
or
take table1 and table2 and create a table3.

I was able to create a union query, but not sure how to then create a 'mater
table' from that query. I need to further create at least two junction
tables from this 'master table' and want to make the database as clean as
possible. I thought about just cut and pasting the info...
Anyone have any ideas

 

[John Spencer]

If there are not duplicates between the table (good luck on determining that)
then you could just choose to append all the records in Table 2 into table 1
with an append query.

If your UNION query gives you the desired result, just use it as the source.

Copy Table 1 and paste it into your database. When asked respond "Structure only"

That will create a table with all the fields and indexes but NO data.

Now create a new query using your UNION query as the source table
Add all the fields to the list of fields
Select Query: Append from the menu
Choose your new table as the destination
If the field names all match then the append to row will have the field names
filled in. Any blanks you will have to adjust/choose the field to be appended to.
Choose Query: Run from the menu



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[S.Clark]

Use Action Queries to Make new tables, then Append and Update data to them.

 

[Kelly]

when I hit run I get the error

"Cannot open datebase ". It may not be a database that your application
recognizes, or the file may be corrupt."

Do you know what " would refer to???

This is what I use to make my Union Query

SELECT *
FROM [KF ITEMS]
UNION ALL SELECT *
FROM [SOUTHWEST, EYE, HEART];



KF Items is Table1
Southwest is Table2

 

[John W. Vinson]

when I hit run I get the error

"Cannot open datebase ". It may not be a database that your application
recognizes, or the file may be corrupt."

Do you know what " would refer to???

This is what I use to make my Union Query

SELECT *
FROM [KF ITEMS]
UNION ALL SELECT *
FROM [SOUTHWEST, EYE, HEART];



KF Items is Table1
Southwest is Table2

Is the name of Table2 in fact

SOUTHWEST

or is it

SOUTHWEST, EYE, HEART

?

If the latter, that may be the problem. A comma is a delimiter, and I've seen
wierd results when object names contain commas (or slashes or hyphens or
semicolons).

Try renaming the table to (say) SOUTHWEST_EYE_HEART and see if that resolves
the problem.