Value List of Current Employees

[ktalley]

Hello,

I have a database for a summer music camp program. I currently have a table of all faculty that have taught at the camps over the last several years. The database I inherited had a Year field that would have "yes" entered into it when faculty taught at the camp that year. Most of the scripting and sorting would start with a find for whichever faculty member was teaching in the current year. I would like to be able to create a value list of all the current teaching faculty for this year. The value list would be used in several layouts for ease of pulling class rosters, contracts, assigning to classes (though a join table to the student table), etc.

I've spent several hours searching, and can't seem to find a workable solution. I know you can't really create a value list based upon a find for "2011=Yes" but I would like to be able to do the same thing as that.

Any help would be appreciated. Thanks!

kt

[Vaughan]

The database I inherited had a Year field that would have "yes" entered into it when faculty taught at the camp that year.

Does this mean that there is a field for each year, and a new field will be added for 2012?

If so, you are quickly discovering the limitations of that method.

Can you describe your data setup; or even better, post a copy of the file?

[ktalley]

You are correct on both counts. The database I inherited was just about 15 individual, unrelated files. Everything was flat by design. The "Year=Yes" has been very annoying.

I'm away from the database itself right now except for remote access to the filemaker server (no direct access to the file), but I can describe what I have now.

I've pulled everything into one file. I have a faculty table of our teachers. Multiple teachers can teach at multiple camps but not every faculty member will teach every year or at every camp. I have a Camp_Info table that lists each camp individually and it's unique information (name, start dates, budget numbers, etc.). All tables have unique, serial IDs.

I also have a student table for each camp we offer. The camps have different classes and rehearsals so each camp also has a class and rehearsal table of it's own. Currently no students attend multiple camps. I am able to get the classes/rehearsals working through join tables to the appropriate faculty members (thanks to user Comment on this site) to generate class rosters and assign faculty to each class/location.

Does that make sense on the setup?

I keep thinking I've learned a lot from this process, but I keep running into roadblocks of what has been done the past 10 years and what I really want to do. Plus, I'd rather not leave someone else down the line a mess.

Thanks for your assistance!

kt

[comment]

You need an additional table to join Faculty and Camps, with fields for:

• FacultyID

• CampID

• Year

• Role

[ktalley]

Thanks. I figured I needed another table. Here is basically what I have now:

Camp_Info--<Camp_Info_Join>--Faculty

matching on Camp_ID in Camp_Info and Camp_Info_Join

matching on Faculty_ID in Faculty and Camp_Info_Join

I have it set to create records from the Faculty table to the Camp_Info_Join table

I can create a value list based upon the faculty_IDs from the Camp_Info_Join table (with secondary field showing the name), but how do I separate them based on year/camp?

Let's say I have a faculty member Kent that teaches at the Middle School Band Camp and the Band Camp. Rob teaches only at the Middle School Band Camp. How can I have a value list of faculty at the Band Camp that doesn't include Rob? Then of course, how do I separate based upon the year if Rob doesn't teach at all next year?

Thank you!

kt

[comment]

I can create a value list based upon the faculty_IDs from the Camp_Info_Join table (with secondary field showing the name), but how do I separate them based on year/camp?

It depends on where you are when you need that value list. For example, if you define another relationship (using a second occurrence of the join table) as:

Camps::CampID = Join 2::CampID

AND

Camps::gYear = Join 2::Year

you can define a value list of camp's faculty in the selected year (a global field in the Camps table) by using the 'show related values only' option.

---

A semantic note: the word "Info" is redundant in a table's name. Of course a table contains info - what else could it contain? I'd suggest you use Faculty, Camps and Roles (or Staff, or CampFaculty) as your table names.

[ktalley]

Thanks. True statement on the "Info" label. Do you have a labeling suggestion for keeping track of duplicate table occurrences?

Apparently I'm missing something fundamental. Let me backup.

Here are some of the tables and fields I have:

Camps

CampID

Year

Name

etc.

Faculty

FacultyID

Name

Band Camp Position (I have one field for each camp because it can be different for each faculty member and camp - ex: director at one camp and teaching faculty at another)

Middle School Camp Position

Band Camp Salary (again, each faculty can have a different salary for each camp and from each other)

Middle School Camp Salary

etc.

Camp_Join

Camp_Join_ID

CampID

FacultyID

Year

Position

BC_Students

StudentID

Name

etc.

BC_Classes

