How to figure out percentage of attendance

[jeepjenn]

Hi Everyone,

I am using the "students" database template that comes with Access 2013, and I'm trying to figure out how to do something with the "Student Attendance Count" Query:

1. I want to calculate a percentage of attendance (and I cant figure out the expression builder)

I'm sorry, this is probably really simple for everyone who has a lot of experience with Access...I've watched a bunch of videos and did a lot of reading today, but I haven't found anything that made sense to me.

Jennifer

[June7]

This is easiest done on a report using Sorting & Grouping features with aggregate calcs in group and report footer sections.

Otherwise, need to do several queries and join the queries. Or nested subquery. So do aggregate query to get the total attendance, grouped as appropriate - maybe by account number or yrmo or whatever you are trying to get percentage of. Then join that query to other dataset, maybe an aggregate grouped by student and account number. This will put the total for each account next to each student/account total and now you can do a calculated field for the percentage.

[jeepjenn]

This is easiest done on a report using Sorting & Grouping features with aggregate calcs in group and report footer sections.

Otherwise, need to do several queries and join the queries. Or nested subquery. So do aggregate query to get the total attendance, grouped as appropriate - maybe by account number or yrmo or whatever you are trying to get percentage of. Then join that query to other dataset, maybe an aggregate grouped by student and account number. This will put the total for each account next to each student/account total and now you can do a calculated field for the percentage.

Ok, so I have done it through a report which gives me the information I need to calculate the percentage (the sum of number of days, and the count of the days "Present")....I'm much more familiar with excel and formulas, but these don't really look the same at all.

(Photo Included Below)


Status has three options - "Present", "Absent Excused" and "Absent Unexcused"

I know I want to do this: =([Status = "Present"])/([Number of days]) but I don't think I'm writing it correctly.


Also, will the correct formula eliminate the "enter parameter value" box that comes up when I run the report?

Thank You for your help so far.

Jennifer

[June7]

=Sum(IIf([Status]="Present", [Number of Days], 0)) / Sum([Number Of Days])

[jeepjenn]

=Sum(IIf([Status]="Present", [Number of Days], 0)) / Sum([Number Of Days])

That worked!!!

Thank You SOOOO much June7!!!

I really appreciate your kindness!!!