Average of count per row (1 Viewer)

Access_Help

Registered User.
Local time
Today, 02:55
Joined
Feb 12, 2005
Messages
136
I have created a summary query which counts each grade, I want to average percentage the count of each grade using the total. How can this be achieved in the query?

:banghead:

Thanks in advance



 

Attachments

  • Capture.JPG
    Capture.JPG
    16.4 KB · Views: 351
  • Capture1.JPG
    Capture1.JPG
    21 KB · Views: 338
Last edited:

isladogs

MVP / VIP
Local time
Today, 09:55
Joined
Jan 14, 2017
Messages
18,186
Here's one approach.
Assuming your existing query is called qryGradeCount and your table is Table1, create a new query , add both of those to the query but don't join them

The new query SQL is
Code:
SELECT qryGradeCount.Grade, qryGradeCount.CountOfGrade, [CountOfGrade]/Count([ID]) AS PercentGrade
FROM Table1, qryGradeCount
GROUP BY qryGradeCount.Grade, qryGradeCount.CountOfGrade
ORDER BY qryGradeCount.Grade;

Format the PercentGrade field as Percent in the property sheet.
Attached is a quick example based on that.
Undoubtedly there are other ways of doing this.
 

Attachments

  • Database2.zip
    29.6 KB · Views: 234
Last edited:

vba_php

Forum Troll
Local time
Today, 04:55
Joined
Oct 6, 2019
Messages
2,884
or, if you wanna do it in just one query...
 

Attachments

  • Database2 - one query.zip
    35.9 KB · Views: 227

isladogs

MVP / VIP
Local time
Today, 09:55
Joined
Jan 14, 2017
Messages
18,186
Yes that works.
However I avoid using domain functions in queries as they can be very slow especially if there are a lot of records.
The one query solution suggested above actually has two domain functions so that will be even slower

The sql suggested by vba_php was
Code:
SELECT DISTINCT Table1.Grade, DCount("Grade", "Table1", "[Grade] = '" & [Grade] & "'") AS CountOfGrade, Format([CountOfGrade] / DCount("Grade", "Table1"), "00.00%") AS PercentGrade
FROM Table1;
 

vba_php

Forum Troll
Local time
Today, 04:55
Joined
Oct 6, 2019
Messages
2,884
Yes that works.
However I avoid using domain functions in queries as they can be very slow especially if there are a lot of records.
I have always heard the experts here on the forum say that dlookup() is very slow, but I was unaware that I they thought the other ones were as well.
 

isladogs

MVP / VIP
Local time
Today, 09:55
Joined
Jan 14, 2017
Messages
18,186
The domain functions are much better than they used to be and have their uses.
However, all cause speed issues in queries as the function(s) have to run in turn for each field/record.

If you want to find out more, you could have a look at these speed tests on my website
Optimise Queries, Check Record Exists
 

Users who are viewing this thread

Top Bottom