Combining data from 2 same structue databases

[AK]

I have 2 databases BowlingLeague1.mdb and BowlingLeague2.mdb

BowlingLeague1.mdb has info on teams 1,2,3
BowlingLeague2.mdb has info on teams 4,5,6

There are some Access AutoNumber fields in both databases.

I am trying to ??combine/merge/append?? (don't know the right term)them so
that I have a single DB MasterBowlingLeague.mdb that has all the data for
all the teams.


Can I just copy and paste the info from one table into the other. or is
there some merger/combiner program/utility I need to use?


Any direction appreciated.

Thanks
AK

 

[Ron2006]

For a one time shot the copy and past will work fine IF

A) there is not a unique key (indexed with no duplicates) in the the
tables that will be duplicated between the two tables.

B) the field are in the exact same sequence in both tables.

BACKUP YOU TABLES BEFORE YOU DO THE COPY AND PASTE.

Ron

 

[Jeff Boyce]

If each database (i.e., .mdb file) is using Autonumbers as a primary key
field in the "same" tables, you can't simply copy and paste (or link-to &
append) because your Autonumber primary keys may be duplicated.

It all starts with the data. I have no idea what data you are storing.
Please describe your tables.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ron2006]

If each database (i.e., .mdb file) is using Autonumbers as a primary key
field in the "same" tables, you can't simply copy and paste (or link-to &
append) because your Autonumber primary keys may be duplicated.

It all starts with the data.  I have no idea what data you are storing.
Please describe your tables.

--
Regards

Jeff Boycewww.InformationFutures.net

Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/












- Show quoted text -

If you use queries for the two tables that DO NOT include the
autonumber field it can work.

Ron

 

[Ron2006]

And if you want to do it programmatically or have to do it more than
once

BACKUP THE TABLES

and THEN

A) Create an append query using the query wizard -
1) Select one of the two team tables
2) Change the query type to Append and select the other team
table as
the one you want to append to.
3) Select ALL of the fields from the table (do not include the
ID field (or any autonumber field) and drop all of those fields onto
the grid.


B) Run the query.


Now as long as there was no duplication of any information that was
indexed with no duplicates, then all the teams are in the single
table.

Ron

 

[Douglas J. Steele]

If you use queries for the two tables that DO NOT include the
autonumber field it can work.

And what happens if the autonumber field is being used as a foreign key in
some other table?

Jeff's correct: it's not a simple matter, and without knowing the design of
the tables, it's pretty much impossible to give a definitive answer.

 

[John W. Vinson]

A) Create an append query using the query wizard -
1) Select one of the two team tables
2) Change the query type to Append and select the other team
table as
the one you want to append to.
3) Select ALL of the fields from the table (do not include the
ID field (or any autonumber field) and drop all of those fields onto
the grid.


B) Run the query.

However, if the Autonumber pk in one table is linked to corresponding foreign
keys in another table or tables, those child tables' foreign key values will
not be updated.

This operation can be pretty simple... or it can be a mind-numbing tedious
chore.

John W. Vinson [MVP]

 

[Ron2006]

You are right John. I was approaching the problem VERY simplistically.

Therefore, AK, be forewarned: Without further information the
solutions offered so far may or may not work depending on what we
DON'T know.

Ron

 

[AK]

Hello All,

Thanks for the info...and I was digging into it while messages were being
composed and I too find that it is a real can of worms and that like
everybody says, it can be an easy or a complex task depending on the data
structure.


As far as my structure, the bowling teams have a player ID that is an auto
number field, and then in the tables that have player stats there are
autonumber fields.

I am going to use the info here to do some test cases ALWAYS with a BACKUP
to preserver the original!



I will let you all know

Thanks AK

 

[AK]

I do not have this condition...thank goodness!

AK

 

[AK]

Ron,


I have one table that has an autonumber field for a primary key and this is
linked to the other tables (which also have primary key autonumber field

For example; In Table Players there is an autonumber field called PlayerIDIn Table PlayerStats there is an autonumber primary
key Field called BatIDIn Table Bats there is a field BatID


Here is the situation:
DB1 has Table Players with AutoNumbered PlayerID's 1,2,3

DB2 has Table Players also with AutoNumbered PlayerID's 7,9,11

Trying to get DB2 "into" DB1 so that I have Table Players with Player IDs,
1,2,3,4,5,6 etc

OR PlayerIDs 1,2,3,7,9,11


On those Append Queries, DO I have to to it on a table by table basis, or
cans I set things up to query from a bunch of tables in the source and
append them to same named tables in the target




AK

 

[Ron2006]

1) Table by Table.

2) Now for a way around the player ID problem. Here is one way around
it that I have used for conversions

You will need to do this for the tables that you are appending:
A) Add a new field to the table that will become the final combined
employee table- Name it something like "OldIDNumber"
B) In the append query that is looking at the table to be added and is
appending to the final combined version have the query update the
"OldIDNumber" field with its ID number.
C) Do the same thing with in the table playerstats but in the
playerstats append query place the old "PlayerID" field into this new
field (not the actual record ID). This step will be required for any
and all sub tables that have this type of relationships ie. PlayerID
pointing back to ID of player table.)
D) Now create an update query that is linked by oldIDnumber in player
table to OldPlayerID in playerstats table and update the PlayerID in
the stats table with the new playerID from the Player Table. BE sure
to place a criteria on the update that says ignore anything with 0 or
Null in the old match field.

After all the paired tables have been updated you can drop the new
fields. I would suggest waiting a while BEFORE you do this. You may
find something still not loaded only after you get into some rare
reporting scenarios. It's called CYA and the extra space is marginal.

Ron

 

[AK]

Thanks

I will add this to my bag of tricks.


AK