Combine two databases into one

[SJ]

I have an Access app where users point to a database and I link to the
tables. The new requirement is that they would like to point to two
databases with the same table and field names and use it as the datasource.

There is not a primary key on any of the tables I link to. In addition,
there is some data that is repeated in both databases. The combined file
should only contain unique values between the two databases.

I need to combine the all the tables in each of the two databases into one,
but with unique values only. I am thinking of using a SELECT DISTINCT from
the UNION ALL of the two tables in each database, but I am open to
suggestions.

If anybody can point me to sample code to get started, it would be
appreciated.

 

[John W. Vinson]

I have an Access app where users point to a database and I link to the
tables. The new requirement is that they would like to point to two
databases with the same table and field names and use it as the datasource.

There is not a primary key on any of the tables I link to. In addition,
there is some data that is repeated in both databases. The combined file
should only contain unique values between the two databases.

I need to combine the all the tables in each of the two databases into one,
but with unique values only. I am thinking of using a SELECT DISTINCT from
the UNION ALL of the two tables in each database, but I am open to
suggestions.

If anybody can point me to sample code to get started, it would be
appreciated.

Do you want to actually permanently combine the two databases into one, and
discard the other one? Or do you want to keep the two databases distinct and
just (somehow) dynamically combine the data? Is the repeated data repeated
*exactly* or do you have "pretty close" repeats, such as 312 Main St. in one
database and 312 W. Main Street in the other?

This can be done, but it can range from being pretty easy (a UNION query) to
nightmarishly difficult... and it's a very good reason to try to keep all the
"same" data in the "same" place to begin with!

More details please...

 

[SJ]

Yes, ideally I would like to combine the two databases into one. 99% of the
time the users will be working with a single database, but there will be
times where they will need to select two databases. The databases I am
linking to a furnished by a federal governmant agency. There are not any
primary keys and the data is not 'normalized'. I do not have any control
over the source data.

The data repeats will be EXACT. There is one large table with about 60
fields. The repeats will be for all 60 fields in the two databases.

 

[John W. Vinson]

Yes, ideally I would like to combine the two databases into one. 99% of the
time the users will be working with a single database, but there will be
times where they will need to select two databases. The databases I am
linking to a furnished by a federal governmant agency. There are not any
primary keys and the data is not 'normalized'. I do not have any control
over the source data.

The data repeats will be EXACT. There is one large table with about 60
fields. The repeats will be for all 60 fields in the two databases.

Well, it's still not clear to me what you want to do.

You have two databases that you do not control... right? But you want to
combine them? What if the government agency changes the tables? Will you want
the combined database to reflect those changes?

All I can suggest is that you investigate UNION queries. a UNION query splices
two sets of data together into one, discarding duplicates in the process; e.g.

SELECT A.Field1, A.Field2, A.Field3, ..., A.Field60 FROM A
UNION
SELECT W.FieldA, W.FieldB, W.FieldC, ..., W.FieldJK FROM W

will give one recordset with fieldnames Field1, Field2, etc. (the fieldnames
in the first SELECT), with all records from table A followed by all records
from table W, with exact duplicates removed.

 

[ryguy7272]

In "union" only distinct values will be selected but in "union all "values
will be selected irrespective of its frequency -- union all will return ALL
records, even dupes.

HTH,
Ryan--