PIVOT Question

  • Thread starter Thread starter knowshowrosegrows
  • Start date Start date
K

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()) & ")";
 
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());
 
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


KARL DEWEY said:
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 said:
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()) & ")";
Click to expand...
Click to expand...
 
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.

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


KARL DEWEY said:
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 said:
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()) & ")";
Click to expand...
Click to expand...
Click to expand...
 
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
 
Thanks so much for your help.
--
Thanks

You all are teaching me so much


John Spencer MVP said:
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()) & ")";
Click to expand...
Click to expand...
 
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?
 
Back
Top