Merging data from tables in different databases

[Guest]

Hi,
Is it possible to merge data from tables, obviously with the same column
headings, in separate databases?
We have 2 databases set up by different individuals, with some common data
and a lot different that needs to be merged, we are trying to find a way of
avoiding a great deal of retyping!
Thanks
Leigh

 

[Douglas J. Steele]

What exactly are you wanting to do: get all of the data in one central
database, or keep the data in the individual databases, and simply be able
to report on it?

In either case, you can link to the tables in the individual database
through File | Get External Data | Link Tables. Once you've done that, for
option 2 above, you'd create a Union query that would take the data from
each of the individual tables and combine it into a single recordset. (If
you use UNION, it will eliminate duplicate rows: UNION ALL will keep the
duplicates. If you want to know where each record comes from, you can add a
computed field indicated that to each subselect in your Union query)

For Option 1, use the Union query described above as the source for an
Append query to add the data to a new table in your database.

 

[Guest]

Douglas,
Thanks for reply, it is Option 1 that we want, and it sounds as if we can
get the result we need.
Thanks
Leigh

 

[Guest]

Douglas,
Can you clarify whay this involves << If you want to know where each record
comes from, you can add a

computed field indicated that to each subselect in your Union query>>

I did not really understand what to do here.
The main bit worked OK on a test database. I am having some problems on the
real ones but I think I need some more time to try it.
I would like to see where the data originate so if you can help with the
above I would be grateful

Leigh

 

[Douglas J. Steele]

Assume you've got 2 tables named LinkedTable1 and LinkedTable2, the first
one in Joe's database and other in Mary's. Further assume that the tables
have 3 fields, Field1, Field2, Field3.

Your Union query could be something like:

SELECT Field1, Field2, Field3, "Joe" As Source
FROM LinkedTable1
UNION ALL
SELECT Field1, Field2, Field3, "Mary" As Source
FROM LinkedTable2

 

[Guest]

Douglas,

I have managed to combine the two tables successfully, many thanks, hours of
retyping avoided!!
In fact there was a field in one table that was empty, but all the records
in the other contaaind data so it was easy to see which came from where.
Initial problem was that the field names were not identical in the two
tables e.g Event Date in one and EventDate in the other, this took me a
while to spot.
Thanks again
Leigh