PIVOT Question

[knowshowrosegrows]

Can someone see why I would only get a result for the month of April in this
following query. The qryUtilization has data from 1/1/08 - 12/31/08?

TRANSFORM Avg([qryUtilization].Utilization) AS Utilization
SELECT [qryUtilization].Prm_Code, Avg([qryUtilization].Utilization) AS
[Average Of Utilization]
FROM [qryUtilization]
WHERE ((([qryUtilization.CensusDate]) Between DateAdd("yyyy",-1,Date()) And
Date()))
GROUP BY [qryUtilization].Prm_Code
ORDER BY PIVOT Format(CensusDate,"yyyy mm") & "(This month - " &
DateDiff("m",[CensusDate],Date()) & ")";

 

[KARL DEWEY]

Try this --
TRANSFORM Avg([qryUtilization].Utilization) AS Utilization
SELECT [qryUtilization].Prm_Code, Avg([qryUtilization].Utilization) AS
[Average Of Utilization]
FROM [qryUtilization]
WHERE ((([qryUtilization.CensusDate]) Between DateAdd("yyyy",-1,Date()) And
Date()))
GROUP BY [qryUtilization].Prm_Code
ORDER BY [qryUtilization].Prm_Code
PIVOT Format(CensusDate,"yyyy mm") & "This month - " &
DateDiff("m",[CensusDate],Date());

 

[knowshowrosegrows]

Thats for your quick reply.

I got the same result. Only April 09.

I changed the qryUtilization to gather data from 1/1/08 to present and I got
the moths from APRIL 08 to present. I need those first 3 months before April!
--
Thanks

You all are teaching me so much

Try this --
TRANSFORM Avg([qryUtilization].Utilization) AS Utilization
SELECT [qryUtilization].Prm_Code, Avg([qryUtilization].Utilization) AS
[Average Of Utilization]
FROM [qryUtilization]
WHERE ((([qryUtilization.CensusDate]) Between DateAdd("yyyy",-1,Date()) And
Date()))
GROUP BY [qryUtilization].Prm_Code
ORDER BY [qryUtilization].Prm_Code
PIVOT Format(CensusDate,"yyyy mm") & "This month - " &
DateDiff("m",[CensusDate],Date());

Can someone see why I would only get a result for the month of April in this
following query. The qryUtilization has data from 1/1/08 - 12/31/08?

TRANSFORM Avg([qryUtilization].Utilization) AS Utilization
SELECT [qryUtilization].Prm_Code, Avg([qryUtilization].Utilization) AS
[Average Of Utilization]
FROM [qryUtilization]
WHERE ((([qryUtilization.CensusDate]) Between DateAdd("yyyy",-1,Date()) And
Date()))
GROUP BY [qryUtilization].Prm_Code
ORDER BY PIVOT Format(CensusDate,"yyyy mm") & "(This month - " &
DateDiff("m",[CensusDate],Date()) & ")";

 

[KARL DEWEY]

Try editing PIVOT to this --
PIVOT Format(CensusDate,"yyyy mm");

Then run query. If it runs ok then try some editing in the design view to
accomplish the desired display.

Thats for your quick reply.

I got the same result. Only April 09.

I changed the qryUtilization to gather data from 1/1/08 to present and I got
the moths from APRIL 08 to present. I need those first 3 months before April!
--
Thanks

You all are teaching me so much

Try this --
TRANSFORM Avg([qryUtilization].Utilization) AS Utilization
SELECT [qryUtilization].Prm_Code, Avg([qryUtilization].Utilization) AS
[Average Of Utilization]
FROM [qryUtilization]
WHERE ((([qryUtilization.CensusDate]) Between DateAdd("yyyy",-1,Date()) And
Date()))
GROUP BY [qryUtilization].Prm_Code
ORDER BY [qryUtilization].Prm_Code
PIVOT Format(CensusDate,"yyyy mm") & "This month - " &
DateDiff("m",[CensusDate],Date());

Can someone see why I would only get a result for the month of April in this
following query. The qryUtilization has data from 1/1/08 - 12/31/08?

TRANSFORM Avg([qryUtilization].Utilization) AS Utilization
SELECT [qryUtilization].Prm_Code, Avg([qryUtilization].Utilization) AS
[Average Of Utilization]
FROM [qryUtilization]
WHERE ((([qryUtilization.CensusDate]) Between DateAdd("yyyy",-1,Date()) And
Date()))
GROUP BY [qryUtilization].Prm_Code
ORDER BY PIVOT Format(CensusDate,"yyyy mm") & "(This month - " &
DateDiff("m",[CensusDate],Date()) & ")";

 

[John Spencer MVP]

Your where clause limits the records to be between today's date one year ago
(April 24, 2008) and today.

Perhaps you want something like this to get all records between January 1 of
the previous year and today's date.

WHERE [qryUtilization].[CensusDate]
Between DateSerial(Year(Date())-1,1,1) And Date()

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[knowshowrosegrows]

Thanks so much for your help.
--
Thanks

You all are teaching me so much

Your where clause limits the records to be between today's date one year ago
(April 24, 2008) and today.

Perhaps you want something like this to get all records between January 1 of
the previous year and today's date.

WHERE [qryUtilization].[CensusDate]
Between DateSerial(Year(Date())-1,1,1) And Date()

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Can someone see why I would only get a result for the month of April in this
following query. The qryUtilization has data from 1/1/08 - 12/31/08?

TRANSFORM Avg([qryUtilization].Utilization) AS Utilization
SELECT [qryUtilization].Prm_Code, Avg([qryUtilization].Utilization) AS
[Average Of Utilization]
FROM [qryUtilization]
WHERE ((([qryUtilization.CensusDate]) Between DateAdd("yyyy",-1,Date()) And
Date()))
GROUP BY [qryUtilization].Prm_Code
ORDER BY PIVOT Format(CensusDate,"yyyy mm") & "(This month - " &
DateDiff("m",[CensusDate],Date()) & ")";

 

[knowshowrosegrows]

You are the best. Thanks for your help.

How would I change your formula if I wanted the query to use all the data I
have - all the way back to 2004?