Best method for design of database with multiple value in one field

[600720544]

I'm new to database design, I have an issue with a stock database. I'm at the design stage and was wondering what is the best way for the following example. I have a table of products, which has a style code then description, and colour. straight forward so far. I then wanted to add size. Here's the question, does every variation of the style need it own entry in the database I.e.

line 1 style abcde, Description Shirt, colour Blue
line 2 style abcde, Description Shirt, colour black
line 3 style abcde, Description Shirt, colour White



which would result in a huge amount of repeated data. Moving on I will need to add Size which will duplicate even more.

if you have 30 styles in 5 colours and 10 sizes that's 1500 lines. that's a lot in imputing for each style.


I have tried the "allow multiple values" in table design lookup but can't seem to follow that a white shirt may have 5 sizes but the blue only has 2.
I am just looking for a bit of basic advice

[ranman256]

no, youd have
tSize table
tStyle table
tColor table

then the order would put them together
user would pick from each of the lists to assemble a shirt.

tOrder
OrderNum, Size,Style,Color,Other

(once picked, youd check to see if BLUE shirts have any size 14, in stock)

[ranman256]

plus , to create inventory, you don't have to input the data by hand 1 at a time,
making one query of all the tables: size,style,color, and NOT joining them, will multiply the values.
youd get a shirt of every color, every size, every style.

[600720544]

thanks for the reply, but still having problems. I understand the 3 tables, but I am looking to use a products table which has a stock count. I have tried the query with no joining but this as you have said gives all options. I want to add New Product STYLE ABCDE in 3 colours, so I can check the stock i.e. STYLE ABCDE in Blue with 3 stock, then STYLE ABCDE in white with 10 stock, also STYLE ABCDE in black with no stock. I have created a form customer detail with subform for products when I select ABCDE i get one style with 3 colours, but i would expect the style repeated 3 times for the different colours.
Any help appreciated.

[orange]

My suggestion is to start with a clear description of WHAT your "business" is about. Make it clear so it is easily understood by colleagues. Then start to add detail. Do not jump into Access too quickly expecting Access to do something magic for you. You must tell Access what to do. Work through this tutorial to get an understanding of design of database, tables and relationships. (30-45 minutes)/ Move from a description to a model of your tables and relationships. Concoct/build some test data to vet/verify/prove your model with some test scenarios.

You may get some ideas from this free video.

Good luck.

[Bob Fitz]

thanks for the reply, but still having problems. I understand the 3 tables, but I am looking to use a products table which has a stock count. I have tried the query with no joining but this as you have said gives all options. I want to add New Product STYLE ABCDE in 3 colours, so I can check the stock i.e. STYLE ABCDE in Blue with 3 stock, then STYLE ABCDE in white with 10 stock, also STYLE ABCDE in black with no stock. I have created a form customer detail with subform for products when I select ABCDE i get one style with 3 colours, but i would expect the style repeated 3 times for the different colours.
Any help appreciated.

Can you post a copy of your db

[600720544]

I think I have attached a small extract of what I'm trying to achieve, with notes of my issues. I think i'm using multiple values correctly but I'm open to offers for a better solution

testDB.accdb