Attendance database

[DanBrill]

Hello all,

Been crushed by practical issues lately, and haven't been around the Forum much. (Holding down a job, starting a business, the usual.) But I'm seeing a little daylight again. Now I've got a theoretical problem I'm having trouble getting my head around.

I need to create a classroom attendance database. Nothing too fancy. I have a file (People.fp5) where the student names are held. I need to track their daily attendance throughout the school year.

What would be the best way to structure this so that each teacher can see a list of the students in his or her class, and mark off who is present on a given school day. It seems this is a many to many relationship (many students and many school days.)

Should I set up a file where each record is a school day in session, and another that links the student records to the school days? What will faciliate ease of data entry? (That is, what would a helpful layout look like?)

This must be a fairly common problem, so any help would be appreciated.

Thanks,

Dan

[Fenton]

Hey Dan. I've been a bit crushed myself. It's nice to have work, but it's also nice to have time. Of course I spend most of my "free" time working on FileMaker as well. So much to learn these days :-|

My approach to attendance is to create the records. I think the old "piece of paper with the names on it, checkmark next to the name" still makes sense. That way you have a record of: 1. who's here, and 2. who's not. There's no problems with reports or anything else.

All you have to do is to check whether the records for a date are there, then, if not, loop to create the records. I think there's 2 choices at to when to do this. Normally I'd say a massive operation at startup; get it over with; loop within loop. Or you could do it when navigating to a particular class. But they would need all of them every school day (you'd probably want a "holiday's" table, to avoid doing those).

Yes, it's a lot of records, but very little data in them. It would go pretty fast (unless they're using really old computers).

I suppose you could alternatively just keep track of who is not there; but that's kind of awkward, especially for a teacher who has a lot of students, or a substitute teacher. I don't think it's even legal, as schools often have to document attendance to be compensated.

You didn't mention classes. And periods. Several "entities," but they all fit together. I've got some example files for some of this, classes, etc., in version 6 anyway; 7 would probably be all in one file, because of the interconnectedness; or good candidate for 2 files, data and interface.

[Ender]

Hi Dan,

I think in this case it's easier if you don't think of doing this with a true many-to-many.

For the data entry, you will have an attendance portal showing all current students for the current room and specified date. The current room is likely to be a ClassID field in your Class file. The specified date can be a global field in that file. You can put these together in a concatenated key, ClassID_AttendDate. This will be the key for the portal relationship. In the Attendance file you will create a foreign key, ClassID_Date. (If you plan on making multiple years's attendance records available, you would need School Year to be part of the key too. I recommend archiving past year's attendance records, just to keep the file size down.)

I think it's best if the attendance portal is already populated when teachers pull up the screen, so if a teacher forgets to mark attendance records, it is easy to find the ones not completed in a report. To populate the Attendance database, I recommend (each day) running an import script of all currently enrolled students.

If you need aggregate numbers for each day, just run summary reports in the Attendance file.

I recommend an interface that allows teachers to go back to previous days to fix attendance records, or mark ones they forgot. Below is an interface for one attendance system I did that shows this.

[dbruggmann]

Hi Dan

I remembered a school attendance template I once saw and could even find it again. I'm not sure if it is to your help (personally I never had to bother with this things in FM), but you may want to look at it. It's called

[blomgren]

I have been working for a year and a half on an entire database for a school district to house all of their data -- bios, demographics, grades, attendance, assessments, discipline, faculty, ... This may be more than you want to get into but it is working for me. Each grade level is in a database of it's own related to the main db with names, student id, etc. If you think you may be interested in more info from this snapshot I'll be glad to show you more.

trish

[DanBrill]

Thanks everyone, these are some good directions to follow. Trish, I'd be more than happy to take a look at any example files you have. As I migrate to Ver. 7, the thought of a table for each grade or classroom for each year is not so overwhelming.

Please feel free to post here or contact me directly. [email protected].

Thanks,

Dan

[jrb12]

Dan et al,

I'm really surprised by how accepting those in this thread are to storing a record for every student who is NOT absent from or late for class. Why? Does your local police department store records noting the citizens who do NOT rob their local convenience store? The fact that a record is NOT in a database is informative in itself. Sorry for the caps, its just rather startling.

