Avg and Max in crosstab - Dale (repost)

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

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
 
You should be able to use a Row Heading of
Month([Date])
Max
Row Heading

Not sure what you want in the other column. It might help if you showed us
the SQL of your crosstab.

--
Duane Hookom
MS Access MVP


Razor said:
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

-----Original Message-----
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


.
Click to expand...
Click to expand...
 
Razor
Messy but works avoiding crosstab and easy to translate to tSQL or
passthrough.

SELECT YourTable.CustomerID, CCur(Sum([OrderValue]/(SELECT
Month(Max([OrderDate])) FROM YourTable))) AS AvSales,
Sum(IIf(Month([OrderDate])=1,[OrderValue],Null)) AS 1,
Sum(IIf(Month([OrderDate])=3,[OrderValue],Null)) AS 3,
Sum(IIf(Month([OrderDate])=4,[OrderValue],Null)) AS 4,
Sum(IIf(Month([OrderDate])=5,[OrderValue],Null)) AS 5,
Sum(IIf(Month([OrderDate])=6,[OrderValue],Null)) AS 6,
Sum(IIf(Month([OrderDate])=7,[OrderValue],Null)) AS 7,
Sum(IIf(Month([OrderDate])=8,[OrderValue],Null)) AS 8,
Sum(IIf(Month([OrderDate])=9,[OrderValue],Null)) AS 9,
Sum(IIf(Month([OrderDate])=10,[OrderValue],Null)) AS 10,
Sum(IIf(Month([OrderDate])=11,[OrderValue],Null)) AS 11,
Sum(IIf(Month([OrderDate])=12,[OrderValue],Null)) AS 12

FROM YourTable

WHERE (((Year([OrderDate]))=Year((SELECT MAX(OrderDate) FROM YourTable))))

GROUP BY YourTable.CustomerID;



copy and paste into Word, search and replace your field/table names and copy
back into Access.



Peter





Razor said:
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

-----Original Message-----
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


.
Click to expand...
Click to expand...
 
missed feb
SELECT YourTable.CustomerID, CCur(Sum([OrderValue]/(SELECT
Month(Max([OrderDate])) FROM YourTable))) AS AvSales,
Sum(IIf(Month([OrderDate])=1,[OrderValue],Null)) AS 1,
Sum(IIf(Month([OrderDate])=2,[OrderValue],Null)) AS 2,
Sum(IIf(Month([OrderDate])=3,[OrderValue],Null)) AS 3,
Sum(IIf(Month([OrderDate])=4,[OrderValue],Null)) AS 4,
Sum(IIf(Month([OrderDate])=5,[OrderValue],Null)) AS 5,
Sum(IIf(Month([OrderDate])=6,[OrderValue],Null)) AS 6,
Sum(IIf(Month([OrderDate])=7,[OrderValue],Null)) AS 7,
Sum(IIf(Month([OrderDate])=8,[OrderValue],Null)) AS 8,
Sum(IIf(Month([OrderDate])=9,[OrderValue],Null)) AS 9,
Sum(IIf(Month([OrderDate])=10,[OrderValue],Null)) AS 10,
Sum(IIf(Month([OrderDate])=11,[OrderValue],Null)) AS 11,
Sum(IIf(Month([OrderDate])=12,[OrderValue],Null)) AS 12

FROM YourTable

WHERE (((Year([OrderDate]))=Year((SELECT MAX(OrderDate) FROM YourTable))))

GROUP BY YourTable.CustomerID;

Peter



Peter Surcouf said:
Razor
Messy but works avoiding crosstab and easy to translate to tSQL or
passthrough.

SELECT YourTable.CustomerID, CCur(Sum([OrderValue]/(SELECT
Month(Max([OrderDate])) FROM YourTable))) AS AvSales,
Sum(IIf(Month([OrderDate])=1,[OrderValue],Null)) AS 1,
Sum(IIf(Month([OrderDate])=3,[OrderValue],Null)) AS 3,
Sum(IIf(Month([OrderDate])=4,[OrderValue],Null)) AS 4,
Sum(IIf(Month([OrderDate])=5,[OrderValue],Null)) AS 5,
Sum(IIf(Month([OrderDate])=6,[OrderValue],Null)) AS 6,
Sum(IIf(Month([OrderDate])=7,[OrderValue],Null)) AS 7,
Sum(IIf(Month([OrderDate])=8,[OrderValue],Null)) AS 8,
Sum(IIf(Month([OrderDate])=9,[OrderValue],Null)) AS 9,
Sum(IIf(Month([OrderDate])=10,[OrderValue],Null)) AS 10,
Sum(IIf(Month([OrderDate])=11,[OrderValue],Null)) AS 11,
Sum(IIf(Month([OrderDate])=12,[OrderValue],Null)) AS 12

FROM YourTable

WHERE (((Year([OrderDate]))=Year((SELECT MAX(OrderDate) FROM YourTable))))

GROUP BY YourTable.CustomerID;



copy and paste into Word, search and replace your field/table names and copy
back into Access.



Peter





Razor said:
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

-----Original Message-----
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


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