Best structure for a finite-day course's attendance

[serenechaos]

Hi,
I'm setting up an attendance register (or more accurately an absence register) for trainees who take training courses over a set number of days (30 / 50 /100). Each day has the same five sessions. Trainees are divided into training groups, and all training groups run on the same schedule.

To link the trainees to groups I put a GroupID field in the Trainees table, then manually typed in the group for each trainee - manually entering was not too bad here as we only have about 150 trainees. I've also fiddled a little bit with setting up relationships which resulted in being able to open each group and see its trainees

Now I've set up aTrainingDays table, with a CorrespDate field and a TrainingDayNumber field - also not too bad, but I generated and pasted from excel. After this table is where things go off the rails for me.... to have unique records where I can record absence for every trainee every lesson of every day in the training course, (old, limited excel based thinking!) with my approach I'd have a table like this:



ID PeriodName SeqDayID AbsentYN
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
6 6 1
9 1 2
10 2 2
11 3 2
12 4 2
13 5 2
14 6 2

and that's just for one trainee over two days! 150 trainees * 100 days * 6 sessions = madness! What's a better way to build or generate the structure/values needed for this kind of database? I'm very new to Access so there are probably many features that I'm not yet aware of. I've tried to jump ahead and create the user form (trainers taking attendance), but realised that the underlying structure needs to be solid first!

[isladogs]

The correct approach for normalised data is one item of data per record.
That is one mark for one student for one session

So for a school of 1500 students with 5 lessons per day and 190 school days in an academic year, there are 1500*5*190=1425000 records.

[serenechaos]

Right. So what options/mechanisms are available to set up or generate the necessary structure - other than manual entry /importing via excel? some sort of append query?

[isladogs]

The database should already contain the IDs of the trainees, course, dates etc
If not, you need to import them into Access tables
Then create queries for populating the attendance form - either SELECT or CROSSTAB depending on how you set this up

As previously mentioned, this is certainly possible to do BUT it isn't really a beginners project.