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

raskew via AccessMonster.com

Here is a working example, based on Northwind's
Orders table.
Note that since the Orders table contains dates
between 1994 and 1996 the date parameters have
been modified accordingly.


SELECT
Orders.CustomerID
, Format([OrderDate],"yyyy mm") AS [Yr Mo]
, Avg(Orders.Freight) AS AvgOfFreight
FROM
Orders
WHERE
(((Orders.OrderDate) Between DateAdd("yyyy",-14,Date())
AND
DateAdd("yyyy",-13,Date())))
GROUP BY
Orders.CustomerID
, Format([OrderDate],"yyyy mm");

TRANSFORM Avg(Query21.AvgOfFreight) AS AvgOfAvgOfFreight
SELECT
Query21.CustomerID
FROM
Query21
GROUP BY
Query21.CustomerID
PIVOT Query21.[Yr Mo];

HTH - Bob
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...
 
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()) & ")";
Click to expand...
Click to expand...
 
M

MGFoster

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!
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you want January of the previous year to the present date, the date
formula should be like this:

BETWEEN DateSerial(Year(Date())-1,1,1) And Date()

Also, do a check on your qryUtilization to see that it actually has
those dates:

SELECT MIN(CensusDate) As Earliest, MAX(CensusDate) As Latest
FROM qryUtilization

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfIRu4echKqOuFEgEQIKowCfSld6anx48BvitfgI6amyRIn7NtsAn3wI
JY5xltjuqUH+uylgd0dTsriH
=xVrn
-----END PGP SIGNATURE-----
 
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()) & ")";
Click to expand...
Click to expand...
Click to expand...
 
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()) & ")";
Click to expand...
Click to expand...
 
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?
 
M

MGFoster

knowshowrosegrows said:
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?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just substitute 2004 for Date() in the DateSerial() function:

DateSerial(2004,1,1)

Syntax for DateSerial(Year, Month, Day).

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfZRyIechKqOuFEgEQILJACfelnJfYF0Nk+t70a3c4mS16Grh0UAn0qJ
YhSzNbzCKycRTNXEDGVSKXqK
=U28T
-----END PGP SIGNATURE-----
 

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

Similar Threads

Querie that leads to trend chart 2
Crosstab Riddle 6
Previous Months data 1
Riddle me this you wizards 2
How to convert Null values to zero when create an average query 1
Help with small result type 2
minimize repeating query criteria & generating monthly YTD totals 1
Query Question 0

Top