Count number of fields with value TRUE for each record

[sistemalan]

Hi,

I have a table set up to record attendance. The table has a field for each possible day of attendance named by date i.e. Tuesday 21st July, Wednesday 22nd July. These fields are of type yes/no. Is there a way of finding out the total attendance (i.e. adding up the total number of fields marked TRUE for any record)?

So far I have been copying and pasting the table from Access to Excel and using find and replace to turn all TRUE values to 1 and all FALSE to 0, thus giving me some numbers that I work with to determine attendance levels. It'd be nice to be able to do this in Access, and perhaps incorporate the results into a report.

Thanks in advance to anyone who can put me on right path.

Alan

 

[RuralGuy]

You could probably do it with a RecordSet. Are you saying that there is one- record for every month for each person in attendance?

 

[sistemalan]

One record for each person.
One field for each potential day of attendance. Not the most elegant way to do it I'm sure, but it make it very easy to transfer the data from a register.

 

[gemma-the-husky]

first in access, out of interest, true is actually STORED as -1, not +1 - although we can see why its nice to count the number of 1's (or add 1's)

secondly, your table is possible constructed incorrectly.

you need a two field table, to include these columns - empname, and empdate
or possibly 3 fields empname, empdate, attendance

(note i deliberately avoided using fields called NAME and DATE - these are access system words (reserved) and although legal to use, can cause issues to the unwary.)

you wil end up with a table like this (with gaps for missing days)

EMPNAME EMPDATE
Bill           17/7/09
Bill           18/7/09
Bill           19/7/09
Mike        17/7/09
Mike        19/7/09
John        18/7/09
John        19/7/09

or this, with no gaps, but with indicators for attendance

EMPNAME EMPDATE  ATTENDANCE
Bill           17/7/09      Yes
Bill           18/7/09      Yes
Bill           19/7/09      Yes
Mike        17/7/09       Yes
Mike        18/7/09       No
Mike        19/7/09       Yes
John        17/7/09       No
John        18/7/09       Yes
John        19/7/09       Yes

you could even just store the absences - you Know they were there if they werent absent.

The choice of these is a matter of taste really, but all of these become simple to count, because in each case you can easily write a query that looks something like


"how many attendances did each person have between startdate and enddate"

 

[sistemalan]

yes I think my table is probably badly constructed. Using the structures you suggested, any idea how I could quickly update it with information from the register. At the moment I can do it really quickly, but I jsut can't do much with the data!

 

[Guy Boswell]

If you create a column in a query Day1+Day2+Day3+... it will return a negative number equal to the number of yes answers, true or yes being stored as -1, false or no being stored as 0.

But that means adding new columns to your table and your query every time you add a possible date.

Better to have three tables, one for people, one for possible dates and one for people attending on possible dates. Lookup and limit the values of name and date on the third table to values existing on the other two tables.

Then you just need to count the number of records matching the persons name on the linking third table.

 

[gemma-the-husky]

you have it the other way?

rows of students/employees - and columns of dates

so you are adding an extra date each day?

-----------