average formula- HELP!!

[HaChIrish]

Anyone know how to calculate an average by choosing a number in a field coresponding to the month of the year? I have an average for year-to-date, but as soon as I add in a month, it'll list all averages for each month, and not the year-to-date. HELP!! This is the only thing holding me up on this project!

 

[colmtourque]

My best answer is it does not sound possible if you throw the month in. If you want to list months averages and years you would probably be best doing that on the report.

 

[Jon K]

> Anyone know how to calculate an average by choosing a number in a field coresponding to the month of the year? <


We need more info. Please illustrate your question by showing the field names (with some data), and how you want the query result to be like (based on the data shown).

To arrive at the year-to-date average, we must base our calculation on the raw data so that the "Running Sum" and "Running Count" can be calculated.

For instance, we can't get the Jan-Mar average using:-
(AvgOfJan + AvgOfFeb + AvgOfMar)/3

basing on the fact that 3 is corresponding to Mar.


The correct Jan-Mar average should be:-
(Sum of data from Jan-Mar)/(Total number of records in Jan-Mar)

 

[HaChIrish]

Table is called PHONE MONITOR. Field names are: MONTH / COUNSELOR / CONNECT / PROTECT / RESOLVE / CPRID (autonumber). There's an average of 10 monitors done per counselor per month. Connect/protect/resolve are numbers from 1-5 with 5 being the best. Another problem is PROTECT doesn't always get a rating, so count would have to be done per column. Here's a sample of the table:

MONTH COUNSELOR C P R CPRID
January Mike 4 3 2 1
January Mike 3 2 4 2
January Mike 2 2 3

How do I get year-to-date in your above example, from Jan-Mar?

 

[raskew]

Built a table per your example (with the exception of table name).
Try this query against your data, changing table name as necessary
enclosing it with brackets [ ] if there are spaces in the table name.
Format the query fields (C, P and R) as Fixed with 2 decimal places.

Think it will resolve your month issue, but may not return the data
precisely as you envision (I'm confused with the desired output.)
Anyway, it's hopefully a start:

PARAMETERS [enter month as integer] Text;
SELECT tblPhoneMonitor.Counselor, Avg(tblPhoneMonitor.C) AS AvgOfC, Avg(tblPhoneMonitor.P) AS AvgOfP, Avg(tblPhoneMonitor.R) AS AvgOfR
FROM tblPhoneMonitor
WHERE (((DateValue([Month] & "/02"))<=DateValue([enter month as integer] & "/02")))
GROUP BY tblPhoneMonitor.Counselor;

 

[HaChIrish]

Perfect! That WHERE function is what I needed. It works like a charm. Thanks! But now I come to another tie-up: When a counselor is hired they are part of a training unit. When they complete certification (6 months) they switch to a regular unit. I'm using 2 tables; the above table (called PHONE MONITOR) with a column called GRADE and another table called COUNSELOR with column UNIT. I used an iif statement:

Trainee Unit: IIf([Grade]="M","M",[Unit])

Where M is the training unit, and now I get 2 values for the months when the counselor passes certification; M and UNIT. The stats need to be kept separate because upon certification their stats start fresh. How do I fix it so only the UNIT value shows?

 

[raskew]

In the UNIT column, try <>"M" as the criteria.

 

[HaChIrish]

THANK YOU THANK YOU THANK YOU!!!

 

[raskew]

Ma'm, Sir, Godess (don't know how to address you):

Couple of things you might want to consider with your application:

1. Re my previous suggestion--I messed up. Change this line:
WHERE (((DateValue([Month] & "/02"))<=DateValue([enter month as integer] & "/02")))
GROUP BY tblPhoneMonitor.Counselor;
To read..
WHERE (((DateValue([Month] & "/2002"))<=DateValue([enter month as integer] & "/2002")))
GROUP BY tblPhoneMonitor.Counselor;

Reason: The DateValue() function attempts to work with the information fed to it. If there's an ambiguous situation (eg, does '01/02' mean January 2002-or-the 2nd day of January), it defaults to using the current system year. So, when we enter datevalue("01/02") the actual return is 01/02/02 when what we really want is 01/01/02. Obviously hasn't created a problem yet, but to be on the safe-side, make the change.

2. If there's even the most remote possibility that your query could ever span more than one year, get rid of the MONTH field and replace it with strMoYr (with will contain string data that looks like "01/2002"). Do it now while you have the opportunity or you'll be busy designing work-arounds when a time frame unexpectedly goes from Oct this year to Feb next year. The computer has no way to sort this out unless the month is accompanied by a year.

Bob