Extracting data from multiple tables into a separate table and to consolidate multiple tables

[Marcia]

Hi all. I have seven tables imported from Excel with the same field names. The field names (12 of them) include Name and Mailing Address. I would like to create a Name table, extracting all the distinct names with their corresponding address from the 7 tables. A name that appear in different address should be included in the new table. I tried the "Append" but it only returned those names and addresses with matching records in all tables. How do I include the distinct names and addresses? also, how do I consolidate the 7 tables into one big table? thank you.

[ssanfu]

Well, "Name" is a reserved word (it is a property) and shouldn't be used as an object name.


You want to merge 7 tables that have identical field names?
Is this a one time thing?


For a one off job, I would use 7 append tables.

What is the table structure (field names)?

[Marcia]

Well, "Name" is a reserved word (it is a property) and shouldn't be used as an object name.

I'm sorry, the actual field name is "NameOfOwner"

You want to merge 7 tables that have identical field names?

Yes i would like to merge the 7 tables

Is this a one time thing?

It is not a one time thing because the source tables might be edited later on

For a one off job, I would use 7 append tables.

I tried the ff but it returned an error message of Syntax error in query, incomplete query clause
SELECT * Into MainTable From 2006Table
UNION ALL
SELECT * Into MainTable From 2014Table
UNION ALL

What is the table structure (field names)?

Here they are:
RowNo - Autonumber
TaxDecNo - Text
NameOfOwner - Text
Administrator - Text
MailingAddress - Text
Location - Text
Barangay - Text
Classification - text
PreviousNo - Text
Area - Number
MVLand - Number
MVImp - Number
MVMach - Number
AVLand - Number
AVImp - Number
AVMach - Number
YearBegins - Number
Remarks - Text

[ArviLaanemets]

1. Link all 7 Excel tables into Access database;

2. Create an Access table with structure identical with linked Excel tables (with atonumber field as PK);

3. Create a procedure which runs 7 INSERT INTO queries like

INSERT INTO YourAccessTable (TaxDecNo,  ..., Remarks)
SELECT exc.TaxDecNo, ..., exc.Remarks
FROM YourExcelTable1 exc LEFT JOIN YourAccessTable acc  acc.NameOfOwner = exc.NameOfOwner AND acc.MailingAddress = acc.MailingAddress
WHERE acc.NameOfOwner Is Null AND exc.NameOfOwner Is Not Null

3. Set up some event which runs the procedure and updates your Access table with new mailing info from linked Excel tables. When you also want to update changes made to old info, you need 7 UPDATE and 7 DELETE queries too.

[Marcia]

Big thanks for this Arvi. I will get try this once I have organized the 7 excel tables for merging.

[ssanfu]

Arvil has provided you with a solution. I just want to make a few comments about the code you tried.




You tried

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)

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):

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.

[Marcia]

Thank you again Steve for going the extra mile in expounding on the method of merging tables. I will try these for when the seven tables shall have been substantially cleaned of errors by the encoder. We find it easier to edit the data in Excel table than in Access.