Merging Tables

[jlcford]

I have two tables that I need to merge into one table. One table has the following fields:

My first table is a Select Distinct table from my second table

id (autonumber primary key), Bill_No, Equip_Int, Equip_Nbr, Repair_Dt, Repair_Order_No, SumOfAmt_Billed

My second table unfortunately has a concatenated primary key

Bill_No, Equip_Int, Equip_Nbr, Repair_Dt, Repair_Order_No, Amt_Billed - with the first 4 fields being concatenated fields as primary key in this table.

So my third table would be

id, Bill_No, Equip_Int, Equip_Nbr, Repair_Dt, Repair_Order_No, Amt_Billed

Any help would be greatly appreciated. I basically want to add the autonumber primary key that I have given my summary table to the third table. But the third table will have to use the same concatenated primary key of the second table because the id field will not be unique in the third table.

Thanks,
jlcford

 

[Kryst51]

If you are adding an autonumber ID to the third table, then that would indeed be a unique identifier and there is no need to concatenate a primary key.

I don't think I really understand your question. Are you having problems figuring out how to actually put the data together, like using append or update queries?

 

[SOS]

1. You are duplicating data which is a bad normalization problem.

2. You have multiple tables with the same structure and data - again bad design.

3. as for the keys - I can't say this enough - don't use keys with meaning. Just let Access use autonumbers for the primary keys. Access uses the keys for maintaining relationships and you, and your users, should not care what they are. They should not be seen, just used in the background. If you want a concatenated number for other purposes, so be it. But let Access manage the keys. If you don't, most likely at some time it will come around to bite you.

 

[Kryst51]

I think that jlcford realizes this mistake in his design and is trying to correct it by merging the first two tables into one (the third). And, I completely agree with you. Although, his wording is a bit confusing.

 

[jlcford]

I am working with a database that I was given, not one I design so I totally agree with your criticism about the normalization issues. The 1st table is made from a query I wrote to sum the costs of repairs on jobs that only have the concatenated key which is made up of 4 fields. A bit difficult to work with so I gave each record in the sum table an autonumber primary key. Now what I would like to do is go back to the original table an assign the autonumbers to the items I used to create the sums for each total repair cost.

For example you have a truck damaged in a yard: the tires, rims, and bumpers are all damaged. In my database all of these items are given the same Bill_No, Equip_Int, Equip_No, Repair_Date, and Repair_Order_No (believe it or not), but each of these items does have a specific Amt_billed. When you combine the four fields, that makes you primary key or one incident.

For the second table I have created a query Select Distinct and Sum Amt_billed and then created an autonumber for the primary key.

Somehow I would like to be able to associated the autonumber id I assigned in the second table with the multiple records I summed in the first table so I could associate those records in listboxes and other queries.

Or any other suggestions you have to solve this normalization problem.

jlcford

 

[Kryst51]

Steps that might work to normalize.

For purposes of this explanation Your table number one will be called SUM
Your second table will be called HeaderDetails

1.If SUM does not contain any unique information (other than the sum) that is not contained in HeaderDetails, then delete it.

2. Add a primary key called “HeaderDetailID” and make it an autonumber.
3. Create a copy of HeaderDetails and Call it “RepairDetails”
4. Delete from RepairDetails the following fields:

Bill_No, Equip_Int, Equip_Nbr, Repair_Dt, Repair_Order_No

5. Delete from HeaderDetails the following fields:

Amt_Billed

6. Add to RepairDetails the following fields:

PartName, BilledAmt

7. Create a linking table (Call it something like "RepairHeader" which looksup the primary keys of each of the other two tables. And make those foreign keys the primary key of the linking table.

8. By some means (And I don’t know the best way) enter the info into the linking table (RepairHeader), the primary keys of both tables should correspond right now as you have not added or deleted any records from either. PK1 from table Header Details should correspond with PK1 from table RepairDetails etc.