Need Help Counting Attendance

[jcharbula]

I need an Access SQL query to count the number of times someone attended a class.....
So I have a table that has 10 week fields, (ex:week1,week2,week3,etc)
If a student attended during that week, there is a date in that field.
For each student, I need to know how many weeks they attended a class. If they did not attend a class any particular week that field will be null(blank)


So for example, Student ID 1 - if they attended 7 out of the 10 weeks, I would like to display the student ID and the count of weeks they attended
Please help me devise this query?

[rpeare]

You're using a non normalized structure, in other words each week is accounted for in a separate column. This is going to make your queries cumbersome and I'd suggest you look at normalizing your database (there are tons of articles on the web about normalization).

For your purposes your formula would be something like:

WeeksAttended: iif(isnull(week1), 0,1) + iif(isnull(week2), 0,1) + ... for each week column in your table.

[ranman256]

Yes, put the date as the field:

student, date,attend

[jcharbula]

Thank You!