Selecting the correct table

Selecting the correct table

--------------------------------------------------------------------------------

I have an order table and depending on the the product grp it needs to then be able to select the appropriate size scale table.

e.g.

I order suits, and select a size grade A ( there are 7 size grades ) I then then to enter the size scale for that product. I am not sure whether to create 7 differant tables with the different size grades and link that to the table, if so how do i get it select the correct table.

please help...

H

Hi
You need to look at using one table that can have muliple entries for the size grades e.g.

ID SizeGrade Product
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
etcetera
This way you can use queries to show all products for SizeGrade 1 or all SizeGrades for product 1
You then link this table to a table explaining SizeGrades (so you only use a number in this table) and to another explaining what Product 1 is for example
Does this help you on your way?
Shout if you need a bit more
Best wishes

Update on your advice

Hi Mate
Thank you for your detailed replied, I think i am half way there, what I have done is attached a test DB I am working on and the documentation that the user will use to enter on this database.

The process is

A Pre-Order is created.
Once signed off it becomes an Actual order generating an Zm-Order Number.

One Zm-Order can have many colours, but only one size grade. ( A to H )

So for example

Suit 1, in Colour Black can have a Size Grade A ( there are 8 Size grades, but only one size grade can be allocated to that order )

Colour Navy another size scale.

Size Grade A ( I can select this from a Lookup table but not sure how then to connect it to a separate table to enter the below )

So the Size grade that would be entered is

Suit 1 - Black
1 x 88 cm
2 x 92 cm
1 x 96 cm
etc

Suit 1 - Navy
1 x 88 cm
2 x 92 cm
2 x 96 cm

But there are a few issues I can not get my head around.

How do I get the user to open that Size Grade table to enter the Quanitities in the Size Fields

Also Each new style needs an icremental Style number issued to it, so Suit 1 would be 00001, but one style can have many Colours. If a new suit is entered the next style number would be 00002.

If the next order that is not the same product grp, so say Trs the Style number should start at 00001 and incremental after that. I was told to use DMax() not sure how that would work with this???

Just so you know there are 26 Product Grps A - Z

Confused yet???

The final issue which I think would sort itself out once I ( or hopefully yourself can fix the above issue ) if an order is entered into the system but is a repeat Style Number I obvioulsy dont want to allocate a new Style number but I want to pick up Cost Price, Supplier details, etc from the previous order that has been entered but state a different Size Grade ( and Size Scale )

I hope this make sense, I have attached a db and document to help clarify what I have written above, I hope you can help....

Thank you in advance..

H