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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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
Fields are Forename, Surname, Address1, Address2, City, PostCode
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.