pupil / student attendance / register

[motbar]

This idea has been sending me nutty for months!

I’m a travelling music teacher. So my many pupils are split into many small groups (of 1, 2 or 3 pupils).

What I want is to open a form with lesson details. I’d select a date and then a group and then the details of the pupils in that group would come up (probably in a subform). I could then enter data for each pupil for each lesson e.g. whether they were absent.

That’s it!

My current tables are

[pupils]
pupilID (pk) (autonumber)
pupilname (text)

[groups]
groupID(pk) (autonumber)

[lessons]
lessonID (pk) (autonumber)
lessondate (date)
group (text)

[pupilinlesson]
pupilinlessonID (pk) (autonumber)
group (text)
pupilID (number)
absent? (Y/N)
lessonID (number)

This last table is supposed to store the data of each pupil in each lesson.

Relationships

[groups]groupID 1 – M [pupils]group

This is probably quite easy for someone who knows how. Lots of people must need registers of pupils!

Thanks

 

[raskew]

Search on and download Students and Classes.mdb.

Bob

 

[Mike Smart]

This idea has been sending me nutty for months!

I’m a travelling music teacher. So my many pupils are split into many small groups (of 1, 2 or 3 pupils).

What I want is to open a form with lesson details. I’d select a date and then a group and then the details of the pupils in that group would come up (probably in a subform). I could then enter data for each pupil for each lesson e.g. whether they were absent.

That’s it!

My current tables are

[pupils]
pupilID (pk) (autonumber)
pupilname (text)

[groups]
groupID(pk) (autonumber)

[lessons]
lessonID (pk) (autonumber)
lessondate (date)
group (text)

[pupilinlesson]
pupilinlessonID (pk) (autonumber)
group (text)
pupilID (number)
absent? (Y/N)
lessonID (number)

This last table is supposed to store the data of each pupil in each lesson.

Relationships

[groups]groupID 1 – M [pupils]group

This is probably quite easy for someone who knows how. Lots of people must need registers of pupils!

Thanks

Hi Motbar

You've missed the many-to-many relationship. When you're starting with database design simple models only need one-to-many relationships but you can't model your problem without a many-to-many (and that means a link table). The group is also a wildcard as it muddies the water regarding the relationship between a pupil and a lesson.

Start by talking through the relationships

One pupil belongs to only one group but a group contains many pupils (one to many).

One lesson is taken by many pupils (note not groups) and one pupil takes many lessons (the many-to-many).

To model it you need four tables:-

Lesson
Pupil
Group
LessonPupil

The PK/FK's are:-

LessonID (PK lesson table)
PupilID (PK Pupil table)
GroupID (PD Group Table)

LessonID+PupilID is a compound primary key in the LessonPupil table (consisting of the two Foreign Keys LessonID and PupilID)

Foreign keys are
Pupil table contains Foreign Key GroupID

You now have a system that allows pupils to be associated with any group and you know which group takes a lesson via the PupilLesson table. Since you know that all pupils taking a lesson are from the same group.

Hope that makes sense. Best to sketch out a schema and post it here for further audit.