Expert guide

School Attendance Tracking Database in Microsoft Access

Registers in seconds, accurate percentages, and absence patterns spotted in week three, not at report time

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: model attendance as Students, Classes, Enrolments, Sessions and Marks (one row per student per session with a code such as P, L, A, E). Generate sessions from each class timetable, take registers on a continuous form that defaults everyone to Present, then calculate attendance rate = attended ÷ expected sessions with queries. Flag students under your threshold weekly and produce parent letters from a report. We build these as custom Access databases.

Small independent schools, tutoring centres, language academies, apprenticeship providers and after-school programmes often track attendance in spreadsheets with a column for every day. By November the sheet is 90 columns wide, percentages are calculated differently on each tab, and nobody notices a student missing every Monday. A properly structured attendance database fixes the arithmetic and shows patterns.

The Tables

TableKey fields
tblStudentsStudentID, Name, DOB, Guardian contact, Status
tblClassesClassID, Name, Teacher, Room, Weekdays, StartTime, TermStart, TermEnd
tblEnrolmentsStudentID, ClassID, EnrolledOn, WithdrawnOn
tblSessionsSessionID, ClassID, SessionDate, Cancelled (Y/N)
tblMarksSessionID, StudentID, Code, MinutesLate, Note, MarkedBy, MarkedAt
tblCodesCode, Description, CountsAsPresent, Authorised
CodeMeaningCounts as presentAuthorised
PPresentYesn/a
LLateYes (policy)n/a
EExcused / authorised absenceNoYes
AUnexcused absenceNoNo
MMedicalNoYes

Generating Sessions From the Timetable

Public Sub GenerateSessions(ByVal classID As Long)
    Dim db As DAO.Database, rsC As DAO.Recordset, d As Date
    Set db = CurrentDb
    Set rsC = db.OpenRecordset("SELECT * FROM tblClasses WHERE ClassID=" & classID)
    For d = rsC!TermStart To rsC!TermEnd
        ' Weekdays stored like "Mon,Wed,Fri"
        If InStr(rsC!Weekdays, Format(d, "ddd")) > 0 Then
            If DCount("*", "tblSessions", "ClassID=" & classID & _
                      " AND SessionDate=#" & Format(d, "yyyy-mm-dd") & "#") = 0 Then
                db.Execute "INSERT INTO tblSessions (ClassID, SessionDate, Cancelled) " & _
                           "VALUES (" & classID & ", #" & Format(d, "yyyy-mm-dd") & "#, False)", _
                           dbFailOnError
            End If
        End If
    Next d
End Sub

Mark holidays and cancellations by setting Cancelled = True rather than deleting sessions, so the record shows what happened.

The Register Form

  • Teacher picks class and date; the form lists enrolled students for that session.

  • A button 'Mark all present' inserts P for everyone, then the teacher changes only exceptions.

  • Single-key entry: typing L, A or E in the code column moves to the next student.

  • Late minutes and notes optional; MarkedBy and MarkedAt filled automatically.

  • A register that has not been taken by a set time appears on the office dashboard.

Private Sub cmdAllPresent_Click()
    CurrentDb.Execute "INSERT INTO tblMarks (SessionID, StudentID, Code, MarkedBy, MarkedAt) " & _
        "SELECT " & Me.cboSession & ", e.StudentID, 'P', '" & Environ("USERNAME") & "', Now() " & _
        "FROM tblEnrolments AS e INNER JOIN tblSessions AS s ON e.ClassID = s.ClassID " & _
        "WHERE s.SessionID = " & Me.cboSession & _
        " AND e.EnrolledOn <= s.SessionDate AND (e.WithdrawnOn Is Null OR e.WithdrawnOn > s.SessionDate)" & _
        " AND e.StudentID Not In (SELECT StudentID FROM tblMarks WHERE SessionID = " & Me.cboSession & ")", _
        dbFailOnError
    Me.sfrmMarks.Requery
End Sub

Attendance Rate Query

qryAttendanceTermToDate:
SELECT st.StudentID, st.Name,
       Sum(IIf(c.CountsAsPresent, 1, 0)) AS Attended,
       Count(m.SessionID) AS Expected,
       Sum(IIf(c.CountsAsPresent, 1, 0)) / Count(m.SessionID) AS Rate,
       Sum(IIf(m.Code = "A", 1, 0)) AS Unexcused
FROM ((tblMarks AS m
       INNER JOIN tblSessions AS s ON m.SessionID = s.SessionID)
       INNER JOIN tblCodes AS c ON m.Code = c.Code)
       INNER JOIN tblStudents AS st ON m.StudentID = st.StudentID
WHERE s.Cancelled = False AND s.SessionDate >= [Forms]![frmMenu]![txtTermStart]
GROUP BY st.StudentID, st.Name;

Because marks only exist for sessions a student was enrolled in, the expected count automatically excludes sessions before enrolment and after withdrawal.

Early Warning Reports

  • Below threshold: rate under your target (for example 90%) term to date or over the last four weeks.

  • Streaks: three or more consecutive absences.

  • Day-of-week pattern: absences concentrated on one weekday.

  • Lateness: repeated lates or high total late minutes.

  • Unmarked registers: sessions with fewer marks than enrolled students.

Parent and Guardian Communication

An Access report produces letters or emails for students below threshold, listing the dates missed. AI can help draft a supportive message from the facts, which staff edit before sending; avoid sending student data to unapproved tools. The email automation pattern is in sending email from Access with Outlook VBA.

Training Providers and Funded Programmes

Apprenticeship and funded training providers often have to evidence attendance for each learner to receive funding. The same structure works, with sessions tied to course modules and an export in the format the funding body requires. Signed paper registers can be scanned and linked to each session as evidence.

Scanning registers into the database uses the approach in scanning documents into Access with AI. Similar session-and-attendance structures power church membership and attendance databases. For report design, see Access reports and forms.

FAQ

School attendance database FAQ

Structure, percentages and early warnings

Yes. Access works well for small schools, tutoring centres, training providers, after-school programmes and academies that need attendance by session, absence reasons, attendance percentages and parent notifications without a full student information system.

Separate students, classes or groups, enrolments (which student is in which class), sessions (each class meeting on a date) and attendance marks (one row per student per session). This structure makes percentages, absence patterns and reports straightforward, unlike a spreadsheet with one column per day.

Sessions attended (present plus late, if late counts as present under your policy) divided by sessions the student was enrolled for and expected to attend. Exclude sessions before enrolment or after withdrawal, and decide how authorised absences are treated for reporting.

Run a weekly query that calculates each student's attendance rate over the last four weeks and term to date, and lists students below your threshold, or with absence streaks. Early follow-up is more effective than end-of-term reports.

It can be, with a back end on secure, access-controlled storage, front ends only on staff computers, regular backups, and minimal personal data. Schools must also follow their applicable student privacy laws and policies for retention and sharing.