Sizes?!?!?!?!?

[redblaze]

i have a ProductTable which includes various products of different sizes. the only thing is, if one item comes in many sizes (S, M, L, XL) what would be the best way of representing this in the table.

at the moment i have each product with each size
EG
LIV001 - M Owen T-Shirt - Size=M
LIV002 - M Owen T-Shirt - Size=L
LIV003 - M Owen T-Shirt - Size=XL

what i want is this

LIV001 - M Owen T-Shirt - Sizes=M,L,XL

but so the user only has to select one (M or L or XL). i think the check boxes could be suitable but i have no idea on how to go about it! can anybody help me with this or any other possible solution. thank you!!

 

[Jerry Stoner]

Why not make a size table tblSize and link to your main table one to many by SizeID?

 

[redblaze]

im not sure im with u, could you expand on that to make it easier to understand..i mean what method would users use to select sizes if there was a seperate table????????????

 

[Jerry Stoner]

Make a table - two fields - SizeID and Size. In your main table add a field SizeID. If you want you can use the lookup wizard to actually put a combo in your table. Now when you make a form you use a combo box to select size.

 

[redblaze]

but wouldnt that only enable me to select one size per product??? i need to have more than one size for each product in one record.

if i select a size from a combo i can only get one size..?

 

[Jerry Stoner]

Why would you want to do that?

 

[The_Doc_Man]

Sounds to me like your detail records aren't detailed enough.

Suppose you have an order that says,

"Give me two XL Mickey Mouse T-shirts and three L Mickey Mouse T-shirts"

How many records are you going to enter into your database? If your answer is 1, your tables aren't normalized fully. This will lead to difficult questions exactly such as this one. You essentially have a conflict between quantities of non-elementary data items.

If you treated that same order as a single request with two line items, then your line items would read

Line 1, Order #1761, Qty 2, Size XL, Model Mickey Mouse T-Shirt
Line 2, Order #1761, Qty 3, Size L, Model Mickey Mouse T-Shirt

Then you would have a separate order record numbered 1761 that showed whatever summary information was needed. And at the line-item level you have no ambiguity whatever.

 

[Jerry Stoner]

Thanks_Doc_Man

Didnt know how to word it and now I dont have to!
Well put.

 

[redblaze]

well im going to have around 300+ products n the database, and to save space it would obviously be easier to have one record including each sizes available

Item#1 T Shirt (Large, XL, M, S)

in stead of Item#1 T Shirt Large
Item 2 T shirt small ect...........

There are several details for each product including size, quantity ect. so instead of typing each record for each size isnt there a way to make each record for all sizes???? sorry if this sounds confusing im beginning to confuse myself!!!?

thanks for your help

 

[Pat Hartman]

I'm sure that not all items come in all sizes. Some items have a different series of sizes - 6, 8, 10, 12, 14, 16 for women's dresses for example. Plus, if you need to keep inventory, you need a separate record for each size to record quantity on hand, etc. Then of course there is the problem of color.

I cast my vote for a two table setup to handle the 1-to-many relationship with sizes.

An alternative, if you don't need to keep inventory, is to assign group codes to the various sets of sizes. So for example, if you have products that come in L, XL and others that come in S, M, L and others that come in XS, S, M, L, Xl, XXL, you can assign group codes and have two tables such as:

tblProduct
ProductID
ProdDesc
SizeGroupID
etc.

tblSizeGroup
SizeGroupID
SizeGroup

So for group1, tblSizeGroup will have 3 rows, group2 wil have 2 rows, and group3 will have 6 rows. This structure minimizes the number of entries in the second table and it provides the source for a combo. When a product is selected in combo1, combo2 should be set to show the appropriate size choices.

 

[redblaze]

great, thanks pat ill give it a whirl...one other question is how would the user select the size when making the actual order in the form, any suggestions??

thanks.

 

[Rich]

Use a combo box which contains the list of available sizes, use the Wizard if your unsure how to do this

 

[redblaze]

sorry about this.. im still having problems with getting the whole thing to work. iv made the size table but im unsure of how to have to form so that there is only 1 record containing several sizes but so they can only choose one size to order. can anybody help me out.... PLEASEEEE!!!!

 

[Pat Hartman]

tblSizeGroup should contain something like the following:
1 M
1 L
1 XL
2 S
2 M
2 L
3 XS
3 S
3 M

Your product record will contain one of the three codes - 1, 2, or 3. You need two combos. The first to select the product and the second to select the size. The size combo needs to be based on a query that contains selection criteria that refers to the product just selected in the first combo:

Select SizeGroup
From tblProduct InnerJoin tblSizeGroup on tblProduct.SizeGroupID = tblSizeGroup.SizeGroupID
Where tblProduct.ProductID = Forms!YourFormName!YourProductCombo;

Then in the AfterUpdate event of the product combo, requery the size combo to restrict its rows to only those for the SizeGroupID in the Product record:

Me.YourSizeCombo.Requery

 

[redblaze]

THANK YOU SOOOOOOO MUCH PAT.. it worked excellently!!