I have many excel spreadsheets that have the same data structure. I edit these often, so they brought into Access as linked tables. To normalize my dataset, I need to combine these tables, plus add a column that indicates the name of the linked table from which the particular row of data is drawn. Where each source table has the same five columns A through E, I seek to create a combined dataset that has columns A, B, C, D, E and SourceTableName.

***I'd prefer not to use an append query since the source spreadsheets will be modified frequently and I don't want to have to recreate the non-dynamic table every time data is revised.***

Is there a method that serves to combine and add the source table name, without loosing the ability for changes in the spreadsheets to flow through when I update the links?

Perhaps a Union Query?

for instance:

Select "Alpha" as SheetSource, NameField, ID, Amount from LinkedSheet1
Union
Select "Beta" as SheetSource, NameField, ID, Amount from LinkedSheet2
Union
Select "Gamma" as SheetSource, NameField, ID, Amount from LinkedSheet3

Welcome to UA!!

You will need to use a UNION query here but to get the SourceTable name you will have to change things a bit. Pay particular attention to the part in blue.




Dane

Oops, I had the window open and did not realize that you responded Steve!

Dane

No worries- hopefully between the two he can solve his problem!

This is EXACTLY what I needed! Worked perfectly. Thanks so much to both of you!!!