ClassID

FacultyID

Title

Location

Start_Time

etc.

BC_Classes_Join (joins BC_Classes and BC_Students)

BC_Classes_Join_ID

StudentID

ClassID

BC_Faculty_Classes_Join (joins BC_Classes and Faculty)

BC_Faculty_Classes_Join_ID

FacultyID

ClassID

- The Camp_Join table is creating a record in it's table for each faculty member that is added to it and associating it with a specific camp. Correct?

- So Kent would have two records in the join table, one for Band Camp and one for Middle School Camp. Rob would only have one, since he only teaches at the Middle School Camp. Correct?

- If I'm assigning faculty to a class in the BC_Classes Table, I would like a value list that shows only the faculty members assigned to teach at the Band Camp. I hire faculty then assign teaching duties. What mechanism would I 'hire' faculty and assign them to a specific camp and role?

- When the camp staff is printing rosters, I currently have a pop up menu that filters the students for a faculty members' class. If that list can contain only current faculty for one camp, it would be helpful. It works now, but shows all names that are in the faculty table.

- If the Camps table has a global year field, what happens next year when I assign faculty. Do I clear out the Camp_Join table or change to a new global year field?

- Are there more table or table occurrences that I need?

- We currently have 6 different camps, so all of this will be x6. We could possibly add or remove camps in the future.

I'm not expecting anyone to do this for me, however there is something that I'm missing that may not be quickly explained. Unfortunately, I don't know what I don't know at this point. Sorry to be so verbose

Thank you

kt

[comment]

I have one field for each camp

No, that's not a good way. The idea here is that the Camps table holds data describing the camp - and only the camp. The Faculty table describes the faculty alone. Anything that describes a specific instance of a Faculty-to-Camp join - the year, the position, the salary - goes into the CampFaculty join table.

Same thing with classes. There should be only one table for all classes, regardless of camp; each class should be associated with a camp through CampID.

Same thing with students: one table for all of them, with CampID indicating which camp they have enrolled into. You'd probably want a Year field here, too - so you could quickly find the students at camp X in year Y. This is assuming you don't keep track of repeating students - otherwise you'd have a separate table of Enrollments.

What mechanism would I 'hire' faculty and assign them to a specific camp and role?

"hire" = create a new record in Faculty;

"assign" = create a new record in the CampFaculty join table;

If I'm assigning faculty to a class in the BC_Classes Table, I would like a value list that shows only the faculty members assigned to teach at the Band Camp.

Since a class is related to a camp, it is also related to the camp's faculty.

If the Camps table has a global year field, what happens next year when I assign faculty. Do I clear out the Camp_Join table or change to a new global year field?

The global field is merely a utility device - you change its value whenever you wish to view records from another year. It has no effect on your data nor on your "core" relationships.

[ktalley]

Thanks. Design is more important at this point, so I'll get back to the value list issue.

This is obviously the way to go with the database. <deep breath> I've started another database from scratch with this idea in mind. Would you or someone else mind looking at it and let me know if this is the direction I need to be going with it?

Two questions based on tables I currently have that weren't covered so far.

- Two of the camps have auditions. Each student auditions (with one to three faculty members) and is placed in a band ranked by chair. That data can easily be entered into each student's record. Should I treat each auditions just like any other class/rehearsal? It only happens once per camp.

- I have scholarship applications that I don't know where to put. Students make a separate scholarship application and may or may not apply to the camp. The scholarships are then awarded and need to be applied to a student's camp record - but only if they have applied to the camp. Some students don't attend the camp if they've applied for a scholarship, so they may need a separate record. Do these need a different table?

Thank you so much for your assistance. I'm sort of blinded by the way things have been designed, I keep wanting to use that as my template. However, I know most of my weird problems and workarounds will go away with this approach.

kt

Music_Camps.fp7.zip

[comment]

I am not sure I understand the auditions issue. What exactly is/are the result/s of an audition (in terms of data to be recorded)?

Some students don't attend the camp if they've applied for a scholarship, so they may need a separate record. Do these need a different table?

The students? No, I don't think so. A student is a student, whether they have enrolled into a camp or not. In fact, you could even put students and faculty in the same table, if you wanted - perhaps with a bunch of other people, e.g. suppliers, parents, etc. It all depends on what fields you need for each type of person.

I have only looked at the RG in your file. I think that at this point you'd be better off working with an ERD, thus postponing the problem of which occurrences to duplicate (a black art by itself).

