Linking to different tables based on selected value from a combo box

[dekker]

Newbie Question

I'm making a form to assign a part number based on the values selected
from a series of 8 combo boxes. The purpose is to define and
categorize approx. 30,000 items

The first combo box has 8 fields
if the 1st value is selected
then the 2nd combo box will have 24 possible values, however if in the
1st field value #4 was selected then a whole different table would be
required
this sequence would repeat 8 times to complete a unique 10 character
string

Each selection has the description and it's corrosponding ID and I
would like an output to 2 new fields representing the combined
descriptions and the combined ID's


To get:
1234567890

1=Major Category
2=Product Type
3=Sub Group
4=Material Type
5=Finish
6 7=Size
8=Status
9 0=Price Level

Any help or pointing me in the right direction would be appreciated

Thanks
Dekker

BTW: It looks like I'll end up with about 25 tables to store the
characteristics for these products. Each table will have a minimum of
8 columns up 46 columns


Should I try posting in a different group as well

 

[tom]

What you are proposing is feasible, although seems fairly convoluted and
perhaps wasteful, not to mention complicated. It would seem that storing
information in so many different tables may not be appropriate and may be
better in 1 table... Don't forget, having up to 48 tables, possibly means 48
forms to edit data and 48 reports to print out data... however, only you
know how your data is ordered.

I would first consider keeping all the data in 1 table, with an additional
column detailing what the item refers to... e.g. item numbers 1 through 48
can detail the data as if it were in different tables, and be filtered by
each individual combo RowSource. e.g. item 1 = Major Category, Item 2 =
Product Type, etc. A second table would record the results of your combo's
without saving the final values in additional fields... these can be
constructed at runtime.

HTH.

Tom.

 

[dekker]

If I use 1 table I will end up with about a couple of hundred columns
, whereas each table deals with just that product class/type

Thanks

D