Summary fields & summary reports

[Kremlarkin]

Hello all,

This is another newbie question, though an easy one. I am having trouble creating reports that summarize my data. I was wondering where i could go (books, online resources -free if possible) to learn ALL about how to create summary fields and reports appropriately.

*My specific problems is:

I have an attendance database and attendance is recorded in the field "AttendanceStatus". Three values are allowed as input to this field: Present; Absent; and Homestay. I would like to be able to display the total count of presence and absence for a given month for a given student, but every attempt i have made results in either the averages or the totals for every student in the found set.

I have one field (cPresence) checking for the text "Present" in the field AttendanceStatus and returning a "1" if it finds that text. My summary field (sPresentCnt) is a 'count' of (cPresence). Anyway, its an un working mess at the moment and i need to find some instructional material that will help me figure out how to best create summary reports in all sorts of circumstances.

Thanks guys!

attendance_module.zip

[Fenton]

I don't know offhand where a good source of summary report info is. But here's a few basics. I was looking at the Attendance Stats layout. I didn't get into the other, with the relationships and such.

1. Subsummary parts are defined to appear when records are sorted by the "break" field specified in their definition.

Your part had "StudentFullName" as the break field. That's what the 1st sort has to be, not StudentLastName.

2. Your basic counting calculation field was defined on a self-relationship on the name, etc.. Relationships are not really needed for a simple report. Especially not for the base counting field.

Case ( PatternCount(AttendanceStatus, "Present"), 1, "")

with a number result

The Storage Options should be turned back ON. It will be many times faster (like 100x).

3. The Summary field is more safely defined as Sum (cPresence). Count will work in the above case, but it counts anything.

It makes no sense to put it in the body; it's a "sum". It should be in whatever Subsummary part where you want to see the total. In your report it seemed like it would go in the StudentFullName Subsummary.

It can be in more than 1 (if you had for instance, Year, Month, Week Subsummary parts).

4. You don't really need the Body part if you just want the totals. It's only needed to see daily attendance, 1 day, 1 student at a time.

5. The Header appears at the top of every page. There's also a Title part. If it exists, it will appear instead of the Header on the 1st page, with the Header on other pages.

[Kremlarkin]

Fenton, i'm trying your solution...

[Kremlarkin]

Ok, i have implemented your suggested solution and am one step further to my goal. The problem i have now run into is the numbers that i am given. For instance, "32" for student 'aaa aaaaa' is not the correct summation of the number of times the student was present. Interestingly, i have created two calculation fields that record the total number of averages for the given found set. One field returns a "1" if the field "attendancestatus" has 'absent' as its value (0 if anything else or nothing). The second calculation sums the first calc field for a given found set. It does this through a relationship (studentID=::studentID) so that it will only sum the absence_calc fields for a given student. This set of fields, dealing with absence, works correctly. The two fields for presence are set up exactly the same, however they do not produce correct results. For instance, the second presence calc field is set to sum the first presence calc field (just like the absence fields). In the current found set, the field cPresence for each record contained the number "0". The field "cPresentCnt" summed these 31 zeros as 17. Now, i have no idea where it is getting the number 17.

I'll post another copy of the file. (I just changed the student names and in checking it all out, student's "D" and "E" are working correctly. I wonder if there is something wrong with my relationship -though i can't see what...)

Secondary information: I am first running the Find Date Range script that can be accessed from the script menu. I am isolating the month of December.

attend_module.zip

[ESpringer]

For student aaa (etc.) it's not showing 32 here... It's showing 17 total present...

Perhaps a tangential comment: Wouldn't you rather be doing an attendance summary out of the students.fp5 file, so that each row in the report corresponds to one student? (Or are you tapping this information from the student file?

[Kremlarkin]

Quick reply. I believe i have discovered a part of my problem. I believe I need to be basing the sums in the cAbsenceCnt and the cPresenceCnt fields off of a different relationship that concatenates the studentID fields and the found set's date range (for the left key) and the studentID and the date_attendance (for the right key). When thinking about it, i believe that the summation fields are pulling data in from all the records that correspond with a given student, regardless whether or not those records are in the current found set. If i am right, then i will have to create a new self-join.

Espringer (glad your here as you helped quite a bit with my last major problem). I'll try creating the cAbsenceCnt and cPresenceCnt fields in the students.fp5 file and see if i can get accurate data that way (well, mainly just to see if i can figure it out). That will be a project for tomorrow. This is my last post before i get in bed for the night. Thanks to you both for providing valuable input.

[Fenton]

I dunno. I do a simple Find for "aaa aaaaa" and "present" and I get 32. Find for "aaa aaaaa" and cPresent = 1, also 32. Maybe if you're finding a subset, by month or something, you're seeing different (Nov. is 16).

I doing this in Attendance. It's just a flat file really. I was just trying to fix up a simple subsummary sum/count problem.

There are times to use self-relationships, there are times to use summaries/subsummaries, and there are rare times to use both. Maybe I'm missing something, I see the basic problem is just a simple summary sum.

[ESpringer]

I would get the same find as Fenton, but I take it Kremlarkin's looking for the data for any given month at a time (given his previous posts), and in fact the month's worth *is* corectly reflected in his self-join calc. If I understand right, he was assuming that if he *found* a set of records in the att file, the student's relational search would be constrained to that found set; but instead it was returning the full 32, because the relation was tallying attendance for that student rather than going to a representative record *within a month* and pulling out the self-join-calc total for that *month's* attendance for that student. Yes?

And you're right (Fenton) that it's easy to *get* the data here in the flat file, but to print a report for a month-worth of attendance, with one row for each student, it seems much cleaner to do it in the student's file. No?

[Kremlarkin]

You are correct. I am attempting to isolate and obtain summary information for one month at a time. You are correct in all of your assumptions. I am having trouble telling it to sum attendance records for ONLY the month represented by the found set. Do you think i will have to create another relationship in order to do this?

[ESpringer]

Kremlarkin,

You don't need to get the students file to do any summarizing or counting at all, actually, because you already have the data tallied within the attendance file. All you need to do is to set up a global field for the first (or last) day of the month for which you want to do a report, and a concat field which looks something like:

ID&"_"&DateToText(gMonth) [sorry I don't have the file here, so I don't remember how you did it, but:]

You already have a similar concat in the attendance file, right? It's what's doing the work to yield those correct self-join totals.

So you can have the students file retrieve the monthly total data from "the" related record in the attendance file, using a relation between the two fileds that concat ID&"_"&gMonth

(When there's more than one related record, telling it to find "the" value results in FileMaker choosing the first related record. That will do just fine for your purposes.)

Does that make sense? (Sorry to be hasty, running out for now...)

[Kremlarkin]

I believe i have it working. Thanks to you both for the input. I have asked my relatives for a Filemaker download that i can just pour into my head.

Again -thanks...

[Kremlarkin]

Oh, to give you more of an update as to what i did -

I used the concatenated LeftKey (RECORDID / gMonth) & RightKey (StudentID / MonthName(Date_Attendance) ) keys that you suggested. The key i was thinking about creating was MUCH more complicated than that and i don't know why i thought it had to be so complicated. I just wasn't thinking. I also put the report in the students.fp5 file and it was MUCH easier to accomplish that way as you predicted, espringer. Thanks.

[Kremlarkin]

Ok, last post on this thread i hope. Not only have you two helped me out with the attendance problem, you two have also helped me make another part of my database MUCH more efficient.