Access 2007 Table using a combo box lookup in a field

[DougP]

I have a table with called "Choose a class" with 2 fields
Class and Type ( to keep it simple now).
I also have a table called "Classes with" 2 fields Class and Type.
which has this sample data.
Music Songs
Music Note study
Math Addition
Math Subtraction
Math Mutiply

I am able to make a lookup in the "Choose a class" table, Class field to find Music or Math.
but how can I make the "Type" field combo box lookup narrow the result set by finding only Classes with "Music" records?
So when some one chooses "Music" for the "class" field then "Type" field shows only "songs" or "note study" and not eveything like "Addition", "subtraction" etc as well.

Do I have to make a query or can it be done in the table design?

DougP

 

[Jowidog]

Hey Doug,

Sounds like you need an outer join query. However, are your tables normalized with a foreign key reference for lookups?

There has to be some sort of connection between the two Parent to Child so you can form an outer join query without getting into the mess of a Union query which can completely boggle and mess with your data.

Jim