Help on Tables/relationships?

[mistaken_myst]

I have a scenario and from it I have designed a database:



Basically, it is a Student who chooses a Work, and many Fruits.

1. tblWork and tblFruit consist of a list of works and fruits respectively.
2. For now, Grade from tblWork is a combo box which consists only of the values 'Junior' or 'Senior'.

After several attempts, I have achieved the tables & relationships above but they might be wrong. The scenario does not require the use of forms at all, just the database tables. Anyway my problem is that:

To be a able to choose an 'Apricot', or 'Blackberry', the student should have a 'Senior' Grade.

Any help on this please?, I am really stuck on this. Should some validation rules be implemented? or maybe some new tables/relationships created? I have tried all these but I really cannot move on.

[RuralGuy]

It sure looks like a homework project to me.

[RuralGuy]

Here's a couple of links that may be helpful:
The Access Web - The Evils of Lookup Fields in Tables
Access Tips: Cascading Lists for Access Forms

[mistaken_myst]

It is a quite big scenario where many endless possibilities exist. In fact, I posted just a part of my work (3 tables), I already hav 10-11 tables while my friends got 8-16 tables, it all depend the way you design it and to which extent.

I never really got problems with MS Access till now, however it seems I was stuck somewhere. From what I understood: All the design should normally remain at the database tables level, so forms are not concerned since I am in fact designing a database system only.

Everything would have been easy with Forms for me, but I think I must remain on the database tables level. so really any help ? redesigning tables? maybe separating 'Apricot', or 'Blackberry' as a separate entity/table?

Thx, I am currently checking your links.