Average question?

[Randy]

I have set up and unbound text field Text17 to Count the number of
Status fields in a report. It works fine. I grouped by Date1 field.
That works fine. I now need an average of the count by Date. I have
grouped by Date1 Field.

I tried to set up an unbound field to Sum([Text17]) but that does not
work.

How can I get an average of the Text17 unbound field?

 

[Ed Warren]

You have a query that gives you the count for a day (qry1).
one day one number --> the 'average count for that day would be equal to
the count'.
if you want to get the average count over some number of days then that is a
'horse of a different color'
you only need to build yet another query based on the one above (qry1) with
the date groupby as where (between date1 and date2) and count groupby
average.

or you can use the VBA code for average.avg(expression).

In both cases you may need to consider how to handle null values.

Ed Warren.

 

[Randy]

I am using a text field to count the number of appointments in a day. I
have that grouped by date. That works fine. but I need to know how to
average the numbers in the text field. So what I need is a field that
will average those numbers in that field over a date range. I know how
to get the date range. I just don't know the formula for getting the
average of the text field that contains the total daily count. I don't
understand your suggestion about a date1 and date2.

 

[Ed Warren]

Forget about the date1 and date2.

I'm not sure if you are working with a form/report/ or query.

But to do what you want to do you have to first get a set of data that has
the count calculated as a field in the data view (see query 2 below)

Then you can calculate the average count.

So first you want a query that returns the count for each day.
Then you want a query that averages that count.

otherwise you will be getting an average for the original field values.

or

You will have to build a vba function to return the average of the count

Here is an example from one of my databases: To start I have a query that
returns the date and a traffic count for a road (Query1)
---------------------
Query1 Date TrafficCount
1/1/1994 269
1/1/1997 204
8/7/1997 51
1/1/1998 55
11/5/1999 151
11/5/1999 65
11/5/1999 60
11/15/1999 83
11/15/1999 34
11/15/1999 50
11/15/1999 87
5/7/2001 39
5/7/2001 66
5/22/2001 121
5/22/2001 128
5/22/2001 102
5/22/2001 102
5/22/2001 64
5/29/2001 23
5/29/2001 51

--------------------
SELECT Query1.Date, Count(Query1.TrafficCount) AS CountOfTrafficCount
FROM Query1
GROUP BY Query1.Date;

Query2 Date CountOfTrafficCount
1/1/1994 1
1/1/1997 1
8/7/1997 1
1/1/1998 1
11/5/1999 3
11/15/1999 4
5/7/2001 2
5/22/2001 5
5/29/2001 2


--------------------------------------------------------------

SELECT Avg(Query2.CountOfTrafficCount) AS AvgOfCountOfTrafficCount
FROM Query2;

Query3 AvgOfCountOfTrafficCount
2.22222222222222


Which is what I think you are asking for.

If you want the average of all the rows.
--------------------------------------------------------
SELECT Avg(Query1.TrafficCount) AS AvgOfTrafficCount
FROM Query1;

Query3 AvgOfTrafficCount
90.25


----------------------------------------

but if you want the average traffic count for each date then you would have:

SELECT Query1.Date, Avg(Query1.TrafficCount) AS AvgOfTrafficCount
FROM Query1
GROUP BY Query1.Date;

Query3 Date AvgOfTrafficCount
1/1/1994 269
1/1/1997 204
8/7/1997 51
1/1/1998 55
11/5/1999 92
11/15/1999 63.5
5/7/2001 52.5
5/22/2001 103.4
5/29/2001 37




Ed Warren.