Merge Tables/Databases

[Guest]

I have 20 tables/separate databases all with the same fields
I want to merge the data into 1 access database

When they merge I want access to add another field to the new merged table
‘DBID:’ the entry would be generated from one of the 1 – 20 single databases
e.g. DB-3 if the entry came from Database 3

External Tables:

Table (DB-1)
ITEM NO Name: Price1 Price2 Cost
88800001 Item Alpha 1.20 0.87 0.23
88800002 Item Delta 1.40 0.93 0.28

Table (DB-2)
ITEM NO Name: Price1 Price2 Cost
88800001 Item Alpha 1.20 0.87 0.23
88800002 Item Delta 1.40 0.93 0.28

Table (DB-3)
ITEM NO Name: Price1 Price2 Cost
88800001 Item Alpha 1.20 0.87 0.23
88800002 Item Delta 1.40 0.93 0.28

Table (DB-4)
ITEM NO Name: Price1 Price2 Cost
88800002 Item Delta 1.40 0.93 0.28


New merges table:

KEY DBID ITEM NO Name: Price1 Price2 Cost
1 DB-1 88800001 Item Alpha 1.20 0.87 0.23
2 DB-2 88800001 Item Alpha 1.20 0.87 0.23
3 DB-3 88800001 Item Alpha 1.20 0.87 0.23
4 DB-1 88800002 Item Delta 1.40 0.93 0.28
5 DB-2 88800002 Item Delta 1.40 0.93 0.28
6 DB-3 88800002 Item Delta 1.40 0.93 0.28
7 DB-4 88800002 Item Delta 1.40 0.93 0.28

 

[Pieter Wijnen]

SELECT MyTable.*, 'DB-1' AS DBID FROM MyTable IN 'C:\DB-1.mdb'
UNION ALL
SELECT MyTable.*, 'DB-2' AS DBID FROM MyTable IN 'C:\DB-2.mdb'

etc

Should get you going

Pieter

 

[Guest]

You could use a union query with a derived field like:
SELECT ItemNo, [Name], Price1, Price2, Cost, "DB-1" as DBID
FROM [DB-1]
UNION ALL
SELECT ItemNo, [Name], Price1, Price2, Cost, "DB-2"
FROM [DB-2]
UNION ALL
SELECT ItemNo, [Name], Price1, Price2, Cost, "DB-3"
FROM [DB-3]
' --- etc ---