Normalising this data - bit beyond me

[Lol999]

Hi, I thought I had a handle on this data but I didn't, so if anyone could offer some suggestions for structure then that would be great.
The data is thus:
Clothing: Fleece, T-Shirt, Polo Shirt, Hi-Viz
These are available in a multitude of sizes from XS to XXXL
Helmets: Orange, White, Blue, Black
These are available in colours only, no sizes
Employees: Employee ID and Name
There will of course be an issue date for each item and a quantity issued for each item.
The idea is to enable warehouse staff to issue clothing to staff where a record is kept of what they have received, what size or colour and when.
The data also needs to be interrogated for specific issuing i.e clothing onlyor helmets only etc.
My initial idea was to have an Employee table with a link, Employee ID to a Issued table which recorded the issuing of what and when, as in one employee can have many items issued to them on many occasions.
The sticking point was the sizes and colours of the clothes and helmets, and this may be further complicated by the addition of respiratory masks to the mix.
I tried cascading combo boxes for the attributes such as size and colour but couldn't get them to work, on reading the internet it appears because my subform was set to datasheet.
Can anyone suggest a structure for this data?
Thanks, Lol

 

[The_Doc_Man]

What does your business track?

Let me be more specific. If you have an XXXL Polo Short and an XXXL T-Shirt, are they tracked in such a way as to have separate internal identification numbers, something like an Inventory number or SKU number? (If not, they probably should.)

Even if the trackable items are simply autonumbered for internal tracking only, it would be good for each possible item and size combo to have a different code. It might be tedious to generate this list, but it would be a one-off operation that might even be doable with a series of Append queries. But you were interested in structural questions, so that's where I'm going.

It is clear that you can have a multiplicity of colors, sizes, and styles. Typically, these are treated as attributes of a base item. You might have a base-item table that holds Fleece, T-Shirt, Polo Shirt, Hi-Vis, Helmet, Mask. Then you have attributes such as size & color. You might say - "but color doesn't apply to the shirts and size doesn't apply to the helmets." But yes, both do.

For helmets, you have a size that is OSFA (One Size Fits All) to go with XS...XXXL for other items. For shirts, you have a single COLOR that applies to all shirts - but the helmets allow wider choice of colors.

Structurally, you have a many-to-many situation. Many persons are tracked with respect to the many items they can have. You would have the person ID and the Item ID in this table along with the date of issuance for that item to that person. This is a classic Junction table, which you can look up in this forum using the Search function.

 

[Lol999]

I see now, I never thought of putting a table together with the variants of item and size, I was using separate tables for size and item and inserting those, or trying to, with cascading combo boxes into a table that logged items issued to each person.

I'm very familiar with the junction table, it's the classic VHS rental scenario fro years ago.

Many thanks, Lol

 

[Lol999]

I've sorted it Pat, just been up to the neck with other stuff to come on and post the solution.
What i did was have the following tables:

Tbl_Employee - as it suggests
Tbl_PPE - clothing table now incorporating masks and harnesses
Tbl_Issue - junction table between the two above
Tbl_Size not linked to any of the others

With the judicious use of combo boxes etc the system works quite nicely.
Cheers, Lol