AnsweredAssumed Answered

custom value list design

Question asked by jklm on Feb 19, 2011
Latest reply on Feb 21, 2011 by philmodjunk


custom value list design


Hi, i have following three 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: members. Fields: id (Number, Auto-enter Serial), name (Text), year_id (Number), department_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

In this members table, the fields year_id and department_id are pop menus showing all the values from years and departments tables, respectively. They are value lists where they actually store id of each table, i.e. years and departments, but display year field of years and name field of departments.

Now, I want to create two tables, let's say Table1 and Table2.

1) In Table1 I want to have two fields, where first one shoud pop up all the different years from members table, and second should pop up all available different departments belonging to that year form members table. For example, first field should contain 2003, 2004, and 2005. If user selects let's say 2005, then second field should only have department C as an available one.

2) In Table2 I want to have the same two fields of Table1 above described with an addition of third field, which is the member name. So, for example 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 step by step how this two tables can be created. Thanks a lot in advance!