I'm no expert either, but I'm also setting up a db for my tutoring business and needed head counts for each session. I did something along the lines of the following and it worked for me:
- Create a summary field in your Enrolled table that counts ALL enrollments, regardless of the class or student. Let's just call it enrollmentCount.
- In your Applied table, create a GetSummary calculation field something like this:
GetSummary ( Enrolled::enrollmentCount ; classID ) // "classID" would be the field that contains the IDs of your classes
- Place this calculation field on the Application layout and it should display the enrollment count for each class.
If it doesn't work, try sorting the Enrolled table by "classID." If sorting solves it for you and you're using FMP 11 (not sure about earlier versions), you can create a sorting script and set a layout trigger so that whenever you open the Application layout, the sorting script will trigger. Likewise, it may not give you an accurate count if the Enrollment table has records omited, so also add the "show all records" script step to the script that is triggered. If you can't do a script trigger, maybe a Sort button?
Again, I'm no expert so I don't know how this will work in your particular situation, but it may be worth a shot since it's just a couple simple field setups to test it.
This seems like an elegant way to solve it, but it doesn't want to work for me? I think I am missing something, because it isn't returning any value. The classID you mention, is that the class ID that is assigned to each student? (so, for instance, Math-Week 1 is 101, Math-Week 2 is 102, and so on, so each student has a three digit courseID associated with their enrollment.
Yes, that's what I meant by the class ID, and the same class ID can be assigned to many students. BTW -- Would Math-Week 1 in 2012 have a different class ID than Math-Week 1 in 2013?
I may not be following your structure correctly. My mind is so embedded in my own db's structure that I could be making incorrect assumptions about yours. Maybe it would help to have a list of a few of the more relevant fields in each of your 3 tables and the relationship structure.
One part I didn't understand was this:
The "Student" table houses school-wide student info, while the "Applied" table only houses info gathered from the applications that is not already in the "Student" table.
Does this mean students who are not enrolled in your school can enroll in your summer programs?
Do you have a separate table (like Classes) where you draw information about the summer classes (ID#, class name, dates in session, etc.) for the application. This may help you down the line, if not with the current problem.
I appreciate your help in trying to figure this out! I feel like this shouldn't be that hard, but I just can't get it to work right.
So, in answer to your questions...
The courses offered change every year, so the ID will change, but I don't believe that will be significant in this case. Math-Week 1 will be the same in 2012 as 2013, but it may not be offered. However, a lot of this info is temporary. That is, once the student has completed the course, the important info from the application will be moved to another table, which houses data on all previous summer programs.
At this point, it is not clear if students from other schools will be able to join this program (historically, they have, but funding is shrinking), but I am building in a way to enter new students into the Student table.
After thinking about it, I liked your suggestion about the "Classes" table (don't know why I didn't think about it myself!) I can see where it will really help us out down the road, so I changed a few things around and added that. Thanks!
However, I am still stuck. I have done a million searches, and I keep trying things, but nothing is working right.
Here is a bit more about the tables, so maybe this will clear up some confusion...
The three (now 4, thanks for the suggestion!) tables in question are Applied, Enrolled, Student, Courses.
The Student table houses info for all students in the school such as contact info, parent info, etc. It is related to both the Applied and the Enrolled table with the StudentID. Each student has one record.
The Applied table houses additional info not stored on the student table, but captured on the paper application the student submits. Things like t-shirt size, if they need transportation, etc. The key bit is the course they wish to enroll in, and the week(s) they are available. And this is the reason that I am even asking this question...I need to be able to see an accurate count of how many students are enrolled in each course at any time. (sorry to repeat this...just trying to make sure I am clear, because I am confusing myself now! LOL) This table is related to the student table and the Enrolled table with the StudentID. Each student that has applied will have one unique record.
The Enrolled table houses the CourseID for the course that each student is actually placed in. Once all applications have been received, we will have to physically go in and enroll students based on their course requests, availability, seniority, etc. This table is connected to the Applied table via the StudentID and the Courses table via the CourseID. A student can have 1-2 records in this table, as they are eligible to take up to 2 classes, depending on availability.
The Course table houses course info (name, date of class, teacher, room, etc). It is connected to the Enrolled table via the CourseID.
Based on recommendations I searched out, I tried setting up individual filtered portals (so, using a summary field in the Enrolled table that counts all enrolled students, and then filtering by, for example, Math-Week 1. The summary seems accurate (I am getting an accurate count in the field when I look at it on the Enrolled table) but when I use the portal, it only gives me the total count for the student (application) I am looking at. So, if a student is enrolled in 2 courses, the unfiltered portal shows two.
I tried a host of other calculations, but nothing seems to work.
I feel like this should be an easy thing?
I know what you mean about something that seems like it should be easy! I had one big problem that I solved a few months ago, then tweaked something I shouldn't have and only just noticed that it stopped working. I don't remember how I got it to work in the first place and I don't know what I did to break it either. Too much time had passed. I recently learned from an advanced user here that storage settings on fields used in relationships can be important. If you haven't already considered that, it might be something to look into, too.
Have you considered setting up a separate (small) Courses window (list view of items in Courses table) related to Enrolled by CourseID. Then set up a summary field in Enrolled to count enrollments. The field you place in the courses list to display your enrollment count might need to be a a calculation field defined in the Courses table (GetSummary similar to the one in my first post). I haven't tried it but it seems that might work. This is different than my first suggestion only because now you have a Courses table to work with so this time it just might work.
That's all I can think of right now. I have an extra long work day tomorrow, but I'll post this weekend if I think of anything else. If you solve it in the meantime, please do let me know!