Average of count per row

[Access_Help]

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

 

[isladogs]

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

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.

 

[vba_php]

or, if you wanna do it in just one query...

 

[isladogs]

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

SELECT DISTINCT Table1.Grade, DCount("Grade", "Table1", "[Grade] = '" & [Grade] & "'") AS CountOfGrade, Format([CountOfGrade] / DCount("Grade", "Table1"), "00.00%") AS PercentGrade
FROM Table1;

 

[vba_php]

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]

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

 

[vba_php]

I'll check it out. thanks.