access append-query: set column to a literal for each record

Question

hi,

i have multiple tables containing similar records. i want to merge them into one table. therefore i use an update query and map the fields from the various tables to the ones in my target-table. but i need to keep track from which table a record comes, so id like to add a literal "TABLE_XY" in the ORIGINALTABLE field in the resulting table to each record. but the query designer always wants a source-field. I cant just put a literal anywhere an select ORIGINALTABLE in "Append To"...

what to do? do i really have to add a NAMEOFTHISTABLE field to the original tables...?

thanks for your help!

Answer 1

Using the Query Designer in Design View for an Update Query:

Field: ORIGINALTABLE 
Table: <tableName>, where tableName is the name of the table you are updating.
Update To: "TABLE_XY", make sure to include the quotes.

Using the Query Designer in Design View for an Append Query:

Field: Expr1: "TABLE_XY", where Expr1 is an alias name.
Table: <leaveBlank>
Append To: ORIGINALTABLE

Answer 2

Make a backup copy of your database. Create a new query and switch to SQL View. Then paste in this statement, and modify the table and field names to match yours:

INSERT INTO master_table (
    ORIGINALTABLE
    , field1
    , field2
    )
SELECT
    "TABLE_XY" AS ORIGINALTABLE
    , field_a
    , field_b
FROM
    TABLE_XY;