Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / May 2008

Tip: Looking for answers? Try searching our database.

vocational school attendance record

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ToniJ - 13 May 2008 18:05 GMT
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.
Fred - 13 May 2008 22:41 GMT
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.   :-).

 

> I am looking to create a permanent attendance report.
> I presently record all absences.
[quoted text clipped - 6 lines]
> 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.
ToniJ - 14 May 2008 16:01 GMT
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.
                                                   

> Dear ToniJ,
>
[quoted text clipped - 32 lines]
> > 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.
Fred - 14 May 2008 19:07 GMT
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.
[quoted text clipped - 49 lines]
> > > 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.
Michael Gramelspacher - 14 May 2008 20:56 GMT
>Sample of what I am looking to accomplish, showing the entire calendar year
>for each student.
[quoted text clipped - 49 lines]
>> > 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.

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");
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.