Summary Nightmare

[jludwig]

Hi folks! I'm trying to set up a system for our school that will keep track of attendance. I've got a student db and an attendance db. Set up my relationship and that's working. Now, we need to keep track of attendance specific to each quarter. The student db has a start and end date for each quarter, (classes start on different months, so always a different date). How do I get a summary of how many times a student has been present, absent or tardy for each quarter? I created summary fields in the attendance db and that seems to work fine, but I get totals for the whole year not just a quarter.

Help!

Jim Ludwig

FileMaker Version: 6

Platform: Windows 2000

[Ender]

I think your attendance records need to have a Quarter field so that you can sort by that too. Add a Sub-summary by Quarter part to the layout and put the summary Count field on that too.

Using this scheme, you will have to devise an automated way to populate the Quarter field in the attendance table. There are several ways to do this, but it depends on how you are creating the attendance records.

[jludwig]

Thanks for the post! There is one person entering attendance through a portal in the Students DB. Senters the date, then has one field for present, absent, tardy, and Excused or Unexcused if the student is absent or tardy. Right now, the Quarter fields are in the student db, but it looks like they should be in the attendance DB as well. Any ideas on how to auto-populate a Quarter field?

Thanks again!

[Ender]

When an attendance record is generated, you can have the Quarter field populated by a lookup from your Student file's Quarter field.

How are the attendance records actually generated? Are they imported from enrolled students or are they manually generated for each student for each day? Or some other way?

[jludwig]

The attendance records are generated one at a time each day by hand. I've created a field in attendance with a pull down for each quarter number, but the sub-summary only shows me the first quarter count. Sorry, I'm having some trouble wrapping my head around sub-summaries.

[Ender]

The basic idea of summary parts is covered in the manual, but your Attendance Report by Student might look something like the picture below. Lots of ways to summarize data on reports, so don't be shy about playing around with summary parts.

Each Sub-summary part on a layout will show up when the records are sorted by the field specified in the Part Definition dialog. So if you have three Sub-summary part-fields, sort by all of them. In the example below, you would have to sort by Student ID, School Year, and Quarter in that order, for the Sub-summary parts to show up properly.

(Sorting by Student ID works fine for one student's attendance records, but if you are running this report for a whole class, you can sort by additional fields to make it easier to handle the paperwork. Like a sort by Student Name Last, Student Name First, Student ID, School Year, Quarter.)

I think you should also look into automating your attendance record creation a little. It's not difficult to make a couple scripts to import blank attendance records for the Students that are currently enrolled. Your teachers can then pull up their class list in a portal (to Attendance by Date) and enter attendance data. This can also be beneficial because it is then a simple matter to find those attendance records that have not been marked yet. (Don't know if it's a problem where you are, but our teachers sometimes forget to do attendance.)

FileMaker Version: Dev 6

Platform: Mac OS X Panther