Lookup SQL WHERE

[Bob Richardson]

I'm trying to design a Student table that will use a lookup to a Class
table. Classes are provided ONLY for people in certain grades, e.g. Class
101 is only given to grade 9, class 221 is only given to grade 10, etc.

Class Table: Grade, ClassCode
9 101
9 103
9 110
10 101
10 201
10 221
etc.

Each Student can choose 2 classes, so I want a lookup table in two different
fields that will only show, in a drop down list, those classes that are
available to the grade that the student is in.

Student Table: StudentID, Grade, Class1, Class2

I tried the following, with no success:

This is the Row Source of the LookUp tab for Student.Class1

SELECT Class.ClassCode FROM Class WHERE Grade=Class.Grade ORDER BY
ClassCode;

 

[Wayne Morgan]

You are pulling the value Grade from the form and you want Class.Grade to be
equal to that.

SELECT Class.ClassCode FROM Class WHERE Class.Grade = Forms!FormName!Grade
ORDER BY ClassCode;

If may be that what you have will work if you reverse the equal statement in
the WHERE clause, is not, then specify the full path to the Grade value on
the form as indicated above.

 

[Bob Richardson]

Thank you. I see where that will work when using a form. I was hoping to
establish this lookup in the design of the Student table. Then, if entering
data with the Datasheet view, there would be an appropriately filtered
drop-down list in the Class column, depending on the value in the Grade
column.

 

[Van T. Dinh]

Sorry, you cannot have a filtered drop-down list depending on an "earlier"
value in the DatasheetView of the Table. You need a Form, more
specifically, Form / Control Events to do this.

 

[John Vinson]

I was hoping to establish this lookup in the design of the Student table.

You can't. Table datasheets have VERY limited functionality. This is
among the many things you can't do, and among the many reasons you
should not routinely use table datasheets.

See http://www.mvps.org/access/lookupfields.htm for a critique of the
Lookup Field misfeature.

John W. Vinson[MVP]

 

[John Vinson]

What evils abound if I include the data, rather than a key, in the DB?

Not too much. A bit of a waste of space - "Poughkeepsie" has 12 bytes,
a numeric ID only 4. For US States (or Canadian provinces) the
two-letter postal abbreviations are an excellent choice.

John W. Vinson[MVP]