3

I have mostly identical tables, however there are slightly differences like missing columns in older ones. I want to merge 5 tables into one. Is there any easy way to do this?

2

Create a new table that have all the columne in all your 5 tables, then create the needed SQL statment like SELECT * FROM Tb1 INTO TableWithAllColumns eventually add some WHERE to exclude the duplicate rows from the insert.

This will work if your table doesent have similar rows. If you need to merge similar rows together, there is no a simple way to tho this. The best you can do is find, or write, a tool that can compare all the value in all the columns and act in the right way.

1

No need to use SQL that's the whole point of MS Access the Graphical Interface is sufficient for this task First create a new table with all the fields you want. Lets call it tbl01MasterTable Say the fields are GivenName, FamilyName, AddressLine1,AddressLine2, AddressLine3, Town, PostCode

Say you Have 2 tables to merge

tbl02BritishTable

Fields are Forename, Surname, Address1, Address2, City, PostCode

tbl03AmericanTable

Fields Are FirstName, LastName, LineOne, LineTwo, LineThree, LineFour, ZipCode

LineFour is used for the town or City

Create one append query for each of the tables that hold your information ie 2 append queries

Append query one (BritishTable-->MasterTable) Forename-->GivenName, Surname-->FamilyName, Address1-->AdressLine1,...City-->Town, PostCode-->Postcode

Append Query two (AmericanTable-->MasterTable) FirstName-->GivenName, SecondName-->FamilyName, LineOne-->AddressLine1,..., LineFour-->Town, ZipCode-->PostCode

Run the queries sequentially (You can use a macro to do this if you have many append queries)

If the append query does not include a field in the master table it will leave that field blank in the master table hence your data will always fit into the Master table

I strongly recommend wherever possible use the graphical interface it is much easier than trying to remember complex SQL Language.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.