Finding an Absence of Data

[selzlerb]

Hello Team,

I'm working on a database that I will use in a school district to report on Attendance information. Attendance is tracked by our student information system, and I'm exporting that data to FMP.

Incidences of attendance are identified by letters. For example a "Tardy" is identified by the letter "A", Tardy +30 by the letter "B", Unexcused Absence by the letter "C", etc. The attendance codes range from A through L. With each letter representing a unique entry.

I've designed the database from the perspective of wanting to identify instances of Attendance and link them to students. That seems to be working well. But I'm now trying to figure out how to identify those with no entries for certain codes. For example, list every student at a school without a tardy. Or, list every student at a school with perfect attendance, or no absences.

I'm looking at my table relationships and I'm not seeing how I'm going to sort that out. (Note the attached graphic) I've been playing with finding and sorting through the absences table, however I'm thinking I'm on the wrong track with that.

Any help in the right direction would be greatly appreciated.

Thanks.

- Sez

[Ender]

There may be other ways to do this, but I might try this by adding a text field with global storage to the Student table, maybe call it gAbsenceType, and use this as a filter for another relationship to the Absence table:

Student <=>Absence by AbsenceType =

Student::Student ID = Absence by AbsenceType::A Student ID

AND Student::gAbsenceType = Absence by AbsenceType::AbsenceType

You can then define an unstored calc in the Student table to show the count of absences of the selected gAbsenceType. From this, you can build a report to find the records having the desired count.

[selzlerb]

Ender,

This is interesting. Thanks.

Your response has made me rethink the process. Note that I have a portal taken from the attendance table that identifies the attendance records for individual students.

Now I'm wondering if there is a way to leverage that to display the information I want by school, or will the global field solution be more effective in the long run?

Let me know what you think.

Thanks.

- Sez

Edited October 27, 2006 by Guest

[Ender]

Well, you could do the same type of thing in the School table, but with thousands of related records, those calcs will end up being pretty slow. It might be better to set up a similar structure, but use a script to calculate the aggregates and populate regular number fields.

Alternatively, you could build a columnar report with sub-summary parts in the Attendance table, and get counts in a report format for each School and each Absence type.

[selzlerb]

Ender,

I see your point. I think the global field solution is the best of the two ideas. Thanks.

If I understand this correctly, I'm going to create a global field in the student table that corresponds to each attendance field? So if there is an addtendance field for "Code A", there will be a global field for "Code A", "Code B" / "gCode B", etc. ? Sort of like generating calculation fields for a field with multiple response buttons or checkboxes?

Let me know what you think.

- Sez

[Ender]

If it's important to see the counts for each Absence Type at the same time, then yes, going with a global for each with a corresponding relationship (TO) to the Absence table would be fine.

Or you might define regular number fields for each, just use the one global, and use a script to loop through all the Absence Types, populating the Counts for the record. This second option involves a little scripting, but the relationship graph is kept cleaner. Another advantage is that the Counts would then be stored, making any Finds on them faster.

[selzlerb]

Hello Team,

I'm continuing to have a terrible time with this issue. I'm hoping that my database structure hasn't painted me in a corner.

If anyone has any thoughts on identifying specific absences codes as defined above I would really appreciate the help.

- Sez