How do I count YES in a field and then get percentage of total yes

[JShoppell]

field H&P done w/in 24 hours yes or no I have 5 total records audited 4 have yes answers and 1 does not. in excel I count if range =yes and then I divide my number of yes by the total numbers of records to get my percentage how do I build this in my query in access?

[NeoPa]

If you make it a GROUP BY query (Press the Sigma button when designing the query) then you can use the aggregation function Count() to determine how many records are returned (Total number) and the aggregate function Sum() with an IIf() to select the Yeses. The percentage can be worked out by dividing one by the other.

[hvsummer]

did you try to use this way ?
set a field to count (sum actually, but not sum in group query)
let insert this into expression
"CountABC: iif(H&PDoneW/in24H = true, 1, 0)"
this field will return 1 and 0 for you to sum, sum of the will be exactly number of yes, but you don't need group query to know how much.

you can open that query in datasheet view then go to Home -> Records sub ribbon -> Totals -> click on that totals
then choose sum in the CountABC field (still in datasheet view).

[NeoPa]

HVSummer:
but you don't need group query to know how much.

you can open that query in datasheet view then go to Home -> Records sub ribbon -> Totals -> click on that totals

You really should check what you say before posting it. Otherwise you give out mis-information that can confuse people and waste other experts' time having to contradict you publicly, which is usually to be avoided, but is necessary when you post stuff that's factually wrong. It wasn't necessary when you did it to me as my post was actually 100% correct.

In this case, you clearly do need a GROUP BY query, just as I said in my earlier post. When you click on the Totals button on the ribbon that's exactly what you're doing - converting it into a GROUP BY query.

Again, please be very careful before posting, that you know what you're talking about to save time correcting you, and to save what must be humiliation when you're shown to be posting wrong information.

[hvsummer]

I think something missunderstand here, the total in ribbon doesnt convert select query into group by query... I said the total in datasheet view, not design view, plz read carefully.

you can see it in this link, the #2 function
http://www.fmsinc.com/microsoftacces...-features.html.

[NeoPa]

I apologise.

It turns out that you were/are right and I was mistaken.

I will happily remove these posts if you agree to that (Just the discussion part).

[hvsummer]

no, Mate, we help eachother, and sometime our communication like this ---"critical error -- VBA on fire because JET engine can't transfer message" lol.
btw I'm happy to wait Jshoppel reply his situation after read our suggestions.

[NeoPa]

HVSummer:
"critical error -- VBA on fire because JET engine can't transfer message"

I like that :-D

TBF, on this occasion, though I struggled to understand what you were saying, I was guilty of hypocrisy (& I can't begin to express how unhappy I am about that.) by not checking more carefully before making my comments public. While I regret that, I appreciate your attitude. Good on yer.

PS. we can't always rely on the OP ever coming back to comment, as many never do. I'm happy that your post was good advice regardless of whether they do or not.