There's one thing that doesn't make sense to me, and perhaps I don't understand what a "camp" is. How can a camp have a First_Date and a Last_Date? I thought a "camp" was something that repeated every year.

[ktalley]

In two of our camps, students audition on the first day. In the biggest camp, there are 15 sections (flutes, clarinets, etc.) and they are filtered into 5 bands by ability. Each student is ranked within each section. Faculty are assigned to audition students by section. Section order and band order is critical as it is used for roll sheets for band rehearsals and section rehearsals as well as producing a program with names of the students in chair order within their section. Chair order is a big thing in the band world. Taking roll and keeping track of 500 or so minors is also pretty important to us too (-;

In the database, each student will have an instrument. After auditions, we will enter in the chair and band they made. From that information, we create roll sheets for the band rehearsals and section rehearsals. These are sorted by Band, Instrument and then Chair. It's much faster to take roll in a large band if the roll sheet is in the order in which they are sitting.

The most complicated camps have 2 elective classes, 2 band rehearsals and a sectional rehearsal each day - each with it's own roll sheet and faculty member teaching/conducting/rehearsing.

The "First Day" "Last Day" fields are mainly for schedule headings in printable layouts. The first day and last day of the camps have different schedules than the middle days. Auditions on the first day, and concerts on the last. There are also extra activities that are different each evening. None of which is really important in the database, but it's one less date that I have to change in 5 different places for printouts.

That was probably more than you really wanted/needed to know. It seems very convoluted, but actually works very smoothly.

I'm not really familiar with an ERD. Is there a place you would recommend to start?

Thanks

kt

[comment]

I'm afraid I got lost here:

each student will have an instrument.

But:

there are 15 sections (flutes, clarinets, etc.) ... Each student is ranked within each section.

See, that doesn't make sense to me: if a student has ONE instrument, how can he or she be ranked for more than one?

Cardinality is perhaps the most important aspect of database design. You always ask 'how many of X does Y have?". This determines whether the relationship is one-to-one (usually a field in the same table), one-to-many (a parent-child relationship) or many-to many (usually requiring a join table).

I'm not really familiar with an ERD. Is there a place you would recommend to start?

Perhaps here (just something I found in a quick Google search):

http://www2.cs.uregina.ca/~bernatja/crowsfoot.html

[ktalley]

Sorry, I'm not explaining very well. For example, at last year's biggest camp, we had 71 students who played flute, 105 students who played clarinet, 69 who played saxophone, 92 who played trumpet, etc. In order to get a balanced instrumentation for each band, we have to audition students to get those with similar ability level in the same group. The top band last year had 12 flutes, 17 clarinets, 11 saxophones, 14 trumpets, etc. There is never a hard and fast X number of flute players in band 1 and Y number of flute players in band 2. The amount of students in each band should be similar in ability level to each other so we don't have beginners in the same group as advanced players. We don't know their ability level until the first day of the camp.

After the auditions, we add information to each students' record in the database - the band they made (essentially band 1 through band 5) and the placement within each section. Since different faculty members rehearse different sets of students (Mamie rehearses the top band's flutes, Dianne rehearses flutes from the 2nd band....), this allows us to give a roll sheet to an assigned faculty member for all the flutes in auditioned order (aka "chair order") for band 1 and all the other sections too. It also allows us to list students, broken down by section, in the final program for each band.

Each camp has a completely different set of students, classes and daily schedule from each other. We have a choral camp and guitar camp that don't even have auditions. That's why I had initially setup each camp of students with their own table because they don't relate to one another at all for our purposes. Some camps do have similar faculty members, but they teach different classes and rehearsals at the different camps.

Is that making any sense?

Thanks for all your help. Seems like I need to do more reading and planning to avoid problems in the future. What I've got now works fine and is so much better than what has been done in years past - but it's definitely a work in process.

Thanks

kt

[comment]

I am not sure I am getting any wiser on this. I think there are two separate issues here: auditions and band placement.

Regarding auditions, is there anything that needs to be recorded other than a SINGLE result per student? By "single" I mean the student auditions once, playing one instrument only, and the result is a single assessment of their ability. If the answer is yes, then the result should go into the student's record - or, more precisely, into the student's enrollment record (since you have decided to have those).

The same thing applies to band placement: if a student gets one chair in one band only, then the placement is merely an attribute of their enrollment.