count and average

  • Thread starter Thread starter Troy Scott
  • Start date Start date
T

Troy Scott

Hello, I have a table like this:

Driver | Atlanta | Nashville | New York |
Joe 32 18
Tim 11 30
Mark 2 40 32


Bottom line, I want to know the average, but notice some have blanks. Would
this make my query not accurate?

thanks,

Troy
 
Troy, Depends on what you are trying to average, and what these values
represent

None of the aggregate functions consider NULL values when doing their
computations. So, if, for example these numbers represent the number of
trips each driver has made to Atlanta, Nashville, and NewYork, then the
following query should return 17: which is the average number of trips by
the two drivers that have actually driven to Atlanta.

SELECT Avg(Atlanta) as AvgOfAtlanta FROM yourTable

If you want to know the average across all drivers, you might try the
following. It should give you 34/3 = 11 1/3

SELECT Avg(NZ([Atlanta], 0) as AvgOfAtlanta_AllDrivers FROM yourTable

Use of the NZ function will return a zero and force Access to include that
value in the average.

HTH

Dale
 
excuse me, I wasn't quiet clear. I want to count and average the rows, like
add 32 and 18 for Joe, which will be 25.

Troy
 
Troy,

Unfortunately, your table is not normalized, which makes this type of
computation more difficult. If your table were normalized, you would have
fields (Driver, Destination, Quantity) or something like that. Then you
could just do an average of the Quantity, grouped by driver. The way you
have it, you really need a little function that you could call, something
like (untested):

Public Function Average(varValues() as Paramarray) as Variant

Dim intLoop as integer
Dim dblCount as double
Dim dblValSum as double

intCount = 0
dblValSum = 0

For intLoop = LBOUND(varValues) to UBOUND(varValues)
if not isnull(varValues(intLoop)) THEN
dblCount = dblCount + 1
dblValSum = dblValSum + varValues(intLoop)
endif
next intLoop

if dblCount = 0 then
Average = "NA"
Else
Average = dblValSum / dblCount
endif

End function

Then you would call this function in your query, something like:

SELECT [Driver], Average([Atlanta], [Nashville], [New York]) as DriverAvg
FROM yourTable

HTH
Dale
 
don't suppose I could email you my database and let you take a look at it?

Dale Fye said:
Troy,

Unfortunately, your table is not normalized, which makes this type of
computation more difficult. If your table were normalized, you would have
fields (Driver, Destination, Quantity) or something like that. Then you
could just do an average of the Quantity, grouped by driver. The way you
have it, you really need a little function that you could call, something
like (untested):

Public Function Average(varValues() as Paramarray) as Variant

Dim intLoop as integer
Dim dblCount as double
Dim dblValSum as double

intCount = 0
dblValSum = 0

For intLoop = LBOUND(varValues) to UBOUND(varValues)
if not isnull(varValues(intLoop)) THEN
dblCount = dblCount + 1
dblValSum = dblValSum + varValues(intLoop)
endif
next intLoop

if dblCount = 0 then
Average = "NA"
Else
Average = dblValSum / dblCount
endif

End function

Then you would call this function in your query, something like:

SELECT [Driver], Average([Atlanta], [Nashville], [New York]) as DriverAvg
FROM yourTable

HTH
Dale

Troy Scott mindspring.com> said:
excuse me, I wasn't quiet clear. I want to count and average the rows, like
add 32 and 18 for Joe, which will be 25.

Troy
Click to expand...
Click to expand...
 
Back
Top