Hi all

I wonder whether anyone can help me.

I'm setting up a database and want to create lookup values in a table...
For example, I have a table called lookups. In this table are fields called field1, field2, field3, field4
Now, in another table, main_table, I want drop down boxes listing the values from lookups... However, once field1 is selected, I only want certain entries from field2 to be allowed to be selected, depending on what was selected from field1... The same goes for field3, depending on field2 and then field4 depending on field3...

So, it should be along the lines of select all from field 2 WHERE field1 = field1 (already selected)...

The lookups looks something like this:


field1 -- field2 -- field3 -- field4
A type 1 i x
A type 2 ii y
A type 1 ii y
B type 2 ii z
B type 2 iii x
B type 3 iii x
C type 1 ii X


So, in the main table, if A is selected in field1, then when the user moves to the field2, they should only be offered type 1 and type 2. Then, once they select the type, e.g. 2, they move on to field3 where they should only be offered a selection of ii for field3 and so on...

I hope this makes sense!

Look forward to hearing from somebody...

In case you are wondering - why...

I will be creating a main form with subforms. The user is the main form and then they can have various selections from the lookups - in a one to many relationship. I thought it best to sort the look-ups in the tables - unless it is better to do this is queries which are relied upon by the forms/subforms

Any help would be truly appreciated,

thank you

K