Counting groups of records in a table

[wyth]

Okay, this is my first time here. I've been searching for an answer to my problem for about 6 hours, and now I'm posting.

I'm working with a database of students for a university writing center. I didn't build the database, but it's what we have to use. Each semester gets a new database, and each database has two tables, one for student records and one for session records. Each student gets one student record of basic info, and each student gets a session record for each time they come into the writing center for a tutorial. We track primarily through email addresses. Once you enter a student record, the ID fields of both tables are linked, so when you start to enter a session record, it auto fills much of the data. This means that Student X will have one student record, but maybe seven session records. We'll get around 1000 session records a semester.

BUT here's what I'm trying to do. I want to show a count on both the student records and the session records of how many appointments each student has had. The only way I can see to track that is through the ID's that we use (a mix of letters and numbers -- a text field, not numerical). I'd like to have the number of sessions show because each student is limited to a certain number of sessions, and we have had people try to game the system in the past. I'd like it to just show up in a field that reads "Session #_____" but I can't find how to do it.

I've tried using Count, but I keep getting the same results -- I don't get a count for a specific ID, only of all the records in that table. That's not what I need. And I don't want to do it as a report; the desk staff won't be running reports, we get very busy, and I want the desk staff to be able to just see how many appointments someone has had.

I'm fairly new to FileMaker, but I'm a fast study. We're using FileMaker Pro 7.

Is this a real simple thing that I'm just not getting?

[Søren Dyhr]

I don't get a count for a specific ID, only of all the records in that table

Count( the calc' or Count as a summary function? The later only works correctly when sorting for a summary report grouping on say the student's ID or similar, only visible in previewmode or when send to printer. Count( the aggregate function sums over a relation.

So yes it is:

Is this a real simple thing that I'm just not getting?

Since you want a live figure and not reporting, is the aggregate you should have picked instead, but a pretty usual newbee confusion. For something in between the two approaches to the data exists this:

http://www.kevinfrank.com/download/kf-fast-summary.zip

You might wonder why anyone would need it that way, it's becasue the aggregate function works best on limited sets of related data, and it's re-evaluated each time you goes to a new record and the layout renders. If then the summing is done on huge sets of data, will choking, beachballs or hourglasses be likely to be shown. The templates makes the summing only on demand, and the layout renders swiftly.

--sd