Lantern Learning Centre

Queries

The questions this database answers, saved under a name. Open one to see its answer, or its design to see how it is built and change it.

Class lists

Every student in every class that is running now or about to start, with the class's day, time, room and teacher, any medical note, photo consent and the family's phone number. Print it as the register before a lesson.

From Enrolments, Students, Classes, Courses, Terms, Teachers, Families. Shows Class, Course, Weekday, Start Time, Room, Teacher, Student, School Year, Medical Note, Photo Consent, Contact, Phone.

Calculated: Class is [Classes].[Code] & ", " & [Terms].[Name]; Teacher is [Teachers].[First Name] & " " & [Teachers].[Last Name]; Student is [Students].[First Name] & " " & [Students].[Last Name].

Only where Status = Enrolled and Days to End >= 0.

Timetable, next 7 days

Every lesson from today to a week ahead, in date and time order, with the room, the teacher and the planned topic. Use it to plan rooms and cover.

From Lessons, Classes, Courses, Teachers. Shows Lesson Date, Weekday, Start Time, Course, Class, Room, Teacher, Minutes, Topic.

Calculated: Teacher is [Teachers].[First Name] & " " & [Teachers].[Last Name].

Only where Days Ahead Between 0 And 7 and Status <> "Cancelled".

Attendance below 85 percent

Students who have missed more than fifteen in a hundred of their lessons in the last 90 days, with at least four lessons in that time, and the family to call.

From Attendance, Students, Lessons, Families. Shows Student, School Year, Contact, Phone, Lessons, Absences, Attendance %, with totals.

Calculated: Student is [Students].[First Name] & " " & [Students].[Last Name]; Absences is IIf([Mark] = "Absent", 1, 0); Attendance % is IIf([Mark] = "Absent", 0, 100).

Only where ID >= 4 and Attendance % < 85 and Days Ago Between 1 And 90.

Fees outstanding by family

Each family that still owes something, with how many invoices are open, the oldest due date and the total left to pay, largest first.

From Fee Invoices, Families. Shows Family, Contact, Phone, Invoices, Oldest Due, Outstanding, with totals.

Calculated: Outstanding is Round([Amount] - Nz([Paid So Far]), 2).

Only where Balance > 0.

Overdue fees

Each invoice past its due date that is not fully paid, with its number, the family's address, the term, how many days late it is and what is left to pay. The fees letters are written from it.

From Fee Invoices, Families, Terms. Shows Family, Contact, Address, City, Postcode, Invoice, Term, Due Date, Amount, Days Overdue, Outstanding.

Calculated: Days Overdue is DateDiff("d", [Due Date], Date()); Outstanding is Round([Amount] - Nz([Paid So Far]), 2).

Only where Days Overdue > 0 and Outstanding > 0.

Classes with free places

Classes running now or next with room for more students: places, how many are enrolled and how full the class is. Use it when a family asks for a place.

From Classes, Courses, Terms, Teachers, Enrolments. Shows Class, Term, Course, Weekday, Start Time, Teacher, Places, Enrolled, Percent Full, with totals.

Calculated: Teacher is [Teachers].[First Name] & " " & [Teachers].[Last Name]; Percent Full is 100 / [Classes].[Capacity].

Only where Percent Full < 100 and Status = Enrolled and Days to End >= 0.

Results by course

For each course, how many test results there were in the last twelve months and the average, highest and lowest marks as percentages.

From Results, Assessments, Classes, Courses. Shows Course, Subject, Results, Average %, Highest %, Lowest %, with totals.

Calculated: Average % is Round([Score] * 100 / [Out Of], 0); Highest % is Round([Score] * 100 / [Out Of], 0); Lowest % is Round([Score] * 100 / [Out Of], 0).

Only where Days Ago Between 0 And 365.

Progress by student

Every test result of the last twelve months, student by student, with the mark, the percentage and the teacher's comment. The progress report prints it.

From Results, Students, Assessments, Classes, Courses. Shows Student, Course, Assessment, Assessment Date, Score, Out Of, Percent, Comment.

Calculated: Student is [Students].[First Name] & " " & [Students].[Last Name] & " (" & [School Year] & ")"; Percent is Round([Score] * 100 / [Out Of], 0).

Only where Days Ago Between 0 And 365.

Enrolments by course and term

How many places were booked on each course in each term and holiday, and the fees charged for them.

From Enrolments, Classes, Courses, Terms. Shows Starts, Term, Course, Enrolments, Fees, with totals.

