Full Version: Append workarounds for linked tables
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
bvpmemail
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?
Clippit
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
dallr
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.
CODE
  
SELECT A, B, C, D, E, [color="blue"] 'LinkTableName1' As SourceTableName [/color] FROM LinkTableName1
UNION ALL
SELECT A, B, C, D, E, [color="blue"] 'LinkTableName2' As SourceTableName [/color] FROM LinkTableName2

Dane
dallr
Oops, I had the window open and did not realize that you responded Steve!
ane
Clippit
No worries- hopefully between the two he can solve his problem!
bvpmemail
This is EXACTLY what I needed! Worked perfectly. Thanks so much to both of you!!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.