mapping tables 1

[swtrader]

Given: 3 separate databases. An "Individuals" table was created in each of the 3 dbs. Each individual was input into the separate dbs at different times resulting in different (automatic) primary keys.

I need to normalize these dbs using the IndPKs which are in table 1 to map records in tbl2 and tbl3 so that I end up with 1 database containing 4 tables: Individuals (with PKs in Table 1), RecordsFromTbl1 (foreign key IndPK), RecordsFromTbl2 (foreign key IndPK), and RecordsFromTbl3 (foreign key IndPK).

As an example, I now have JoeSmoe with an pkID in tbl1 of "3", in tbl2 of "19", and tbl3 with "28".

There are only about 150 individuals and 50 or so other records in each table. Even if I do this manually, it won't take long but I would like suggestions on how this can be done most easily (both on these small ones and on largers dbs, when necessary).

As usual, thanks for all the help.

swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.

 

[fneily]

Now you know why you shouldn't use autonumbering except in very rare stable tables. This has been explained many a time in these forums. Not even the primary keys have to be changed, but three different foreign keys must be changed into one. It would take more time to figure out how to convert everything through a program then to just redo it correctly. In fact, by the time you get a good answer, you're probably finished.
Maybe set up a cross reference junction table where the first value is the new primary key to the Individual table and the next value is the foreign keys for that person. Together they'll form a multi-field primary key for the table:
JSmo1 3
JSmo1 19
JSmo1 28
etc.
You should make up a primary key such as, first initial, three letters of last name and a number. eg jsmi1, jsmi2, bkei1, etc.
Can the three other tables be consolidated along normalization?

If you need to do autonumbering for some reason here are the main ways to create one:
Let’s say you want the numbering to begin with a different number other then 1. Let’s say 57. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 56. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 57. Delete record number 56.

Let’s say you want to create your own autonumber field. Create a table with a field called ID. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

Let’s say you want an autonumbered field by Microsoft but want some characters in front of the number like mc001, mc002, etc. Go to design view, click on the autonumber field and in the Format box type “mc”00
This places mc next to 2 zeros and then tacks on the autonumber.

 

[swtrader]

I finally did it manually. Your input, fneily, will be valuable on future projects. Thanks.

swtrader
-- The trouble with doing something right the first time is that no one appreciates how hard it was.