Behavior of Avg and Max functions

  • Thread starter Thread starter Razor
  • Start date Start date
R

Razor

Hi,

This is the first time I'm using group functions other
than Group By, First, Sum and Count.

I tried Avg and Max in a crosstab query but they didn't
behave in a way I could have predicted.

So instead I queried the crosstab query and used Avg and
max in the downstream query but with no luck!

Is there a good tutorial on the internet that illustrates
these functions in the context of a crosstab?

Thanks!

Razor
 
Please describe what it is you are trying to do. What does your data
look like, what do you want the results to look like?

--
HTH

Dale Fye



Hi,

This is the first time I'm using group functions other
than Group By, First, Sum and Count.

I tried Avg and Max in a crosstab query but they didn't
behave in a way I could have predicted.

So instead I queried the crosstab query and used Avg and
max in the downstream query but with no luck!

Is there a good tutorial on the internet that illustrates
these functions in the context of a crosstab?

Thanks!

Razor
 
Dale,

I have written a crosstab query, where the Date is the
column heading. So my resultset has 12 columns (Jan
through Dec), with the first 9 populated (since data
exists for Jan through Sep currently).
I need one column that returns a 9 - this is the max month
value of the current data. I tried Max(Month([Date])) in
the crosstab but it didn't work.
AND
I need another column that is the sum of the months
divided by 9 - i.e. Average. I tried using Avg() function
on the sum of the 12 months but I couldn't understand what
calculation this function did.

Thanks!
Razor
 
Give me an example of what you want your report to look like. My
guess is that you are actually going to have to build several queries
and join them together to get what you want.

If you use Avg in the crosstab, it will compute the average values for
whatever field you select, based on the row and column headings.


--
HTH

Dale Fye



Dale,

I have written a crosstab query, where the Date is the
column heading. So my resultset has 12 columns (Jan
through Dec), with the first 9 populated (since data
exists for Jan through Sep currently).
I need one column that returns a 9 - this is the max month
value of the current data. I tried Max(Month([Date])) in
the crosstab but it didn't work.
AND
I need another column that is the sum of the months
divided by 9 - i.e. Average. I tried using Avg() function
on the sum of the 12 months but I couldn't understand what
calculation this function did.

Thanks!
Razor
 
Back
Top