CODEWITHSUNDEEP
sqlms-access
Rodrigo
Rodrigo

Reputation: 67

MS Access merge/join two tables adding a new field

I need to change the structure of my database for a new project and I need to merge two tables in one new table. Both tables has exact the same structure.

The problem it is that I need to add a new field in the final table with the name of the origin table where each row cames from.

Below I upload a picture that will clarify my problem.

enter image description here

Is is possible to do this using a SQL statement in MS Access. Or there is another way to do this?

Any help will be appreciated.

Thanks in advance.

Upvotes: 0

Views: 63

Answers (1)

June7
June7

Reputation: 21379

Of course, any SQL can have a calculated field.

Create the new table then run two INSERT SELECT actions, changing store reference as appropriate:

INSERT INTO Stores([date], item, quantity, price, store) 
    SELECT [date], item, quantity, price, 1 AS store FROM store_1;

Or build a UNION query and use that query as source for subsequent queries/actions.

SELECT [date], item, quantity, price, 1 AS store FROM store_1
UNION SELECT [date], item, quantity, price, 2 FROM store_2;

Must use SQLView of query builder to type or copy/paste UNION query.

NOTE: Date is a reserved word (intrinsic function) and advise not to use reserved words as object names.

Upvotes: 2

Related Questions