I suspect though, that the reason for storing so many non-event records is that its easier from a relationship/portal perspective. Having to create an attendance record when the teacher selects absent on the portal row is bothersome. Not to mention deleting it when he realizes the student is just off in the washroom. Plus there is a second relationship to consider in picking out the attendance records while already inside a portal for the class/student relationship (for ver6). Its not a trivial problem. But its worthwhile pursuing a solution to it. If not to keep the database cleaner and make searching and sorting much quicker or to follow the guideline of keeping the database structure simple and putting the logic in the business layer (read script) then just because its an interesting problem. And that's why I seem to spend so much time mucking around in FileMaker. The bleeding obvious sometimes is very non-trivial.

I've worked with private schools for the past 10 years and come across a number of school admin packages as well worked for a company that markets one in FoxPro. None of those record the fact that a student is present. Its assumed. I've also developed an online school attendance solution in C# on Sql-Server. It never occured to me to store all those records.

To put credence to my rant, I spent some time today roughing out an example student/attendance DB in FMP6. Attached is the zip file. Please have a look. I'd be happy to have you find something useful in there and wish to use it in your project.

Cheers,

John.

StudentAttendance.zip

[Ender]

Dan,

Unless there are compelling reasons to do so, I would not separate different grades or classrooms into different tables. Much easier to fix a bug or add a report to one table rather one for each grade. Much easier to show a student's history when it is all together in one table.

To me it makes more sense to have Grade as a function of Graduation Year and School Year. Classroom should be a simple text field.

[Ender]

Hi John,

Your method has promise; certainly it would be nice to reduce the size of the attendance file.

I know that one reason to have records for Present days too is for making attendance reports. I think you need those records to calculate a classroom's % Present (for a user-specified date range) and to show an individual student's attendance history (showing dates attended IS a requirement for my system.)

I suppose if you didn't need these types of reports your method could work. Have you dealt with these types of reports in the attendance systems you have worked on?

[stanley]

Hi all:

If you look at a teacher's attendance book, the format tends to be a check-mark for "present" and a blank space for "absent." This is logical. It is also logical to have an attendance record for every student for every class-instance, because a class-instance may have more, relationally, than just a record of if they were there or not. Was it a sex-ed day? Was it the day of the class photo? Was it the day the idiotic recruiter from the Army turned up? Was there a quiz? A test? A "yes" or "no" (and/or a grade) is easier to deal with than the process of finding missing records from the found set (in the sense that if a student wasn't present, there's no record, or vice-versa)...

Anyway, these are fairly short records, and you can fit (literally) millions of them on a modern hard drive before you begin to run out of space. How many student-class-instances do you have? You've got to remember that you're (generally) dealing with gigabytes nowadays on a hard-drive, and that's a lot of space.

Just my rant.

-Stanley

[Fenton]

Another idea for reports would be to summarize attendance by month, using another table. Do your reports there. A script could do this kind of maintenance once a month. It would take a little while perhaps, but not that long. Another script could summarize these by year once a year. You could either toss, or archive to another file then toss the individual records. I don't see that having lots of records in the daily attendence table is that big a deal.

I don't know how they do their attendence data entry. It makes sense to me to have a record for each student's attendence, which even a total stranger, such as a substitute teacher, could use for roll call. How would such a person know who is absent without checking off who is there? Or maybe they just do it off a piece of paper in the classroom, then enter it later? It's been a long time since I went to school.

Another factor is what to do when a student leaves, moves away. If you're not counting them every day, just assuming they are there, then you'll have to take into account when a student moves in the middle of the month. This doesn't much matter if you're just looking for a loose system. But if it has to be exact that would matter. It's simple if you're counting them every day.

[jrb12]

Ender,

The reports you mention are of course always necessary and the packages I've dealt with did include them. I've written some in Crystal Reports. The advantage those systems have in using SQL for data access is the ability to do outer joins between tables. SQL can retrieve records from one table along with any matching records from a second table. If there is no matching record, that fact can be returned by providing empty fields.

Determining how many absents a student has in a given time period is equivalent to counting the number of absent records. The number of present days is some calculation involving this and the number of school days in the period. One table those packages do have is a calendar. That contains one record for each day in the school year with a field for type of day (school, weekend, holiday, etc). This can provide a count of school days over a time period.

