Create field from append query based on linked table name

[Tommy]

Here's the setup:

Two linked tables called 'PHD' and 'XANS' bring in daily data from two
CSV files.
A union table-query puts the common data in both into the same name
fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a
new table called 'SOLS_MAIN' and I ran an append query called
'SOLS_DATA_APPEND' to append the data in the table-query,
'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for
this was so that I could assign my data a primary key.

Even though I have achieved my goal of merging the data from the two
linked tables with a primary key, I have no way of telling which data
has come from which linked table. Ideally I would like a new field in
'SOLS_MAIN' that contains the name of the linked table that the record
came from i.e 'PHD' or 'XANS'. I need a way of telling which linked
table each record has come from, because I need to run queries to
compare data from both. Is there a way I can create a field
automatically based on the name of the linked table??

Regards,

Tom

 

[Guest]

You can add a field in the union query like this ---
...., Your_current_fields, "Table_X" AS New_Field
UNION ALL SELECT ....