2 Level lookup in a table

[dnorris]

Hi,

I am baffled on this one and perhaps just a bit brain dead. I have a table with two look up columns.

Structure

Departments
Dept_ID Autonum
Dept_Name Text

Groups
Group_ID Autonum
Dept_ID Num
Group_Name Text

Teams
Team_ID Autonum
Dept_ID Num
Group_ID Num
Team_Name Text

In a table=based look up for Group_ID in the table Teams, I need to limit the returned records from a query on Groups to the subset where Dept_ID equals the previously looked up Dept_ID.

I know the query is going to look something like this:

SELECT Groups.Group_ID, Groups.Group_Name
FROM Groups
WHERE (((Groups.Dept_ID)=???????))
ORDER BY Groups.Group_Name;

but how do I refer to the Dept_ID I just looked up in the row I am entering?

Thanks in advance for the help

[FishVal]

@dnorris
Hello, dnoriss.

Unfortunately, this doesn't make much sense. Could you elaborate further on it?
Example of unfiltered data and desired output would be nice as well.

Regards,
Fish.

[dnorris]

In the table Names, I first do a look up for Dept_ID using all the available records in the table Department, then I want I do a look up for Group_ID where I limit the rows displayed from the table Groups where Groups.Dept_ID equals the value Dept_ID for the row I am currently entering.

I can do this in a form, but I would like to build this into the design for the Table Names.

[FishVal]

What is table [Names] ?

[dnorris]

Du'h

I meant the Teams table

[FishVal]

Well.

What does mean "I first do a look up for Dept_ID using all the available records in the table Department"?
Does it mean table [Teams] could contain Dept_ID values not matching those in [Departments] table? How do you perform this "look up"?

What the second "look up" means?
Does it mean the desired records are those where [Dept_ID]/[Group_ID] combination match that in [Groups] table?
If so, then why would you need 2 level filtering?
Does it mean [Groups] table could contain [Dept_ID] not matching any in [Departments] table?

[dnorris]

It the table design view, there is a 'lookup' tab for each field that allow a listbox to display the rows from a table or query.

When a user is entering/editing a row in Teams, they first choose Dept_ID from a listbox of all rows in Departments (I have that working). Then then choose Group_ID from a listbox of all rows in Groups where Groups.Dept_ID equals the value of Dept_ID in the current rows (this is what is not working).

How do I get the value of Dept_ID for the current row (in Teams) into the WHERE clause in the Lookup associated with the field Teams.Group_ID?

[FishVal]

It won't work anyway in "table view".
Read Cascading Combo/List Boxes howto to get it working in form.

[dnorris]

I knew how to get it working in a form.

Thank you.