append but add original table reference

[pmjf1977]

Hi all,
I have been passed a part complete project and asked to tidy the data for a review. All simple so far.
the data is in 7 unique tables representing groups of data.
How do I pull all data to a single table but retain an indicator for the source table?

The idea is to give a master data set that can be filtered and reviewed by the 7 divisions as needed.
I know there is a way when appending tables to add a column and use the expr here but cant think how this is done.

really appreciate any help (as simple as possible - I am not great with access yet but always willing to learn)
cheers

 

[pmjf1977]

I should add there are 22 columns (common to all tables) and various rows min 75,000 max 1,100,000

 

[Trevor G]

I would suggest creating a temp table for the appended records to go into, making sure the ID field is set to a number only then you don't get any conflicts. If you want to state which table the data came from then you could add an extra field to the Temp Table and name it something like TableName and then in the append queries you would add an expression to Add the Table name to this field.

If seven queries go into this temp table you then have an identifier where the data came from. Run all of the queries from a macro using separate Actions (1 for each query), switch the warnings off (and switch back on once it has done everything) in the actions and add a simple message box at the end to state all completed).

If you have to do this a number of times to refresh the temp table then also include a delete query to delete the temp table data first and include that as the first action in the macro.

 

[pmjf1977]

Thanks Trevor.
I worked through this by creating a new table as you suggested but introduced a blank expression field requesting table number. Then used the basic append function. the expression requested the table number each time and added it to the new base table.

 

[Trevor G]

Peased to read you have a working solution and thanks for letting me know.