Aug 27 2007, 08:38 PM
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?
Aug 27 2007, 09:29 PM
Perhaps a Union Query?
Select "Alpha" as SheetSource, NameField, ID, Amount from LinkedSheet1
Select "Beta" as SheetSource, NameField, ID, Amount from LinkedSheet2
Select "Gamma" as SheetSource, NameField, ID, Amount from LinkedSheet3
Aug 27 2007, 09:46 PM
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.
SELECT A, B, C, D, E, [color="blue"] 'LinkTableName1' As SourceTableName [/color] FROM LinkTableName1
SELECT A, B, C, D, E, [color="blue"] 'LinkTableName2' As SourceTableName [/color] FROM LinkTableName2
Aug 27 2007, 09:47 PM
Oops, I had the window open and did not realize that you responded Steve!
Aug 28 2007, 06:25 AM
No worries- hopefully between the two he can solve his problem!
Aug 28 2007, 08:24 AM
This is EXACTLY what I needed! Worked perfectly. Thanks so much to both of you!!!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here