Counting values in cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

masterkeys - 16 Nov 2005 14:02 GMT

I've had a look through the forums, but can't seem to find out how to d
this:

I'm setting up a digital register for my local school, in access.
Currently I have a linked table, with a student ID, from the schoo
DB.

I also have a table that is storing the register, there is a wee
commencing field, a monday am, monday pm, tuesday am...
this is linked to each student by an studentId field which matches th
ID field in the student database

The absence codes are stored in another table. This has 3 fields:

Absence code, isAbsence, and isAuthorised.

What I need to do is get a count per pupil, of total half day
present(isAbsence = false) .

Total half days absent(isAbsence = true)

and the total half days those absences were unauthorised(isAuthorised
false)

I then need to calculate %ages from total Half days, how many wer
absent, and the %age unauthorised.

I would if possible also like to be able to restrict the date range i
counts over, to between values, set in a seperate table, calle
TermDates, with term start and end dates in it.

Thanks in advance for any hel

--
masterkeys

Reply to this Message

John Vinson - 16 Nov 2005 18:12 GMT

>I've had a look through the forums, but can't seem to find out how to do
>this:
>
>I'm setting up a digital register for my local school, in access.
>Currently I have a linked table, with a student ID, from the school
>DB.

ok...

>I also have a table that is storing the register, there is a week
>commencing field, a monday am, monday pm, tuesday am...
>this is linked to each student by an studentId field which matches the
>ID field in the student database

But this table is INCORRECT. You're storing data - halfday periods -
in fieldnames. A much better structure for the register would be a
"tall thin" table with fields StudentID, AttendanceDate, and Period.

You can use a Crosstab query to flatten this tall-thin table into the
above format, and (with some work) even have a Form with ten columns
of Monday - Friday data to populate it, but you will really benefit
from storing your data correctly.

>The absence codes are stored in another table. This has 3 fields:
>
>Absence code, isAbsence, and isAuthorised.
>
>What I need to do is get a count per pupil, of total half days
>present(isAbsence = false) .

I presume that these codes are what is stored in (say) the Tuesday AM
field in the register?

>Total half days absent(isAbsence = true)
>
[quoted text clipped - 7 lines]
>counts over, to between values, set in a seperate table, called
>TermDates, with term start and end dates in it.

Very difficult with your design... pretty straightforward with the
normalized design.

John W. Vinson[MVP]

Reply to this Message

Chris2 - 16 Nov 2005 19:10 GMT

> I've had a look through the forums, but can't seem to find out how to do
> this:
>
> I'm setting up a digital register for my local school, in access.
> Currently I have a linked table, with a student ID, from the

school

> DB.
>
> I also have a table that is storing the register, there is a week
> commencing field, a monday am, monday pm, tuesday am...
> this is linked to each student by an studentId field which matches

the

> ID field in the student database
>
[quoted text clipped - 8 lines]
>
> and the total half days those absences were

unauthorised(isAuthorised =

> false)
>
> I then need to calculate %ages from total Half days, how many were
> absent, and the %age unauthorised.
>
> I would if possible also like to be able to restrict the date

range it

> counts over, to between values, set in a seperate table, called
> TermDates, with term start and end dates in it.
>
> Thanks in advance for any help

masterkeys,

Please forgive the dates appended to the table names.

Tables:

CREATE TABLE Students_20051116
(StudentsID AUTOINCREMENT
,FName TEXT(12)
,LName TEXT(12)
,CONSTRAINT pk_Students_20051116
PRIMARY KEY (StudentsID)
)

CREATE TABLE Register_20051116
(RegisterID AUTOINCREMENT
,StudentsID INTEGER
,WeekCommencing TEXT(12)
,CONSTRAINT pk_Register_20051116
PRIMARY KEY (RegisterID)
,CONSTRAINT fk_Register_Students_20051116
FOREIGN KEY (StudentsID)
REFERENCES Students_20051116 (StudentsID)
)

CREATE TABLE Absences_20051116
(AbsencesID AUTOINCREMENT
,StudentsID INTEGER
,AbsenceCode TEXT(12)
,isAbsence BIT
,isAuthorized BIT
,CONSTRAINT pk_Absences
PRIMARY KEY (AbsencesID)
,CONSTRAINT fk_Absences_Students_20051116
FOREIGN KEY (StudentsID)
REFERENCES Students_20051116 (StudentsID)
)

Sample Data:

Students_20051116
1, Jay, Smith
2, Dee, Smith
3, Jeff, Smith

Register_20051116
1, 1, monday am
2, 2, monday pm
3, 3, tuesday pm

Absences_20051116
1, 1, ?, -1, -1
2, 1, ?, -1, 0
3, 1, ?, -1, 0
4, 1, ?, -1, 0
5, 2, ?, -1, -1
6, 2, ?, -1, 0
7, 3, ?, -1, -1

SELECT * FROM Students_20051116
SELECT * FROM Register_20051116
SELECT * FROM Absences_20051116

Query:

SELECT S1.LName
,S1.FName
,SUM(IIf(A1.isAbsence = 0, 1, 0)) AS HalfDaysPresent
,SUM(IIf(A1.isAbsence = -1, 1, 0)) AS HalfDaysAbsent
,SUM(IIf(A1.isAbsence = -1 AND A1.isAuthorized = 0, 1, 0))
AS UnauthorizedAbsences
FROM Students_20051116 AS S1
INNER JOIN
Absences_20051116 AS A1
ON S1.StudentsID = A1.StudentsID
GROUP BY S1.LName
,S1.FName

Results:

Smith, Dee, 0, 2, 1
Smith, Jay, 0, 4, 3
Smith, Jeff, 0, 1, 0

My apologies on not locating any days present, but from the
description given, I couldn't determine for certain how that was
being stored.

To handle date restriction, build your TermDates table, join it by
StudentID to the above FROM clause, and use WHERE <absence date>
BETWEEN <start term date> AND <end term date>.

Sincerely,

Chris O.

PS Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.

Reply to this Message