Ave function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Linda RQ - 22 Apr 2008 20:37 GMT

Hi Everyone,

Using Access 2003

I have Query that has Current Date, PtsOnThpy (num), PtsOnOdrsts (num),
Appropriate Thpy Percent which is an expression "PtsOnOdrsts/PtsOnThpy"

What I need from this query is

Average percent by quarter
Average percent by quarter so far

I created another query using this first one as a base and have only one
field in there which is this expression

AvePrtclPcnt: Avg([AppropriateThpyPercent])

It appears to be calculating correctly but I don't have a ton of records in
there.

1.) Is it ok to use the Avg function to get an average of the total
percents?
2.) How do I get it by quarter with January as the first month of the first
quarter?
3.) How can I get a running tally of percent so far for the current quarter?

I do plan on creating a report by quarter from here but I also want to show
the running tally for the quarter on a form control.

Thanks,
Linda

Reply to this Message

John Spencer - 23 Apr 2008 12:33 GMT

1) Yes, that should work.

2) You need to group by quarter in a totals query.

Use an expression like the following in place of the Current Date field:
Format([Current Date],"yyyy_q")
You can still filter by Current Date to limit the records returned. Use WHERE
instead of Group By (using design view) and apply the criteria against Current
Date.

3) This seems to be the same as above. Unless you mean that you want to limit
the above to only quarters that are completed. In that case filter by the
Current Date field to limit the records to entire quarters.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Hi Everyone,
>
[quoted text clipped - 27 lines]
> Thanks,
> Linda

Reply to this Message

Linda (RQ) - 24 Apr 2008 03:22 GMT

Thanks!!! I think I am getting the hang of this...a little <g>

Linda

> 1) Yes, that should work.
>
[quoted text clipped - 47 lines]
>> Thanks,
>> Linda

Reply to this Message