(newbie) counting and tallying text in a table

[Guest_imported]

I created an attendance database at work, to learn access and we ended up using it.
I have daily records for each student and it consists of date, class, student id and one field for each hour with either P (present), L (late), E (excused), or A (absent).
How do I tally the number of Ps or As or Es or Ls each day and tally them over 6 months. In excel I can use nested CountIf statements but I don't understand how to do that in an access table.
Help, me from Toronto, please!
Thanks

 

[tomschafer]

Check out the group-by function.

If you need more help let me know.

 

[MichaelRed]

Hmmmmmmmmmmmmmmm,


I have looked at a few approaches. And found some issues with each. In general, the structure appears to be an impediment. Including both the class and the student Id is difficult, as it becomes quite unclear which is really in use. Including each of the hours ('Class period/?) also points to confusion in the design. I think that a more normalized structure would make the soloution MUCH easier.

For me, the structure would (probably) be:

[tblAttend]
[dtAtt][tab]'Date of School Classes
[StId][tab]'Student Id
[Class][tab]'Class or "Hour"
[AtStat][tab]'Attendance Status

The development of your statistics from this structure would be quite easy, provide you with an easier structure to understand and (therefore) easier to maintain, and normallize the table.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Guest_imported]

My 'gut' tells me that you are right michael and I think I';ll have to do this (so I can stop exporting into excel). This leads me to another question then. Given there are two hundred students and 7 hours a day, I would be adding 1400 records perday. We do 6 month reports so there would be a few less than 200,000 records. Is that sensible? Is there a limit to the number of records? Maybe I could archive results once a month.
Thanks for our input so far.

 

[MichaelRed]

200K records is quite reasonable for most apps in a Ms. Access db. If you have a really heavy multiple user rate, there may be an occassional slow-down. I would not anticipate a simple attendance db to have such a problem - unless you are having each instructor do the input at more or less the same time. 200 students in seven periods / classes sounds like a small school, so I would expect about 30 or so instructors, so even if you have them all doing their data entry in parallel, the process can handle this, although I would suggest that you "split" the db and install the 'front end' seperatly on each computer and to be SURE you fully implement the security module. Soneone in the student body WILL be able to get into the system if you don't - Guess who would get the perfect attendance star?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over