Separate table for Package ID's and dynamic text boxes.

[wchelly]

My need: I need to track which packages went on which shipment. The majority of shipments would have 6 or so packages, I'm told we've shipped as many as 100 packages historically, I'd like to plan for the ability to track 150 - 200 packages to give us some cushion.

My problem: If we had no more than 10 or so packages for all shipments, I could easiliy encorporate them into my shipment table and include data entry for these packages on the main form. However, if I want to have 100 or even 200, the packages will need their own form, and table. Does it make sense to just create a foreign key that relates back to the shipment table, make 200 columns and make a column for each package? Or is there a better way to do it.

Secondarily: I would like to create a form which dynamically generates text boxes either after a number is entered into a text box on the main form.... 6 packages....therefore ....6 textboxes are generated. Or alternately, Make it so that one text box shows initially, and once data is entered, another text box appears...and repeat this until all package ID's are entered. Anyone have any examples of how I would go about doing this. I assume it would involve some coding. Below are the basic outlines of my tables.

Thanks in advance...

Shipments_Table

Shipment Number
Departure Date
Arrival Date
Package Type
Number of Packages

I have a separate table which lists all of the available package serial numbers. We need to have the ability to track package maintenance dates as well as where they've gone and when.

Package_ID

Package Serial Number
Current Package Condition
Comment

One additional table stores the Package Condition and Comment History

Package_History

Package Serial Number
Package Condition
Comment
Change_Date

Soon to be created table

Serial_Number Table
Shipment Number
Serial Number

 

[plog]

First, let's just try and get Packages and Shipments correct. Can 1 package be included in more than 1 shipment? For example could a shipment with 5 packages go somewhere, get broken down into a new shipment of 2 packages and another of 3 packages?

That question is going to determine if you have 2 or 3 tables for Packages and Shipments.

 

[wchelly]

Yes, 1 package can be on more than one shipment, but not at the same time. And no, they would probably never be broken up like that, at least not the way the system is set up currently. Typically if 5 are shipped 5 are delivered. Hypothetically that could happen but it would be very rare and there are other ways of dealing with the problem. Most likely a new shipment number (or 2) would be generated.

 

[wchelly]

However, I can think of a few situations where there were 2 destinations for one shipment. All packages were on one truck, but some packages were dropped off at the first destination while the rest stayed on the truck until it reached it's final destination.

 

[jdraw]

Does it make sense to just create a foreign key that relates back to the shipment table, make 200 columns and make a column for each package? Or is there a better way to do it.

No, not a column for each package.
A record for each package in a shipment.

You need to get your tables structured to represent the things you are dealing with and how those things relate to one another.

 

[plog]

...some packages were dropped off at the first destination while the rest stayed on the truck until it reached it's final destination

This should be thought of as 2 different shipments then. This is how I would design the Package/Shipment tables:

Shipments:
Shipment_ID (autonumber)
Departure (date)
Arrival (date)

Packages:
Package_ID (autonumber, may not be necessary if SerialNumber is unique to each package)
SerialNumber
Comment

ShipmentPackages:
ID_Shipment (links to Shipment_ID)
ID_Package (links to Package_ID)


This is how it works: You load all your individual package data into your Packages table. To create a shipment you create an entry in the Shipments table which gives you an ID number for that shipment, then you go into your ShipmentPackages table and start assigning package ID numbers to Shipment ID numbers.

This allows you to add an unlimited number of packages to a shipment without having to define columns for each package in a shipment. It also allows you to create a query to determine the quantity of packages in each shipment easily.

 

[wchelly]

Package_ID (autonumber, may not be necessary if SerialNumber is unique to each package)

Yes, these are unique numbers.

Can I create a new record in ShipmentPackages without having to enter the Shipment_ID? How do I make that happen?

 

[wchelly]

Hey that worked. I created the new table, created a multi-item form, automated an append query to put the shipperID at the top of the page, created a combo box for the Serial number and that is exactly what I needed. After I enter one serial number, a new box pops up so that I can create the next one.