Integrating Daily Attendance for 41 Schools

[leeinhaiti]

I’m a self-taught newbie (used Lydia tutorials) who is using Filemaker Pro 10 to organize an evaluation I’m conducting of an NGO’s educational program. It breaks down roughly like this:

1 Table for School Superintendents (11 records: Some are linked to multiple schools, others only 1)

1 Table for Schools: (41 records, all linked to superintendent records)

I will soon be collecting daily attendance (daily in its record keeping, but reported to me on a monthly basis) for each school, broken down by grade (7 grades per school). So there will be 287 monthly attendance forms submitted to me (each one with daily absence totals and what a code for the weather outside, ie, S for Sunny, C for cloudy, LR for light rain, R for rain, HR for heavy rain, TR for tropical storm, and C for cyclone [hurricane]).

Right now the only way I can think of doing this is creating new table for each month, a record for school IDfk, and 2 records for every school day: 1 for the total absences on that day, and 1 for the weather (which will be coded).

There must be a more elegant solution than this, no? I will be eventually be exporting all this data into STATA (but that’s another issue), so it doesn’t have to look pretty. Just wanted to know if there was an easier or more efficient way of doing this.

Thank you for your help. I’m living here in Haiti for 8 months, evaluating a huge program, and I’m a little overwhelmed at the moment.

[comment]

You definitely do NOT want a new table for each month. It's not clear whether you want to enter the daily totals only, or all the individual grade data, but it should all go into the same table.

[Fitch]

I'd think you'd have one attendance record for each of those 287 forms. In each record you'd have these fields: school ID, grade, date, absences, and weather. Sounds like you might also want a month field, which could be auto-entered based on the date.

Welcome to the forums!

[leeinhaiti]

So I should make 1 table, called attendance, and an individual record that would capture:

Record #1: School X, Grade 4, October

Record #2: School X, Grade 5, October

Record #36: School Y, Grade 2, October

Record 245: School Z, Grade 9, November

And so and so forth, for a total of 287 records for each month (41 schools x 7 grades per school)?

Each record will capture:

The month

The school

the grade within that school

30 days of that grade's cumulative attendance

30 days of weather data

Do you think that would do the trick? Seems pretty repetitive, but what do I know?

Thanks for your help!

--Lee

[comment]

No, I think each record should be either the way Tom described, i.e.

SchoolID:

Date:

Grade:

Absences:

Weather:

or, if they already made the summaries by month, grade and weather for you, and you are willing to discard the daily details:

SchoolID:

Month:

Year:

Grade:

Weather:

Absences:

IOW, you need less fields and more records.

[leeinhaiti]

So, just to be clear, 1 record for each day?

So we're looking at 8,610 records, per month?

(41 schools x 7 grades per school x 30 days)

I think that's what you're saying, and it makes sense, especially for later on when I want to use that data statistically. Didn't realize I was talking about that many records though! Whew! Got my work cut out for me.

Unless I'm missing something?

[comment]

Yes, that is right (except I presume you will only get reports for school days, so it should be "only" 26 or 22 days per month).

In terms of data amount, 1 record x 30 fields is the same as 30 records x 1 field. However, you cannot easily aggregate fields, so it's best to atomize data by records.

Don't see why that should be more work for you - on the contrary: you have considerably less fields to define.

[leeinhaiti]

First: Can't thank you guys enough. Have been a huge help, and definitely saved me a ton of time and mental anguish in the long run.

One last question, that could be a huge time saver.

So, each month I'm going to receive 287 separate attendance sheets, which I'll have to enter, creating a new record not only for each sheet, but for each individual day on each sheet.

What kind of script could I write, that could automatically move the date to the next day on the creation of a new record, up to 30, at which point it would reset back to day 1 sheet?

For example, after creating the first record of 10/1/09, I hit new record, and the date is auto filled in as 10/2/09, progressing record by record up to 10/30/09, after which it resets back to 10/1/09 for record #31, and I would begin entering a new attendance sheet.

Something to at least automate part of the data entry process?

[leeinhaiti]

First: Can't thank you guys enough. Have been a huge help, and definitely saved me a ton of time and mental anguish in the long run.

One last question, that could be a huge time saver.

So, each month I'm going to receive 287 separate attendance sheets, which I'll have to enter, creating a new record not only for each sheet, but for each individual day on each sheet.

What kind of script could I write, that could automatically move the date to the next day on the creation of a new record, up to 30, at which point it would reset back to day 1 sheet?

For example, after creating the first record of 10/1/09, I hit new record, and the date is auto filled in as 10/2/09, progressing record by record up to 10/30/09, after which it resets back to 10/1/09 for record #31, and I would begin entering a new attendance sheet.

Something to at least automate part of the data entry process?

[comment]

First, I am puzzled why can't you import their data. I see that you have FMPA, so you could even give them runtimes to do the data entry and send it to you.

Anyway, there are thousands of ways to assist in data entry (esp. in version 10 with script triggers). You could certainly enter a looping script working the way you described, roughly:

