Splitting a table

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

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sammie" wrote in message
news
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

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?
--
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7


"Allen Browne" wrote:

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sammie" wrote in message
news
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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sammie" wrote in message
...
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?
--
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7


"Allen Browne" wrote:

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 .
shipmentsUse referential integrity. (Even with RI, Access allows the Null
in the foriegn key.)

"Sammie" wrote in message
news
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 1tracking 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