Calculated: Fees is Round([Fee per Lesson] * [Lessons Billed] * (1 - Nz([Discount])), 2).

Teacher hours last month

For each teacher, the lessons they taught last month, the hours and what they are owed at their hourly rate. Run it at the start of each month for the pay run.

From Lessons, Teachers. Shows Month, Teacher, Hourly Rate, Lessons, Hours, Pay, with totals.

Calculated: Month is Format([Lesson Date], "yyyy-mm"); Teacher is [Teachers].[First Name] & " " & [Teachers].[Last Name]; Hours is [Minutes] / 60; Pay is Round([Minutes] / 60 * [Hourly Rate], 2).

Only where Status = Taught and Months Ago = 1.

Fees received by month

Money received each month from fees, and how many payments made it up.

From Payments. Shows Month, Payments, Received, with totals.

Calculated: Month is Format([Paid On], "yyyy-mm").

Birthdays coming up

Active students with a birthday in about the next month, with the age they turn. Adults have no date of birth here, so only children appear.

From Students. Shows Student, School Year, Birthday, Turns.

Calculated: Student is [First Name] & " " & [Last Name]; Birthday is Format([Date of Birth], "dd") & "/" & Format([Date of Birth], "mm"); Turns is Year(Date()) - Year([Date of Birth]) + IIf(Month([Date of Birth]) * 31 + Day([Date of Birth]) < Month(Date()) * 31 + Day(Date()), 1, 0).

Only where Days Away Between 0 And 30 and Active = Yes.

Background checks due

Teachers whose background check is 33 months old or more, so it can be renewed before three years are up.

From Teachers. Shows Teacher, Subjects, Background Check, Months Old.

Calculated: Teacher is [First Name] & " " & [Last Name]; Months Old is DateDiff("m", [Background Check], Date()).

Only where Months Old >= 33 and Active = Yes.

Forms

Every table has a form of the same name: Start Here, Business, Terms, Families, Students, Teachers, Courses, Classes, Enrolments, Lessons, Attendance, Assessments, Results, Fee Invoices, Payments, Term Reports, Report Lines, Certificates, Pay Statements. A form shows one record at a time and adds new ones. A field that points at another table is a list of that table's records, and the records that point at this one are listed below it.

Reports

Pages to print or save as a PDF, laid out from a query or a table.

Class register

A page for each class running now or next: the students with their school year, medical note, photo consent and the family's contact and phone. Teachers take it into the lesson.

From the query Class lists, a group for each Class: Student, School Year, Medical Note, Photo Consent, Contact, Phone, with the count of Student.

Timetable for the next 7 days

The lessons of the coming week, day by day and in time order, with course, class, room and teacher. Pin it up in the office.

From the query Timetable, next 7 days, a group for each Lesson Date: Start Time, Course, Class, Room, Teacher, Minutes, with the count of Class.

Fees overdue

Overdue invoices family by family, with the address to write to and the total owed. Use it to send reminder letters.

From the query Overdue fees, a group for each Family: Contact, Address, City, Postcode, Invoice, Term, Due Date, Days Overdue, Outstanding, with the total of Outstanding.

Progress report

Each student's test results over the last year with the teacher's comments and their average percentage, to send home at the end of term.

From the query Progress by student, a group for each Student: Course, Assessment, Assessment Date, Score, Out Of, Percent, Comment, with the average of Percent.

Teacher pay

Hours and pay for each teacher for last month, with the totals, for the pay run.

From the query Teacher hours last month: Month, Teacher, Hourly Rate, Lessons, Hours, Pay, with the total of Hours, total of Pay.

Attendance concerns

The students whose attendance has fallen below 85 percent, with the family to call.

From the query Attendance below 85 percent: Student, School Year, Contact, Phone, Lessons, Absences, Attendance %.

Enrolments by course

For each course, the places booked and the fees charged term by term, with totals.

From the query Enrolments by course and term, a group for each Course: Term, Starts, Enrolments, Fees, with the total of Enrolments, total of Fees.

Fees received

Fee income month by month, with the total.

From the query Fees received by month: Month, Payments, Received, with the total of Payments, total of Received.

Start here: the everyday tasks, step by step

The guide to this database printed: each everyday task and its steps, from the Start Here table.

From the table Start Here, a group for each Task: Step, What to do.

Print templates

What the business sends out, one for each record, with its own name, address and payment details at the head from the Business table. Print one from its form, or choose which to print under Print templates, or save them as a PDF. The words in braces are filled in from the record.