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
2) Table Name: departments. Fields: id (Number, Auto-enter Serial), name (Text, Indexed). Data as follows
3) Table Name: members. Fields: id (Number, Auto-enter Serial), name (Text), year_id (Number), department_id (Number). Data as follows
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!