How can I use Access to monitor pupil attendance in lessons?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I believe that it should be possible to use MS Access to monitor the
attendance of my pupils attending each lesson. Can someone give me some help
in getting a suitable (and customizable) calendar into access?
 
How far have you gotten? Do you have any tables created yet? Do you have a
pupil table and a lesson table? Do you have a table that suggests which
pupils are enrolled in which lessons? Are you looking for a calendar to
enter dates or print a formatted report?
 
Dear Duane
Thank you for coming back so quickly. I am a relative "newby" to Access! I
have set up the tables that list the classes I teach and the related subjects
but as you correctly identified, I need a calendar. Iread somewhere that I
could use Outlook's calendar, but that would mean that I would have to
schedule everything in Outlook and I don't want to do that. What I want to do
is to replace the paper register with one on my laptop, and obviously, I want
to do a lot more than just take the register! It may be that this project is
beyond my capabilities, but I want to try. The School I am going to in
September uses a prehistoric system called Bromcom - a precursor to SIMS
which is used at my current school. I will resort to a paper system if I
must, but I wanted to see what I could do.
Regards
Dr Bruce Rae
 
You still haven't told us much. I can't figure out if "Are you looking for
a calendar to enter dates or print a formatted report?"
Also, we don't know if you have a table of pupils and if you have only one
lesson/class repeated several times per week or several classes where pupils
attend only once.
 
I have:
3 x Y7 science classes, 2 have 3 x 1h periods/week; 1 has 2 x 1h periods/week;
3 x Y8 science classes each with 2 x 1h periods/week;
1 x Y9 science class - 1 x 1h period/week;
1 x Y13 chemistry class with 1 x 2h period per week.
1 x Y8 maths class with 1 x 1h period/week;
1 x Y9 maths class with 2 x 1h period/week.
I need to link a calendar to each pupil for each period so that I can check
whether they are Present (P) Late (L) or Absent (A). So, when I go into a
class say on period 1 Monday and key in the date - that pulls up the 8bMa4
class list and I get a box adjacent each name into which I can type P, L or
A. These data are then stored for each pupil. I also need to be able to pull
up the data for each individual pupil etc....
Does that say what I am after doing?
Regards
Bruce Rae
 
Each of these classes should be a record in a table of classes. You also
need a table of all students. Then make a table that stores which StudentId
is enrolled in which ClassID. You can then use these tables to append
records to an attendance table that would store one record per date per
student per class.
 
I want to be in a situation where I don't have to think about what I am doing
- basically because there is no time at the start of each lesson to fiddle
around with setting up the system. I want to be able to type in the date and
it brings up the lessons for that date and allI then do is select the class
and start capturing - surely, this must involve a calendar?
Regards
Bruce
 
Do you have a table of classes, a table of students, and a table that lists
which students are enrolled in which classes? Seems that this is the base
information you need if you want to determine which students are supposed to
be in a class.
 
Never mind - it is obvious that we do not talk the same language. Please read
my last post carefully! On Monday I have 5 consecutive classes - Oh! I give
up.
 
You stated in a previous posting "that pulls up the 8bMa4 class list" and I
ask if you have a "table that lists which students are enrolled in which
classes".

Good luck with your project. You might want to try Excel. You can place each
class on a separate worksheet. Place student names down the left and dates
across the top.
 
Never mind - it is obvious that we do not talk the same language. Please read
my last post carefully! On Monday I have 5 consecutive classes - Oh! I give
up.
Click to expand...
You might try something like this. I think that a parameter query could
be made to pull up a roster by just using the current date and time.
The few databases I have made do not use time, so I am unsure whether I
handled that right. Just consider this as a general idea. I omitted a
Faculty table, because I assumed this is just for one person's use.

Option Compare Database
Option Explicit

Sub CreateTables()

Dim cat

Set cat = CurrentProject.AccessConnection
With cat

.Execute _
"CREATE TABLE Students " & _
"(student_nbr INTEGER NOT NULL PRIMARY KEY, " & _
"first_name VARCHAR (25) NOT NULL, " & _
"last_name VARCHAR (25) NOT NULL);"

.Execute _
"CREATE TABLE Classes " & _
"(class_name VARCHAR (20) NOT NULL, " & _
"class_level INTEGER NOT NULL, " & _
"PRIMARY KEY (class_name, class_level));"

.Execute _
"CREATE TABLE Periods " & _
"(period_nbr INTEGER NOT NULL, " & _
"weekday_nbr INTEGER NOT NULL, " & _
"start_time DATETIME NOT NULL, " & _
"end_time DATETIME NOT NULL, " & _
"PRIMARY KEY (period_nbr, Weekday_nbr));"

.Execute _
"CREATE TABLE ClassPeriods " & _
"(class_name VARCHAR (20) NOT NULL, " & _
"class_level INTEGER NOT NULL, " & _
"CONSTRAINT fkClassPeriods_Classes " & _
"FOREIGN KEY (class_name, class_level) " & _
"REFERENCES Classes (class_name, class_level), " & _
"period_nbr INTEGER NOT NULL, " & _
"weekday_nbr INTEGER NOT NULL, " & _
"CONSTRAINT fkClassPeriods_Periods " & _
"FOREIGN KEY (period_nbr, weekday_nbr) " & _
"REFERENCES Periods (period_nbr, weekday_nbr), " & _
"PRIMARY KEY (class_name, class_level, " & _
"period_nbr, weekday_nbr));"

.Execute _
"CREATE TABLE AttendanceCodes " & _
"(attend_code CHAR (1) NOT NULL PRIMARY KEY, " & _
"attend_name VARCHAR (10) NOT NULL);"

.Execute _
"CREATE TABLE ClassPeriodAttendance " & _
"(student_nbr INTEGER NOT NULL " & _
"REFERENCES Students (student_nbr), " & _
"class_name VARCHAR (20) NOT NULL, " & _
"class_level INTEGER NOT NULL, " & _
"period_nbr INTEGER NOT NULL, " & _
"weekday_nbr INTEGER NOT NULL, " & _
"CONSTRAINT Class_Period_Attendance " & _
"FOREIGN KEY (class_name, class_level, " & _
"period_nbr, weekday_nbr) " & _
"REFERENCES ClassPeriods (class_name, class_level, " & _
"period_nbr, weekday_nbr), " & _
"Attend_code CHAR (1) NOT NULL " & _
"REFERENCES AttendanceCodes (attend_code), " & _
"calendar_date DATETIME NOT NULL DEFAULT Now(), " & _
"PRIMARY KEY (student_nbr, class_name, class_level, " & _
"period_nbr, weekday_nbr));"

End With
Set cat = Nothing
End Sub
 
Thanks for this! I have run the code - what with my level of expertise is
quite good going. It ran perfectly and set up all the tables. Most of which I
had already set up but your is tied together very nicely so I will use your
tables and add my additional fields to them.
I really need to get my head around the data input side of things.
Can you give me some explanation of how one goes about using your tables?
Do I need to now set up some Forms to input the data?
I also was not to sure about what you meant by class-level? Does it mean
that if my class group is 13Ch1 (i.e.Year 13) is the level 13? I could not
see the point of this, but then I am a "newbie" to Access.
Weekday number: Does that mean 2 = Monday, 3 = Tuesday.....?
I have a host of questions, if you have the time?
Regards and thanks
 
Back
Top