Student Tracking Database

[d_k_harris]

Hi,

I have a database that staff enter details into that tracks student
progress at interim
periods throughout the year. This then produces reports that get sent
home and parents
use to see how their child is doing. The database is setup with a
main table tblPupilData
that stores core information about the pupil and the subjects that
they are in. This table
is related to individual subject tables with referential integrity
enforced which stores the
details staff enter on the students progress.

Table structures are as follows:

tblPupilData

PupilID - Autonumber (Primary Key), Name - Text, M/F - Text, DOB -
Date/Time, SEN - Text,
BESD - Yes/No, MLD - Yes/No, ADHD - Yes/No, EAL - Yes/No, LAC - Yes/
No, LAC - Yes/No, G&T - Text,
Band - Text, TutorGroup - Text, CATSVerSAS - Number, QuantSAS -
Number, NonVerSAS - Number,
Mean - Number, VAK - Text, EnglishSAT - Number, MathsSAT - Number,
ScienceSAT - Number, EnglishKS2 - Text,
MathsKS2 - Text, ScienceKS2 - Text, EnglishKS3TL - Text, MathsKS3TL -
Text, ScienceKS3TL - Text,
ReadingAge - Number, WritingAge - Number, EnglishSet - Text, MathsSet
- Text, ScienceSet - Text,
RESet - Text, RETL - Text, ICTSet - Text, ICTTL - Text this carries on
with each SubjectSet followed SubjectTL.

tblSubjectData (One table for each subject).

PupilID - Number, Session1Effort - Number, Session1Attainment -
Number, Session1Behaviour - Number,
Session1Homework - Number, Session1Coursework - Number, Session2Effort
- Number, Session2Attainment - Number,
Session2Behaviour - Number, Session2Homework - Number,
Session2Coursework - Number, Session3Effort - Number,
Session3Attainment - Number, Session3Behaviour - Number,
Session3Homework - Number, Session3Coursework - Number.

This works fine however I am now trying to improve the system by
allowing each subject to store module scores at various times
during the year when a module is completed I am also trying to get a
section on the data entry form that works out an average mark for
modules that have been completed so far. I created fields in the
tblSubjectData to store this information which it does do
but whenever I try to work out an Average it works out the Avaerage
for the whole column and not the row for the students. I also tried
to set the
Module fields up in the tblPupilData but when I do this it does not
allow me to enter any information
in the form. It only allows me to enter data into the table
directly. (I do not want other staff to have access to the table
directly).

Does anyone know how I can solve this, I am a complete beginner with
Access and have limited knowledge so explanations in simple terms
would be ideal.
In the future I would also like to add some kind of ability to conduct
lesson registration
so comments on how I could do this wouldn't go amiss.

All help will be greatly appreciated.

Many Thanks.

 

[Guest]

Firstly to answer the question as regards averaging the module scores per
student you would have to include an unbound text box control on the form
which adds the score for each and divides this by the number of non-Null
module scores (assuming the as yet uncompleted module scores are Nulls rather
than zeros). So the ControlSource for the text box would, for three modules
in total, be something like this:

=(Nz([Module1],0)+Nz([Module2],0)+Nz([Module3],0))/(IIf(IsNull([Module1]),0,1)+IIf(IsNull([Module2]),0,1)+IIf(IsNull([Module3]),0,1))

What this does is add each module score, using the Nz function to return a
zero for the Nulls; this is necessary because if you add a Null to a value
the result will be Null. This is then divided by the number of non-Null
modules, which is calculated by using the IIf function to return a zero for
the Nulls and a 1 for the non-Nulls, and adding those values together.

So, that's how you'd do it with your present design. However, and I hope
you won't think I'm being unduly critical, the fact that its necessary to do
it in such a convoluted way results from the fundamental flaws in the design
of your database.

I'd suggest before going further you try and gain a little more background
knowledge about how a relational database is put together in terms of how
tables and relationships between them model the real world entities with
which the database is concerned. There are plenty of books which will help,
and any decent general primer on MS Access will cover these fundamentals.
Also study the sample Northwind database which comes with Access. While this
is not directly analogous to your database, it does illustrate the underlying
principles in a relatively simple structure. Finally you'll find guidance in
the Help system which is worth reading.

Just to give you a flavour of how a properly structured design might work,
lets take your current question as an example. In a properly structured
design each student's completion of a module would be represented by a
separate row in a table, not by separate columns. The score would be a
column in this table. This table would be related to the student and modules
tables, the latter in turn being related to a subjects table. Averaging
students' scores in the modules taken per subject would then be a simple
matter either of joining the tables in question in a query, grouping by
student and subject and averaging the score column, or in a form the DAvg
function could be used with the current subject and student as its criteria.

Ken Sheridan
Stafford, England

 

[d_k_harris]

Firstly to answer the question as regards averaging the module scores per
student you would have to include an unbound text box control on the form
which adds the score for each and divides this by the number of non-Null
module scores (assuming the as yet uncompleted module scores are Nulls rather
than zeros). So the ControlSource for the text box would, for three modules
in total, be something like this:

