accessing second field of a related record

[jiggerton]

im a new user and have been pulling my hair out for a week trying to accomplish what i feel should be a simple task. i am hoping someone can point me in the right direction before i go bald…well, balder.

i co-teach several different classes at several different schools with several different teachers.  i have set-up two tables, a “schedule” which will tell me my daily classes and lesson plans, and a “schools” table that contains the names of the schools, the classes i teach at each,  and their respective teachers. they are related by the school.

 

in the “schedule” table, when i select the class for each period (which is a generated value list based off the school I've previously selected) i would like the associated teacher for the class i select to be auto-entered in another field.  This would be the same value that would display if, under value list options, i were to choose “also display value from second field” and select the “teacher” field i have set up in the related “schools.” for formatting reasons i would like this data in a separate field.

 

a lookup by “schools::teacher” simply produces the first teacher listed for each school.  i am guessing i need to do a calculation, but i can’t seem to find a function that would let me get the teachers field value based off the class field value. is this more than not knowing the correct function to call? do i have my relationships set up wrong? do i need another table? a 'join' table? any advice would be greatly appreciated.

[comment]

in the “schedule” table, when i select the class for each period (which is a generated value list based off the school I've previously selected) i would like the associated teacher for the class i select to be auto-entered in another field.

 

That shouldn't be necessary: you can simply place any field from the related table on the layout of Classes, and it will show data from the related record. 

 

The catch here is that if there are several related records, it will show data from the first one of these - same as the lookup. you tried.  So the real problem here is that your Schools table is really a Classes table because each record represents one unique class that you teach, while the School attribute is not unique. Selecting a class should make exactly one record in the Classes table related to the current record in schedule.

 

How did you set up the relationship between the two tables?

[jiggerton]

hi and thanks for the reply!

 

i am currently using the school name as the relationship since i use it to create the value list for the school pop-up and then use "include only values starting from <schedule>" to fill the period pop-up menus with the available classes.

 

you are right.  the schools are not unique to the records in the 'schools' table. neither are the class names. its only the combination of both that are unique. which is why i was hoping there was some sort of calculation that would retrieve the value based off of the two fields. i guess i am just not understanding how relationships work <insert zinger about horrible love life here>

[comment]

There are two ways you could handle this, depending on the user interface you prefer:

 

1. Select the school first, then select a class from among the selected school's classes;

 

or:

 

2. Select the class directly, using a value list that shows a list of all classes, together with their respective schools.

 

 

Let us know which one you prefer, and we'll pick from there.

[jiggerton]

since i have over 100 classes, i think i will go with the first option. =)

 

in that case, i would need to have the relationship set up the way i had it originally, correct?

[comment]

In that case you will need two relationships, using two occurrences of the Classes table. The first - core - relationship will look like this:

 

Schedule::School = Classes::School

AND

Schedule::Class = Classes::Class

 

 

The other - auxiliary -  relationship will be:

 

Schedule::School = Classes2::School

 

 

To select a school, use a value list based on Classes::School (or a custom value list of schools, as described below);

To select a class, use a value list using values from Classes2::Class, show only related values, starting from Schedule.

 

 

Note:

Strictly speaking, you should be using meaningless IDs instead of names as your matchfields; but as it seems unlikely that the schools or classes will be renamed, you may just go ahead and use names. Keep in mind though, that your naming must be consistent (perhaps use a custom value list of all school names you are involved with).

[jiggerton]

ok. ive set up the tables as best i can to match but im a bit confused since i have 6 periods a day.  the classes value list works fine, but i have 6 class fields that will be filled per Schedule record. so should i have 7 links in the core relationship?

 

Schedule::School = Classes::School

AND

Schedule::Period1 = Classes::Class

AND

Schedule::Period2 = Classes::Class

etc...

 

or i do i need to create a separate instance of "schedule" for each period? 

[comment]

Numbered fields are a no-no in database design. You need to have a separate record for each period. What other fields does your Schedule table have?

[jiggerton]

aaah...i see. thats what i needed to hear. its not a matter of my tables, but that need to simplify the amount of data for each record.

 

i had thought that one record in the schedule would be equivalent to one day, but i guess i need it to be equivalent to one class.

 

in my schedule table fields i currently have

School, Date, Period1, Period2....Period 6, Period1Teacher....Period6Teacher, Related Files, Notes.

 

i think i see where to go from here now. i should just get rid of the Period1-6 field as well as the Period1-6Teacher, add another field to select the period and one teacher field that can now easily be done as a lookup. then use the layout to control how to display the data so i can see all of my classes for one day, per week etc...

[comment]

i had thought that one record in the schedule would be equivalent to one day, but i guess i need it to be equivalent to one class.

 

Well, it depends. If, as it seems, you spend an entire day in a single school, then perhaps you should keep the Schedule table (where each record is a single unique date) and add a Periods table as a child of Schedule. This would lead you to something like:

 

 

 

 

Note: the Periods::School field would be either a calculation field = Schedule::School, or a lookup (from the same source). If you make it a lookup, then you will also be able to view the related periods from the point-of-view of Classes.