Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-13-2019, 01:56 AM   #1
Access_Help
Newly Registered User
 
Join Date: Feb 2005
Posts: 103
Thanks: 43
Thanked 0 Times in 0 Posts
Access_Help is on a distinguished road
Average of count per row

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?



Thanks in advance



Attached Images
File Type: jpg Capture.JPG (16.4 KB, 70 views)
File Type: jpg Capture1.JPG (21.0 KB, 53 views)

__________________
Not an Access Programmer
But want to be one day....

Last edited by Access_Help; 11-13-2019 at 02:46 AM.
Access_Help is offline   Reply With Quote
Old 11-13-2019, 02:56 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Average of count per row

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.
Attached Files
File Type: zip Database2.zip (29.6 KB, 7 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-13-2019 at 04:09 AM.
isladogs is offline   Reply With Quote
Old 11-13-2019, 10:38 PM   #3
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 607
Thanks: 76
Thanked 66 Times in 66 Posts
vba_php is on a distinguished road
Re: Average of count per row

or, if you wanna do it in just one query...
Attached Files
File Type: zip Database2 - one query.zip (35.9 KB, 4 views)

vba_php is offline   Reply With Quote
Old 11-14-2019, 12:13 AM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Average of count per row

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;
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-14-2019, 05:24 AM   #5
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 607
Thanks: 76
Thanked 66 Times in 66 Posts
vba_php is on a distinguished road
Re: Average of count per row

Quote:
Originally Posted by isladogs View Post
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.
vba_php is offline   Reply With Quote
Old 11-14-2019, 05:32 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,308
Thanks: 115
Thanked 3,095 Times in 2,813 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Average of count per row

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-14-2019, 05:39 AM   #7
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 607
Thanks: 76
Thanked 66 Times in 66 Posts
vba_php is on a distinguished road
Re: Average of count per row

I'll check it out. thanks.


vba_php is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count and Average kitty77 General 2 11-08-2019 06:23 AM
How to count Average by criteria on query? yosiro Reports 1 09-30-2012 04:57 PM
Average of count Karen831 Reports 1 06-14-2007 03:30 PM
Count Records and get Average jeo Forms 5 12-03-2004 09:07 AM
Count and average mugman17 Queries 1 05-28-2002 09:37 AM




All times are GMT -8. The time now is 04:03 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World