Arvil has provided you with a solution. I just want to make a few comments about the code you tried.
You tried
Originally Posted by
Marcia
SELECT * Into MainTable From 2006Table
UNION ALL
SELECT * Into MainTable From 2014Table
UNION ALL
This won't execute for a couple of reasons.
1) You used a wildcard to select the fields. But one of the fields "RowNo", which is an Autonumber type field. You DON'T want to and can't append the autonumbers from the 7 tables. An Autonumber field is a unique field - and I would bet there are duplicate numbers in that field. Do you think that a "RowNo" would be 1 in each of the tables?
2) The syntax you used is "SELECT * Into MainTable...". "Select Into" creates a new table. So, if you had 7 statements like this, you would be trying to create 7 tables named "MainTable". Since each table name MUST be unique....... Boom! It fails!
You could create a union query that merges the data from 7 tables into one query: ( does not eliminate duplicates)
Code:
SELECT xlsTable.TaxDecNo, xlsTable.NameOfOwner, xlsTable.Administrator, xlsTable.MailingAddress, xlsTable.Location, xlsTable.Barangay, xlsTable.Classification, xlsTable.PreviousNo,
xlsTable.Area, xlsTable.MVLand, xlsTable.MVImp, xlsTable.MVMach, xlsTable.AVLand, xlsTable.AVImp, xlsTable.AVMach, xlsTable.YearBegins, xlsTable.Remarks FROM 2006Table AS xlsTable
UNION ALL
SELECT xlsTable.TaxDecNo, xlsTable.NameOfOwner, xlsTable.Administrator, xlsTable.MailingAddress, xlsTable.Location, xlsTable.Barangay, xlsTable.Classification, xlsTable.PreviousNo,
xlsTable.Area, xlsTable.MVLand, xlsTable.MVImp, xlsTable.MVMach, xlsTable.AVLand, xlsTable.AVImp, xlsTable.AVMach, xlsTable.YearBegins, xlsTable.Remarks FROM 2007Table As xlsTable
UNION ALL
SELECT xlsTable.TaxDecNo, xlsTable.NameOfOwner, xlsTable.Administrator, xlsTable.MailingAddress, xlsTable.Location, xlsTable.Barangay, xlsTable.Classification, xlsTable.PreviousNo,
xlsTable.Area, xlsTable.MVLand, xlsTable.MVImp, xlsTable.MVMach, xlsTable.AVLand, xlsTable.AVImp, xlsTable.AVMach, xlsTable.YearBegins, xlsTable.Remarks FROM 2008Table AS xlsTable
.
.
.
Note that this union query uses an alias (xlsTable) in the FROM clause.
This query could be a saved query or in code.
Then you could have (MainTable must already exist):
Code:
INSERT INTO MainTable (TaxDecNo, NameOfOwner, Administrator, MailingAddress, Location, Barangay, Classification, PreviousNo, Area, MVLand, MVImp, MVMach, AVLand, AVImp, AVMach, YearBegins, Remarks )
SELECT xlsTable.TaxDecNo, xlsTable.NameOfOwner, xlsTable.Administrator, xlsTable.MailingAddress, xlsTable.Location, xlsTable.Barangay, xlsTable.Classification, xlsTable.PreviousNo, xlsTable.Area,
xlsTable.MVLand, xlsTable.MVImp, xlsTable.MVMach, xlsTable.AVLand, xlsTable.AVImp, xlsTable.AVMach, xlsTable.YearBegins, xlsTable.Remarks FROM 2006Table xlsTable
UNION ALL
SELECT xlsTable.TaxDecNo, xlsTable.NameOfOwner, xlsTable.Administrator, xlsTable.MailingAddress, xlsTable.Location, xlsTable.Barangay, xlsTable.Classification, xlsTable.PreviousNo, xlsTable.Area,
xlsTable.MVLand, xlsTable.MVImp, xlsTable.MVMach, xlsTable.AVLand, xlsTable.AVImp, xlsTable.AVMach, xlsTable.YearBegins, xlsTable.Remarks FROM 2007Table xlsTable
UNION ALL
SELECT xlsTable.TaxDecNo, xlsTable.NameOfOwner, xlsTable.Administrator, xlsTable.MailingAddress, xlsTable.Location, xlsTable.Barangay, xlsTable.Classification, xlsTable.PreviousNo, xlsTable.Area,
xlsTable.MVLand, xlsTable.MVImp, xlsTable.MVMach, xlsTable.AVLand, xlsTable.AVImp, xlsTable.AVMach, xlsTable.YearBegins, xlsTable.Remarks FROM 2008Table xlsTable
.
.
.
Note: Arvil's query handles duplicates - the method above does not. You would have to run more code to eliminate duplicates.