As for FileMaker, you'll note the field 'AttendanceDaily' in Student.fp5 does return the value 'Present' when there is no record in Attendance.fp5 for the date in question. This value for the attendance code is represented by the absence of a record. So in a sense the database already stores this information. Explicitly storing it is redundant. The issue, as you point out, is how to exploit this fact in FileMaker. I'll spend some time on this and see what comes up.

There is another approach to consider for the reports you mention. Take them out of FileMaker and do them in Crystal Reports or some Mac equivalent. That is if the only reason you store so many records is to create the reports.

Stanley,

Your comparison with the teacher's attendance book is fine for the layout the teacher uses to enter/display the data. This affects but doesn't determine the internal organization of the data. At least this is true in a multi-tier application. In FileMaker the layout is usually pretty much the database so I can understand why you'd organize it like the paper equivalent.

The fact that a particular day was Army recruiter day is information relevant to the day and perhaps class, storing this in every student's class/student/day record is redundant. Firstly, relational database design is hugely about minimizing redundancy and secondly this approach trades off design time effort for the ongoing effort of ensuring all those extra records get put in the right place before they're needed and stay there over time. I'm all for doing the work up front and keeping the maintenance simple.

Call me Simon, but your last paragraph is an excuse. In the long run, this thinking is exactly why gigabytes are not going to be enough.

Cheers,

John.

[jrb12]

Fenton,

Its a good suggestion using a script to build intermediate data for the report. Build the totals into a separate database, produce the report then throw the data away. By rebuilding the report data each time you give the user more control over the parameters for the report. Also, you don't dump responsibility for producing the data on some other process or worse, a user.

Following this idea, I've attached a zip which includes an aggregate report which shows attendance for a class over a given period of time. Ender, this may provide the structure for your %present classroom report. The script for this report first clears accumulator fields in the student record, then transfers to the attendance table to loop through the relevant records accumulating into the student table fields. The report itself is just a list of student records with summary fields added in the footer (though it could be done as a class/student portal). This implementation obviously only tolerates one user at a time.

Your second report, Ender, listing a student's attendance history, could be accomplished with the calendar table I mentioned previously. The report needs a record for every date in the requested range. Currently this requirement is satisfied by storing these records for each student or worse, each student/class. I content its only necessary to store these once. The report can be produced in the calendar table with the 'AttendanceDaily' field and relationship copied over from the student table. Though using a global for the StudentID and the date from the calendar record for the relationship to the attendance table. Do you think this would satisfy your attendance history report?

Fenton, I'm not too clear on your second paragraph. Consider that the teacher is looking at the layout you present to them. They are not looking at your database field structure. This idea of separating the user interface from the data structure has been around a long time but not, it seems, in the FileMaker world. Likely because a layout field, with the exception of a merge field, is a database field. I think a separate thread discussing ways of using multi-tier methods in FileMaker would be useful. And why they are a benefit, of course.

The case of when a student leaves a class in mid term as far as attendance is concerned can be handled by first storing a record somewhere to indicate the event has occurred. Then at data entry time, by checking for a record of withdrawal before deciding whether to display the student in the portal. At report time there is no change to the logic as there would be no further attendance records added after the withdrawal date. Any added prior would be included in reports if they fit into the report's date range. If the student withdraws before the cutoff date for the course, you would delete the entry and all relevant attendance records at withdrawal time and continue on as if he never existed ... and so on ... its a matter of following through all the event scenarios and applying the target site's rules to each.

BTW. I like your FM101. Great idea.

Cheers,

John.

StudentAttendance.zip

[Fenton]

About my second paragraph. I'm just saying that I don't know exactly what the teacher knows. Does he/she just glance at the classs, then enter a couple absences. Or do they do a real roll call? Do they do it on a laptop? Do they do it on a piece of paper?

I don't know which is better, create the multitude of records then post-process them, or build an interface to only create "absent" records, and a more complex report routine. I would tend toward the former, especially in version 7, which has such high limits.

One thing I'd really like to see in future versions is scripted access to the 2 File Maintenance operations. It would be useful in one of these multitude of records, summarize then delete situations.

[solarpunk]

anyone email me example of attendance database. i working on something like this for massage school. Basicall I want to keep track of the hours the student miss, the subject. i looking for something when teacher do a grade book things.

if someone can email me to, webmaster @ asmt.com

Thanks

Victor