Splitting a table

[Sammie]

I want to split out the field TrackingNumber from the table Shipments into a
new table called Consignments. There are a number of other fields related to
the TrackingNumber such as weight, dimensions and ShipDate that should also
be a part of the consignments table. The relationship is many shipments to 1
tracking number
My problems:
1) not every record in the table Shipments has a tracking number
2) Some records in the new Consignments table without a tracking number have
other data in them, and still should be linked to the shipment.

What is the best way to split the table?
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7

 

[Allen Browne]

So one tracking number covers many shipments, but a one shipment will never
be broken into multiple tracking numbers.

You already have the Shipment table, and the TrackingNumber. One approach
would be to add a TrackNumberID field (type Number) to the Shipment table.
It starts out null (so you will need to remove the zero as default value for
this number field. At the point when you put the shipment into the tracking
number, you assign the relevant tracking number to the shipment.

Create a relationship between the primary key of TrackingNumber and
Shipment.TrackNumber. Thus, one tracking number covers multiple shipments.
Use referential integrity. (Even with RI, Access allows the Null in the
foriegn key.)

 

[Sammie]

Thanks, Allen. Your word "never" got my attention, and I should have thought
it through better. I guess occasionally one shipment will be broken into
multiple tracking numbers. Can you modify your response for me in that case?

 

[Allen Browne]

If you do need to handle both ways, you have a many-to-many relation.

So, instead of a TrackNumberID in the Shipment table, you need to create a
junction table. The new table will have fields:
- TrackNumberID relates to the primary key of the TrackingNumber table
- ShipmentID relates to the primary key of the Shipment table

To assign shipment 99 to tracking numbers 22 and 23, you create 2 records in
this table:
22 99
23 99