vocational school attendance record

  • Thread starter Thread starter ToniJ
  • Start date Start date
T

ToniJ

I am looking to create a permanent attendance report.
I presently record all absences.
For the permanent record I also need to show when present.
I have a student, absent date, and number of hours absent.
So I created a table using all dates for 2008, indicating if these dates
were holidays or class days.
I want to create a report showing all dates, default to present unless the
student has an absence for that date.
But I don't know how to do it, or if there is a better way. I looked for a
template and couldn't find anything.
 
Dear ToniJ,

You'll have to deal with a lot of difficult picky clarifications after which
the database design will probably be simple:

-What do you mean by "default" date?
-More specifically, what do you want to be on the printout (report) do you
want to get from this?
-Is "Present" : A. something that you are going to specifically record, or
B. is it something that you are going to presume if there is no absence?
-If the answer to the previous question is "B", for procedural/political
reasons, do you want to load "present" in the actual records? (vs. the
reports just deriving/presuming it at the time of the printouts)
- When you say reporting "hours" are you implying that you are going to
record absences that are just a fraction of a day? And, if so, what is the
smalles fraction of a day that you need to record (1/2 days? Hours? Minutes?)
Or are you just going to derive hours for full days?
- If they are just a non-school day (like weekends) why are listing
holidays? Do you need to allow for the ability to record attendence on a
holiday or other non-school day?

Be careful how you answer these. If you get too thorough, recording the
data may become your new full time job. :-).
 
Sample of what I am looking to accomplish, showing the entire calendar year
for each student.

Student Name: John Doe
Date: 1/1/08 1/2/08 1/3/08 1/4/08
1st Class (2.0 Hrs.) A A T P P P
2nd Class (2.0 Hrs.) P P P P P P
3rd Class (1.5 Hrs.) P P P P P P
4th Class (1.5 Hrs.) P A T P A A

I have the data with the students tardies and absences.
But I need to create a report for each student that shows the entire
calendar year as the example above, with a default of present on school days,
unless the data I have shows them absent or tardy, And I need to be able to
note which days or non school days.
 
First, I think that you realize that in order to record that level of deatail
(attendance of each student for each day of each course) means that you will
creating/ entering a lot of records.

Your "horizontal" day by day display requirement pushes this towards a very
wide report and away from what would be considered to be good databased
design and a typical database application and easy ability to do "by student"
summaries. But this would do it: Make a table which lists students.
Including a Primary Key StudentNumber field

Make a table which lists the courses, the hours for each, and a primary key
Class Number field.

Make a table which will have a record for each instance of enrollement of a
student in a course. Two of the fields will be StudentNumber and
CourseNumbeer. Link the to those two tables accordingly, And make a field
ffor attendence for each day of the year that you want to record. (hopefully
less than 250). Forms and reports that you are looking for could then be
easily developed from that. Set the controls in the report to display "P"
when that "cell" is empty/null.

A better structure (bt which would not format the printout as you describe)
would be to still make those 2 Student and Cour4se tables, but, instead of
that third one, make a table which has a record for each instance of a
student being absent or tardy for a class on a date. This table would have
only 3 or 4 fields. CourseNumber, StudentNumber, Date, and the box with the
"T" or "A"
 
Sample of what I am looking to accomplish, showing the entire calendar year
for each student.

Student Name: John Doe
Date: 1/1/08 1/2/08 1/3/08 1/4/08
1st Class (2.0 Hrs.) A A T P P P
2nd Class (2.0 Hrs.) P P P P P P
3rd Class (1.5 Hrs.) P P P P P P
4th Class (1.5 Hrs.) P A T P A A

I have the data with the students tardies and absences.
But I need to create a report for each student that shows the entire
calendar year as the example above, with a default of present on school days,
unless the data I have shows them absent or tardy, And I need to be able to
note which days or non school days.
Click to expand...

Just an idea:

Assuming you have a table named Calendar containing every class date.

Assuming you have a junction table named StudentClasses joining Students and
Classes.

Assuming you have a table named StudentClassAttendance, which only has data
for the days a student was absent.

This creates an attendance record for every class day for every student for
every class. (could be a lot of rows)

INSERT INTO StudentClassAttendance
(student_id,
class_id,
calendar_date,
attendance_code)
SELECT StudentClasses.student_id,
StudentClasses.class_id,
Calendar.calendar_date,
IIF((SELECT COUNT(* )
FROM StudentClassAttendance AS a
WHERE a.student_id = StudentClasses.student_id
AND a.class_id = StudentClasses.class_id
AND a.calendar_date = Calendar.calendar_date) = 0,
"P",(SELECT a.attendance_code
FROM StudentClassAttendance AS a
WHERE a.student_id = StudentClasses.student_id
AND a.class_id = StudentClasses.class_id
AND a.calendar_date = Calendar.calendar_date)) AS
attendance_code
FROM Calendar,
StudentClasses;

TRANSFORM First(StudentClassAttendance.attendance_code) AS Atten_code
SELECT StudentClassAttendance.student_id, StudentClassAttendance.class_id
FROM StudentClassAttendance
GROUP BY StudentClassAttendance.student_id, StudentClassAttendance.class_id
PIVOT Format([calendar_date],"Short Date");
 
Back
Top