=(Nz([Module1],0)+Nz([Module2],0)+Nz([Module3],0))/(IIf(IsNull([Module1]),0­,1)+IIf(IsNull([Module2]),0,1)+IIf(IsNull([Module3]),0,1))

What this does is add each module score, using the Nz function to return a
zero for the Nulls; this is necessary because if you add a Null to a value
the result will be Null. This is then divided by the number of non-Null
modules, which is calculated by using the IIf function to return a zero for
the Nulls and a 1 for the non-Nulls, and adding those values together.

So, that's how you'd do it with your present design. However, and I hope
you won't think I'm being unduly critical, the fact that its necessary todo
it in such a convoluted way results from the fundamental flaws in the design
of your database.

I'd suggest before going further you try and gain a little more background
knowledge about how a relational database is put together in terms of how
tables and relationships between them model the real world entities with
which the database is concerned. There are plenty of books which will help,
and any decent general primer on MS Access will cover these fundamentals.
Also study the sample Northwind database which comes with Access. While this
is not directly analogous to your database, it does illustrate the underlying
principles in a relatively simple structure. Finally you'll find guidance in
the Help system which is worth reading.

Just to give you a flavour of how a properly structured design might work,
lets take your current question as an example. In a properly structured
design each student's completion of a module would be represented by a
separate row in a table, not by separate columns. The score would be a
column in this table. This table would be related to the student and modules
tables, the latter in turn being related to a subjects table. Averaging
students' scores in the modules taken per subject would then be a simple
matter either of joining the tables in question in a query, grouping by
student and subject and averaging the score column, or in a form the DAvg
function could be used with the current subject and student as its criteria.

Ken Sheridan
Stafford, England

Hi,

I have a database that staff enter details into that tracks student
progress at interim
periods throughout the year. This then produces reports that get sent
home and parents
use to see how their child is doing. The database is setup with a
main table tblPupilData
that stores core information about the pupil and the subjects that
they are in. This table
is related to individual subject tables with referential integrity
enforced which stores the
details staff enter on the students progress.

Table structures are as follows:

PupilID - Autonumber (Primary Key), Name - Text, M/F - Text, DOB -
Date/Time, SEN - Text,
BESD - Yes/No, MLD - Yes/No, ADHD - Yes/No, EAL - Yes/No, LAC - Yes/
No, LAC - Yes/No, G&T - Text,
Band - Text, TutorGroup - Text, CATSVerSAS - Number, QuantSAS -
Number, NonVerSAS - Number,
Mean - Number, VAK - Text, EnglishSAT - Number, MathsSAT - Number,
ScienceSAT - Number, EnglishKS2 - Text,
MathsKS2 - Text, ScienceKS2 - Text, EnglishKS3TL - Text, MathsKS3TL -
Text, ScienceKS3TL - Text,
ReadingAge - Number, WritingAge - Number, EnglishSet - Text, MathsSet
- Text, ScienceSet - Text,
RESet - Text, RETL - Text, ICTSet - Text, ICTTL - Text this carries on
with each SubjectSet followed SubjectTL.

tblSubjectData (One table for each subject).

PupilID - Number, Session1Effort - Number, Session1Attainment -
Number, Session1Behaviour - Number,
Session1Homework - Number, Session1Coursework - Number, Session2Effort
- Number, Session2Attainment - Number,
Session2Behaviour - Number, Session2Homework - Number,
Session2Coursework - Number, Session3Effort - Number,
Session3Attainment - Number, Session3Behaviour - Number,
Session3Homework - Number, Session3Coursework - Number.

This works fine however I am now trying to improve the system by
allowing each subject to store module scores at various times
during the year when a module is completed I am also trying to get a
section on the data entry form that works out an average mark for
modules that have been completed so far. I created fields in the
tblSubjectData to store this information which it does do
but whenever I try to work out an Average it works out the Avaerage
for the whole column and not the row for the students. I also tried
to set the
Module fields up in the tblPupilData but when I do this it does not
allow me to enter any information
in the form. It only allows me to enter data into the table
directly. (I do not want other staff to have access to the table
directly).

Does anyone know how I can solve this, I am a complete beginner with
Access and have limited knowledge so explanations in simple terms
would be ideal.
In the future I would also like to add some kind of ability to conduct
lesson registration
so comments on how I could do this wouldn't go amiss.

All help will be greatly appreciated.

Many Thanks.- Hide quoted text -

- Show quoted text -

I have just given what you suggested a try on a duplicate database
that I have on my laptop. At first I could not get it to work because
I had misread what you suggested and tried to set this up in a query.
After making a few mistakes and adjusting some Data Types on fields I
have I have got it to work. Once again thanks for all your help it's
priceless. I'll look at restructuring the tables when I am a bit more
knowledgable about how this all fits together.

Thanks!