asked on

Access - Products database pack sizes

Hi

I was given the following link for a simplistic database structure for handling the debtors side of an Access accounting type of system. I need to incorporate pack sizes into the model. For example a product comes in pack sizes of 6 or 12. What would be the best way to incorporate that into this model? In other words, which table would it go into or would it perhaps be in a separate table.
http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm

Thanks

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

See: Inventory Control: Quantity on Hand at http://allenbrowne.com/appinventory.html

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

thanks very much

Jim Dettman (EE MVE)

FWIW, pretty much ditto what fyed said, but in addition add a conversion factor and inner pack size. Each product would be required to have an Each UOM, then one record for each pack/inner pack size.

I would also not call it a prod_pk_ID, but rather a UOM (Unit of Measure) and the invoice line items would have a product ID along with a SellUOM.

Many times you will see this done in systems with:

BaseUOM - Each
SellUOM - UOM that the selling qty and price represents
ShipUOM (sometimes PickUOM) - UOM that the shipped qty and price represents
PurUOM - UOM that purchased qty and price represents

Rather then the above, some systems will also break the qty's from the prices with individual UOM's for each. ie.

SellQtyUOM - 12CS
SellQty - 1
SellPriceUOM - Each
SellPrice - $1.23

vs

SellQtyUOM - Each
SellQty - 12
SellPriceUOM - 12CS
SellPrice - $14.76

Jim.