Calculate % with multiple conditions with one query

[pickslides]

Hi all,

I have attached a DB with what I'm trying to achieve. Its a table, I want to find the % using just the one MS Access query. Not sure it can be done?

The trick is only summing the data for particular AbsentReason when equals only "Late" or "None Given". Also Lesson 1 is only 10 mins and the rest are 50 mins.

OTUPUT would be

John = ( (Count of Lessons * 10 or 50) - (Sum of Absent Mins only "Late" or "None Given") ) / (Count of Lessons * 10 or 50) = (520 - 150) / 520 = 71%


Mary = ( (Count of Lessons * 10 or 50) - (Sum of Absent Mins only "Late" or "None Given") ) / (Count of Lessons * 10 or 50) = (520 - 7) / 520 = 99%



Thanks Q

[Bob Fitz]

Why must it be done with only one query?
I don't understand this:

Also Lesson 1 is only 10 mins and the rest are 50 mins.

BTW: "Date" and "Name" are Reserved words and should not be used to name any object. Also a poor choice because they tell you little of what the data is about. "FirstName", for example, would be better.

[mike60smart]

Your calculations bear no resemblance to the data in the table.

Mary has a record for Late but the duration is only 5 minutes
Mary has a record for Not Given but the duration is only 2 minutes ??

So should the criteria be <=10 ??

[mike60smart]

How are you arriving at the 520 value ??