...

Set Variable [ $d ; Attendance::Date ]

New Record

Set Field [ Attendance::Date ; Case ( Month ( $d + 1 ) = Month ( $d ) ; $d + 1 ; $d - Day ( $d ) + 1 ) ]

...

[leeinhaiti]

The first idea is wonderful in theory...however, very few people here (southern Haiti) have reliable internet access. So, for the first few months anyway, we unfortunately need to stick to paper so they can get comfortable with this whole "taking attendance" notion. Need to take baby steps with these superintendents and schools, otherwise I'll freak 'em out and won't get any data at all. Thanks for the script suggestion though, I'll give it a whirl.

[leeinhaiti]

BTW, any suggestions on some good places to look (FM Forums not withstanding, of course), for those thousands of scripts that might help with data entry? Or should I just google it and stop bothering you?

[comment]

They don't need reliable internet access. They could export the data and e-mail it you when they do have access, or send you the physical file on any kind of removable media.

You can ask anything you want - it's just that some questions are too wide to answer efficiently, esp. those concerning building a user interface. Without being thoroughly familiar with the workflow, all I could say are generalities - or start writing a book about all the possible ways to do this.

[leeinhaiti]

While I definitely would love to have them fill out the forms electronically, frankly, it's not going to happen. As for layouts, not too concerned with anything fancy right now, it's a very utilitarian database, only being used by me. Do need to familiarize myself with some scripts that can automate some facets of the data entry process. Guess I'll take a look around the forums here and the wider web. (while we still have power!)

[Peter (duksis3)]

Hi,

The best is to record as much data as possible - it future it will paid back.

But your overall productivity due to 2 parallel "workflows" - one computer based and other paper based - will be low. Very typical problem around the world.

Here is no question how to implement FM because you can record attendance in many ways. Main is to start with "open" for future solution.

Superintendents (if internet isn't accessible) must fill Numbers, NeoOffice or Excell files (you are on Mac) in form you have defined. Form must be e-mailed or delivered in another way to you and you import records in FM. Spreadsheet colons will import to field and rows will be separate records. If superintendents haven't computers - give to them paper forms thy must to fill and later use some OCR to import data in FM.

Probably this is not right forum to discuss productivity ... ;)

[comment]

As for layouts, not too concerned with anything fancy right now, it's a very utilitarian database

I wasn't talking about fancy - just efficient. I don't see the way your paper forms are organized, so it's difficult to advise on the best order of actions. I'd probably enter the current school ID and grade into global fields, start the loop and punch in the number and the weather (don't forget a button to take you out of the loop).

your overall productivity due to 2 parallel "workflows" - one computer based and other paper based - will be low.

I don't see productivity as the main issue here. My concern would be for data-entry errors. If possible, I would enter the data twice, then look for discrepancies.

[leeinhaiti]

Is there anyway I can email you my database (it's only 1.5 mb) so you could take a look and see if there are any inherent flaws in it?

I'm trying to get it as "clean" as possible before the rush of data starts flowing in. Is there a way of posting the database without any of the actual fields filled out? Just to avoid privacy issues...

[comment]

You can save as a clone (no records), zip it and attach it here. I make no promises, however....

[Peter (duksis3)]

Use Save a Copy as ... clone and create (or not) empty record or record with sample data

[leeinhaiti]

Here you go. Sorry for the delay, we've been having some power outages down here! Thanks for any feedback you can give me, any potential structural problems, etc. You have no idea how helpful this and how grateful I am. We're trying to evaluate a fairly large, long-running educational program in southern Haiti.

Program_Evaluation_Clone.zip

[Peter (duksis3)]

OK.

I will take a look and then can say something. Please, allow a day to response.

[Peter (duksis3)]

Ok, I quickly look in to database and as I understand, you try to do 2 things - 1) manage schools, teachers and students 2) make a statistic survey.

If I have such task I will use 4 tables - 2 for survey or supervision (as you have done) and 1 for schools (directors included) and 1 for students (student data and attendance). Layouts is up to you -use tabs or separate layouts etc.

The most interesting point for me is the way how you plan to organize it all - I'm writing my thesis in education management. So probably we can communicate via e-mail on this or other topics. Pls, write to [email protected]

[comment]

A few things that stand out:

What is the role of the table named School_Director_Beginning_of_Year_Survey? It seems to be describing a school, not a director. Will there be multiple descriptions for one school?

The table School_Control_Group_Beginning_of_Year_Survey seems to be almost the same thing.

Any time you have numbered fields, e.g.

'07 '08 Number of Students

'08 ‘09 Number of Students

'09 ‘10 Number of Students

it's time to think about a related table where each school year is a record.

School Name is NOT an attribute of a teacher. This is just an example - I believe there are more of these. You should ask yourself what type of entity does a table represent and include only fields that describe this entity.

Check your field types: for example, Date Survey Administered should be Date, Who_collects_attendance cannot be a Number.