Creating a load list for shipping

[Reaper]

I need to create a load list when building a load for shipment.

There are two tables involved. tblInventory and tblLoadList.

The load list table will have information about the shipment, Shipped To, Truck #, Date, Loader, Load ID# (pk),etc.



The Inventory table has listed the inventory by pallet number. Each pallet has a unique Ticket # (pk).

My vision for this is a form/subform with the Load list as the main and the inventory as sub. When the form is opened the Load ID would fill as the PK Autonumber and then the operator would fill in the ShipTo, etc.

Where I am getting stuck is in the sub form. The form should start out empty. Then as the load is built the pallet ticket number would be entered and the appropriate data for the pallet would be autofilled. This is real similar to an "Order Form" were the item number is entered then the description information is auto filled, but I'm lost.

After completing the load list I need to change one field in the inventory table to update the pallet's "location".

Any help would be appreciated.

[orange]

Can you show us your table structure?

Also, advice -- don't use names with spaces or special characters (#)?
Don't use Access reserved words in names see
http://allenbrowne.com/AppIssueBadWord.html

[Reaper]

tblPickList

strPickLoad_ID:Autonumber (pk)
strPickDate:Medium Date
strPickTime:Long Time
strShipTo: Text
strCarrier: Text
strTrailer#: Text
strShipment#: Text
strPickedBy: Text
strLoadedBy: Text
strSeal#: Text

tblInventory

strTicket#: Autonumber(pk)
strCommodityDescription: Text
strGross: Number
strTare: Number
strNet: Number
strPackaging: Text
strTransactionDate: Medium Date
strTransactionTime: Long Time
strSource: Text
strDestination: Text
strPickLoad_ID: Number

There is a One to Many relationship keyed on the strPickLoad_ID field.

Each strTicket# represents one pallet of commodity to be sold. For example; Aluminum, copper wire, etc. When the pallet is sold a Pick/Load list is created for the shipment that includes the ticket#. There could be as many as 52 pallets per truck load. Then each ticket# 's location (strDestination) is changed from "Finished Goods" to "Shipped", thereby effectively deleting it from the warehouse inventory while still saving historical data.

Thanks,

[orange]

Is tblPicklist the same as tblLoadlist?
Is this part of a larger "view" database, including deliveries, customers, invoices etc?

You may get some ideas from the following: (parts of these may apply)
http://www.databaseanswers.org/data_...ries/index.htm
http://www.databaseanswers.org/data_...ries/facts.htm
http://www.databaseanswers.org/approach2db_design.htm
http://www.accessmvp.com/strive4peac...Chapter_03.pdf

[Reaper]

The tblLoadlist is the same as tblPickList. The first one was a typo, my apologies.

The only part I have not included here is the forms that are used to create the entries in the inventory table. Its not part of a larger database for invoicing or deliveries, It is stand alone and used only to track inventory thru the warehouse.

[orange]

Did you look at the links?

[Reaper]

Yes, I did. I am familiar with the basics of what these present.

My specific concern is how do I get these two tables to interact in the way I have described. If I have not made myself clear, I apologize.

The difference between the normal orders process and what I am after is this: the normal orders process pulls item data from inventory in a general way and then reduces the inventory count accordingly. In my process, I may have 10,000lbs of scrap wire, but it is sold by the pallet, each having a different weight. Therefore, when I build a shipment I must pull inventory by specific pallet ID#'s to get the specific pallet that the the customer bought. Each pallet is more like it's own item than having 10,000 widgets and shipping 100 on an order, ending up with 9,900 left.

[orange]

Do you have some sample data?
Do you have a list of transactions that this database will support?

As for your names, the str prefix is good for string/text datatypes.
For Gross, Tare and Net, which are numbers, you could use nGross, nTare etc.
Also, If Net = Gross - Tare, you don't need to store Net. It can always be calculated.

As for strTransactionDate: Medium Date
strTransactionTime: Long Time you don't have to store Time. General Date should include Time 12:45:14PM etc. Same applies to strPickDate:Medium Date
strPickTime:Long Time

It seems you may need a lookup table or a few to handle
ShipTo, Carrier, Source, Destination, Packaging, CommodityDescription,....

[Reaper]

Yeah, I know all about lookup tables. That's not my problem and I don't need a database grammar lesson. You know what, never mind. I'll go somewhere else.

[orange]

I was putting some tables together, and was looking for some test data and transactions to help get this working and work through it with you. However, it's your choice to get help/advice elsewhere.
Good luck with your project.