layered value list

[jklm]

Hi, i have following four tables with their data

1) Table Name: years. Fields: id (Number, Auto-enter Serial), year (Text, Indexed). Data as follows

 	1 	2003

 	2 	2004

 	3 	2005

2) Table Name: departments. Fields: id (Number, Auto-enter Serial), name (Text, Indexed). Data as follows

 	1 	A

 	2 	B

 	3 	C

 	4 	D

3) Table Name: Rel_years_deps. Fields: id(Number, Auto-enter Serial), year_id (Number), dep_id (Number). Data as follows

 	1 	2003 	B

 	2 	2003 	D

 	3 	2004 	B

 	4 	2004 	C

 	5 	2004 	D

 	6 	2005 	C

In this table, the fields year_id and dep_id are pop menus showing all the values from years and departments tables, respectively.



4) Table Name: members. Fields: id (Number, Auto-enter Serial), name (Text), year_id (Number), dep_id (Number). Data as follows

 	

        1 	Alex 	2004 	C

 	2 	Bob 	2004 	B

 	3 	Kate 	2005 	C

 	4 	James 	2004 	D

 	5 	Simon 	2003 	B

 	6 	Judy 	2003 	D

 	7 	Jim 	2004 	C

        8 	Thomas 	2004 	C

        9 	Michelle 	2005 	C

        10 	Joshua 	2003 	D

        11 	Merylin 	2004 	C

        12 	Joan 	2004 	B

        13 	Michael 	2004 	B

In the members table, fields year_id and dep_id are related accordingly from Rel_years_deps table.

The relationship for this is as follows:

Rel_years_deps::year_id = years::id

Rel_years_deps::deps_id = departments::id

Rel_years_deps::year_id = members::year_id

So using this relationship, i have two value lists:

1) This value list is used by year_id field (displayed as pop menu). It points to Rel_years_deps::year_id (this would give ids), and second display part points to years::year. This value list displays all values from selection.

2) This value list is used by deps_id field (displayed as pop menu). It points to Rel_years_deps::deps_id (this would give ids also), and second display part points to departments::name. This value list will display related values from table members.

So, all these would give an option of choosing first year from year_id field, then all departments related from table Rel_years_deps to that year would be displayed in deps_id field.

Now, I want to create the following table, let's say Table1.

I want to create three fields in this table as follows:

1) Table1::year_id

2) Table1::deps_id

3) Table1::members_id

All of three I want displayed as pop menu with the following three value lists, respectively:

1) ValueList1: display all different years from members table.

2) ValueList2: display all different departments that belong to the selected year, which are related from table members.

3) ValueList3: display all different member names from table members that belong to particular year and department selected.

So for example, if first field has been selected as year 2004, then second field should only display C,B, and D. Let's assume user selects C, then the third field should contain member names Alex and Jim. Please can you show how Table1 can be created. Thanks a lot in advance!!!

[bcooney]

What is the goal? I'm a bit lost. It is very rare to have a Year table. Are you looking to record a person's service in a department? Why not have a table that simply stores a PeopleID, DeptID and start and end date of service?

Then, all you need are Finds to do reporting.

[jklm]

What is the goal? I'm a bit lost. It is very rare to have a Year table. Are you looking to record a person's service in a department? Why not have a table that simply stores a PeopleID, DeptID and start and end date of service?

Then, all you need are Finds to do reporting.

Hi, actually this tables are made up for illustration purpose. Please can you show how it could be done as it is being asked without any modifications.

Simply, in Table1, I actually want three fields: year_id, dept_id, and member_id. First user should select "year_id", which should be populated using various "year_id"s from members table. Then accordingly "dept_id" should be populated with departments related to the selected "year_id" also related from members table. Lastly "member_id" field should be populated with members name depending on the "year_id" and "dept_id" selected from members table. Thanks a lot in advance!!!

[bcooney]

You are looking for how to do a conditional value list. Search the forums, there are many examples.