Count values in same row

[Drh]

Hi,

I have an unnormalised table that i cant alter the schema of. Its a sort of attendance register scenario for pupils attending classes:

Columns:

Student_id, class_id, week1, week2, week3, ..., week11

Ideally i'd like a calculated column to count for how many weeks the pupil has been absent (absence is denoted by a value "u" under each week, as appropriate).

This would be much easier if the database was normalised, but my union query and vba efforts cause cause the db to slow to a crawl.

Any thoughts much appreciated!

[Imdabaum]

Are there 52 week fields?

I would try to iterate through the weeks with VBA this might be sufficient

function getTotalAbsence(studentID as Integer) as Integer
Dim rst = Recordset
set rst = Currentdb.openrecordset("Select * from table where student_id = " & studentid, dbOpenDynaset)

For i=1 to 52
count = IIF(rst.Fields("week" & i)="u", count + 1, count)
Next i
getTotalAbsence = count
End function

for one student passing the student_id as a parameter, but if you are trying to generate that for 20-50+ students, it might take a while. Hence why normalization is ideal. (Not your fault... we all get stuck with tables once in a while that we just aren't authorized to fix)

[Drh]

Thanks for your input Imdabaum - your code actually executes faster than mine did, so I'll go with that I think.

There are only 11 weeks but the student record set is pretty large.

I think the reason they structured the database in this way was to make data entry into the table easier (as multiple weeks are logged at the same time). It just makes data processing a chore!

Many thanks again