Desciptions & Quantities

[Guest]

I'm trying to create a database so we can track the miscellaneous shipments
that our office sends to the warehouse. I've got most of it set up (the
address, the tracking, the type of shipment) but I'm having trouble with the
last part of it.

We have to fill out a section to describe what it is we're sending and how
many of it we are sending. For example, we can send 10 types of this
catalogs, etc. I can get it set up so that it will display one line of what
we send, but on some of thse shipments, we send multiple things (i.e. 10
catalogs of this, 1 cd, a t-shirt and a brochure).

How can I get each record to reflect a different list of quantities and
descriptions without it repeating on each record (I tried a separate table)
or only allowing one line (when all the info was on the same table).

 

[John Vinson]

I'm trying to create a database so we can track the miscellaneous shipments
that our office sends to the warehouse. I've got most of it set up (the
address, the tracking, the type of shipment) but I'm having trouble with the
last part of it.

We have to fill out a section to describe what it is we're sending and how
many of it we are sending. For example, we can send 10 types of this
catalogs, etc. I can get it set up so that it will display one line of what
we send, but on some of thse shipments, we send multiple things (i.e. 10
catalogs of this, 1 cd, a t-shirt and a brochure).

How can I get each record to reflect a different list of quantities and
descriptions without it repeating on each record (I tried a separate table)
or only allowing one line (when all the info was on the same table).

What you have here is a Many (shipments) to Many (items) relationship:
one shipment can contain many items, and each type of item (t-shirt
say) may be included in several different shipments.

The proper table structure for such a relationship involves THREE
tables:

Shipments
ShipmentID <Primary Key, maybe an autonumber, or some unique ID>
ShipmentDate
<other info about the shipment as a whole>

Items
ItemID <Primary Key, part number or autonumber>
Description <e.g. Men's large t-shirt style GHQ>

ItemsShipped
ShipmentID <link to Shipments>
ItemID <link to Items>
Quantity


See the Orders form in the Northwind sample database for an example of
how a very similar setup can be handled.

John W. Vinson[MVP]