Querie that leads to trend chart

[knowshowrosegrows]

I don't have the skills to get where I want to be.

Goal - A table that tells me how the utilization of each of 72 programs
changes daily between a time frame that I designate. I can calculate the
utilization. I can make the form where I put in the BegDate and EndDate.

I need a querie/pivot that would have column heads
Prm_Code
1/1/08
1/2/08
1/3/08
etc.
The larger the time frame I choose, the more date columns I have running
left to right across the page.

There will always be 72 rows because I only have 72 programs.
Each row will tell me the result of my uitilization calculation for that
program for that column date.

I have something similar that gives me similar data but by month. I can't
seem to make one that gives me days. The SQL for that is:

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


Is that clear? Can you help me?

 

[MGFoster]

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

It seems your problem is getting the days in chronological order -
correct? If so you'll have to international date format to get chrono.
order: YYYY-MM-DD

PIVOT Format(CensusDate, "YYYY-MM-DD")

--
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/AwUBSNL+aoechKqOuFEgEQLNewCgpYHzVRQU5mw5YxK/4q6IHStVuGEAoO4/
P4e9Hrd2LRvUY9WjdSn6G7WN
=jpp3
-----END PGP SIGNATURE-----

 

[knowshowrosegrows]

You are brilliant - perfect.
--
Thanks

You all are teaching me so much

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

It seems your problem is getting the days in chronological order -
correct? If so you'll have to international date format to get chrono.
order: YYYY-MM-DD

PIVOT Format(CensusDate, "YYYY-MM-DD")

--
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/AwUBSNL+aoechKqOuFEgEQLNewCgpYHzVRQU5mw5YxK/4q6IHStVuGEAoO4/
P4e9Hrd2LRvUY9WjdSn6G7WN
=jpp3
-----END PGP SIGNATURE-----

I don't have the skills to get where I want to be.

Goal - A table that tells me how the utilization of each of 72 programs
changes daily between a time frame that I designate. I can calculate the
utilization. I can make the form where I put in the BegDate and EndDate.

I need a querie/pivot that would have column heads
Prm_Code
1/1/08
1/2/08
1/3/08
etc.
The larger the time frame I choose, the more date columns I have running
left to right across the page.

There will always be 72 rows because I only have 72 programs.
Each row will tell me the result of my uitilization calculation for that
program for that column date.

I have something similar that gives me similar data but by month. I can't
seem to make one that gives me days. The SQL for that is:

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


Is that clear? Can you help me?