Table Lookup issue

[chrisjames25]

Hi Everyone. I'm new to access and to the forum. This is first post so hopefully can explain my issue suitably.

I have a table called Tbl_Category which lists the category of products i sell. Each category has sub groups called genus which are listed in the Tbl_Genus table. In this table i have used a lookup function so that i can only enter a genus name against a prescribed category name to maintain the data.

Finally within each genus are varieties of products (Tbl_Variety). In this table i have three fields: Category, Genus, Variety. I can do the lookup on Category and that works fine. However if i then do a genus lookup i get all the genus' in the genus table. What i want is only the genus that fit within the category entered into the category field of that particular record.

I have read how this can be done in a form but cant find anything regarding tables.

Hopefully i have explained myself ok....

by way of example

Tbl_ Category:
Fruit
Vegtables

Tbl_Genus:

Category Genus
Fruit Apple
Fruit Pear
Veg Carrot
Veg Potato


Tbl_Variety

Category Genus Variety
Fruit .........

So on tbl_variety once i select Fruit (via lookup function) the only two that will be allowed to be entered in genus are apple and pear. Carrot and potato would not appear as opposed or be rejected if typed.

THanks in advance

 

[Minty]

You shouldn't be trying to do this directly in a a table, and ideally you shouldn't be using lookup fields in you table design.

The lookup fields are largely frowned upon for reasons best explained here: http://access.mvps.org/access/lookupfields.htm

You should be doing the work in a form so that you can process data entry errors correctly, and perform other manipulation more readily.

 

[chrisjames25]

Thanks minty. I will try and stay away from lookups.

Question though. If i stay away from lookups. And wanted to change say a typo in the Category table from Fruuuuit to Fruit, how would i be able to cascade that through all the other tables without the lookup function?

 

[Minty]

The real answer is that you don't store the text but a number that refers back to the text. So your Tables should be

Tbl_ Category:
CategoryID CategoryTxt
1 Fruiiiiit
2 Vegtables

Same with your other tables.
This way a) you only store the number not the same text over and over. b) as you described changing the typo in Fruiiiit will not change anything except the description when you link back to it....