Access - Products database pack sizes


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.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
See: Inventory Control: Quantity on Hand at

Dale FyeOwner, Dev-Soln LLCCommented:
You could add a table for Product_Packaging, which would include fields like:

-Pkg_Size (ea, 6Pk, 12Pk)
-Prod_Pkg_Price - remove the price from the Products table
-Prod_Pkg_Size - depending on what Size means (Sm, Med, Lg) or (shipping dimensions) you might need to move the Prod_Size from the Products table into Prod_Pkg_Size

Then, you would need to remove the Product_ID from the Order_Items and Invoice_Line_Items tables and replace it with Prod_Pkg_ID.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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


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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.