PIVOT Question


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

Advertisements

K

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());
 
K

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


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

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.

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

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
 
K

knowshowrosegrows

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

Advertisements

K

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top