Repeating Fields

[nbrown]

I am working on a database that will hold attendance information for 800+ students. I'm using repeating fields to hold the data. They are as follows:

attend_date, attend_status, signinout, period

I would like to create a report that would do the following... Search the repeating field by a date and report back the students name, and the status from attend_status, and signinout, and possibly period. I've yet to figure this one out.. It has got to be something that says... 'If the date I'm looking for is in row 13 of attend_date, then display the information from row 13 in attend_status, and row 13 of signinout' --- Is this even possible, and is this correct forum for this?

Any help is greatly appreciated,

Thanks in advance!

Nic

[ March 13, 2002, 07:34 AM: Message edited by: nbrown ]

[Rigsby]

It’s not the forum that’s wrong, but your approach. From what you’ve described you’ve taken the wrong way of storing your information. You should consider restructuring now, and using related files to store the information you have described. Repeating fields should be avoided whenever possible.

Sorry for the negative news.

Rigsby

[nbrown]

The only problem with doing it that way, is I will have a database that will have 720 fields in it.. I've tried doing it that way, and was very unsuccessful.

Each student must be able to contain Attendance Date, Status, Sign In-Out, Period for each day of the school year. Some students might even have multiple statuses for any particular day.. Which would make the 720 fields not enough yet. Thus the reason I used repeating fields. I'm not even sure something like this is even possible.

[andygaunt]

Um,

Sorry to jump in here, but.......

why would you need 720 + fields.

Why not create a db that has your main fields in it Attendance Date, Status, Sign In-Out, Period.

This can then be related to the student record and you can write a script to pick up the signing in and out time, maybe even the period.

the date can be auto enter creation date through the portal.

5 Fields in a related database.

Yes you will have hundreds of records, but then you can really start to get some reports out of the back of it.

[nbrown]

Ok, I see what your saying.. Let me explain what I'm doing as a whole.. Instead of a small chunk.

A teacher will login, select the period from a drop-down box, the semester, and what room they are in.. FM should then retreive just her 24-30 students and give he the option to mark them as "Absent or Tardy" (Present is assume) and the period that she is currently in.

The Attendance Officer has to be able to go back into the database and mark those kids who the teacher marked as "Absent" either, "Excused" or "Unexcused", "OSS", "ISS", or "Sign-Out" and then with Sign-Out, obviously a time.

See, the problem I'm having.. is for each of these kids I have to store 20 periods for them.. To keep track of them through the day..

For example, during "Semester I" a kid might be in room 202 during 9th period..

Basically, the ability to take attendance for every kid in the building (800+) by period.

I don't too many snags creating the database or the scripting, I'm running into how it should be stored.

Thanks,

Nic

[andygaunt]

OK,

So what you have is

A student database with their names in.

A Class Database which stores all the students classes, i.e. date, semester, period, room.

This is linked to the students database by student id.

Then you have a teachers database for logging in.

Now, if you take these databases and add a fourth we might be getting somewehere.

The fourth database is the driving force behind all of this, bringing all the related information together and displaying through portals.

Basically, The teacher can select their name, period, semester and room.

A portal with a filter on it will then display the matching records from the classes database.

The teacher can then mark those absent.

The attendance officer can then mark all the absent with the relevant option.

By keeping the class information seperate, you can then generate multiple types of reports based on a class, student...basically whatever you want.

Want to know how many times that student has been absent and why. No probs.

Got a problem class that has poor attendance. See the results in a report summarised by absent type.

The hardest part is going to be creating the classes, but it will be worth it in the long run.

[nbrown]

Makes perfect sense... Only one item I'm running into at this point.

The database that holds the classroom information.

We have 10 periods, 2 semesters.. Classes in semester 1 are usually different from classes in semester 2. This is how I'm storing them currently, let me know if you have a better suggestion.

Fields:

studentID

s01p01 (Semester 01 Period 01)

s01p02 (etc,. ec,. 1-10)

s02p01 (Semester 02 Period 01)

If I do it this way, then when teachers are selecting their rooms, semester, and periods.. They'll type their room number in a box that's associated with the correct semester/period.. Which is fine, but I'll need 20 boxes on the database driving all this (interface). Or can I assume there is an easier way?

Nic