Ave function

[Linda RQ]

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

 

[John Spencer]

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

 

[Linda \(RQ\)]

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

Linda

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,

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