Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-15-2019, 12:32 PM   #1
VictoriaHood
Newly Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
VictoriaHood is on a distinguished road
Davg in report group footer

I am trying to get an average as follows:

=Davg("dayspastdue","div30qry","dayspastdue >=0")

This works in giving me the overall average (of overdue tickets) for the entire report. I need to get the same average per group [salesperson] and it doesn't seem to adjust for the group. I'm sure I need to add another criteria, but don't know how. . .

Any help would be greatly appreciated!

VictoriaHood is offline   Reply With Quote
Old 10-15-2019, 12:42 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,378
Thanks: 0
Thanked 747 Times in 732 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Davg in report group footer

in the report, add a GROUP band (for what ever group you need)
set it WITH FOOTER
in the footer , put your AVG( field).
(you don't need DAVG, but you can)
Ranman256 is offline   Reply With Quote
Old 10-15-2019, 12:42 PM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Davg in report group footer

Hi Victoria. Are you saying a simple Avg() function in the group footer doesn't work?

__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 10-15-2019, 12:50 PM   #4
VictoriaHood
Newly Registered User
 
Join Date: Jan 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
VictoriaHood is on a distinguished road
Re: Davg in report group footer

I have the group set with header/footer. It is grouped on [salesperson]. I need to average only tickets that are overdue, i.e., those that show as 0 or greater days. I tried to use Avg(IIf([dayspastdue]>=0,1,0)) but it keeps returning "1" for every salesperson. [dayspastdue] is a calculated field in the underlying query (based on two dates).
VictoriaHood is offline   Reply With Quote
Old 10-15-2019, 06:53 PM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,461 Times in 1,442 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Davg in report group footer

Quote:
Originally Posted by VictoriaHood View Post
I have the group set with header/footer. It is grouped on [salesperson]. I need to average only tickets that are overdue, i.e., those that show as 0 or greater days. I tried to use Avg(IIf([dayspastdue]>=0,1,0)) but it keeps returning "1" for every salesperson. [dayspastdue] is a calculated field in the underlying query (based on two dates).
Hi Victoria. Are you able to post a copy of your db with enough test data to duplicate the problem?
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 10-15-2019, 10:52 PM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,382
Thanks: 115
Thanked 3,113 Times in 2,831 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: Davg in report group footer

Just to clarify, if you used an expression like Date()-[DueDate] AS DaysPastDue in your query, you can't reliably use DaysPastDue in another field in the same query such as your IIf expression.
You must use the full expression again for the derived field e.g.
Code:
SELECT DueDate, Date()-[DueDate] AS DaysPastDue, Iif((Date()-[DueDate])>0,1,0) AS Overdue
FROM TableName;
Alternatively you can use IIf(DaysPastDue>0,1,0) in a second query based on the first
__________________
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 10-16-2019, 09:20 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Davg in report group footer

When calculating overdue, return null rather than 0 when the item is not overdue. The domain functions will ignore nulls but will include zero so:

Avg(3,0,3) = 2
BUT
Avg(3, null, 3) = 3

Then the control for the average would have a simple expression as its ControlSource:

=Avg(OverDueDays)


__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
davg , group footer , report group

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use DSum() in report group footer bulrush17 Reports 1 06-22-2017 07:48 AM
Question How do I subtract Group footer from Report Footer? Joe8915 General 0 05-08-2012 06:45 AM
Referencing Group Footer Calculated Field in Report Group Header rmoreno Reports 7 03-17-2008 08:15 PM
DAvg in Dynamic Crosstab report footer WillEllis Modules & VBA 0 09-15-2005 06:41 AM
Sub Group data to bring in Report footer susanbeno Reports 1 04-29-2001 07:18 AM




All times are GMT -8. The time now is 03:38 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