Access Inventory DB

[Wirediron]

Hello,
I am trying to build an Acess inventory DB that has identical products but
different sizes - from XXS, XS, S, M, L, XL, XXL. How do I buid the product
table that includes sizes? Maybe a separate size table?
Any help would be greatly appreciated!

Thank you!

 

[Allen Browne]

Yes, you do need a separate size table.

You need fields for:
Size Text (primary key?)
SortOrder Number
so you can sort XS before S before M, and so on.

Depending on what kinds of products you are selling, this can get messy.
Some garments come in size 8, 10, 12, etc. Others come in a waist
measurement, or neck measurement, which may be in inches or centimetres.
Some jeans have a leg measurement as well, which may have a suffix such as S
or R, or may actually be a further measurement number. Shoes come in
fractional sizes, and different size ranges for children than adults, and
may also have a suffix for width.

So, you can end up with hundreds of entries in your Size table, with various
combinations of which ones apply to what categories of product. If you need
to restrict the entries in a drop-down list to the sizes appropriate to the
product, you would need another table for each valid combination of Category
and Size. (This is where using the actual Size text field as primary key is
helpful: if you hide the primary key value and filter the combo, you can end
up with entries "disappearing".)

 

[Mike Painter]

Hello,
I am trying to build an Acess inventory DB that has identical
products but different sizes - from XXS, XS, S, M, L, XL, XXL. How do
I buid the product table that includes sizes? Maybe a separate size
table?
Any help would be greatly appreciated!

I'd go out and buy MYOB which has one of the better inventory modules for
this type of product.

Another poster mentions a separate table but left off the cost, if different
between sizes and quantity on hand fields. You can't keep the quantity in
the master table.

Then there's colors....

I would probably bend rules here and keep them all in one table Product,
size, color, etc.
This allows easy assignment of a part number that identifies a unique
product/size/color.