[AccessD] CrossTab Query - 12 Month "sliding scale"

Thanks Don:

Just found another workaround - I added a field calculating the interval to
my query ("qryMonthlyProduction")
that accepts the user parameters. I can then use the interval value in the
crosstab ... And it works! 

Thanks again!


Larry Mrazek
LCM Research, Inc.
www.lcm-res.com
lmrazek at lcm-res.com
ph. 314-432-5886
fx. 314-432-3304

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mcgillivray, Don
[ITS]
Sent: Wednesday, March 23, 2005 4:56 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] CrossTab Query - 12 Month "sliding scale" 

Larry,

You need to zero pad your month numbers so they'll sort properly when
appended to the "Month" text.  Try wrapping the portions of your statement
that return that number in the "Format" function.  Something
like: Format(datediff(yada yada), "00")

Hope this helps.

Don

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lawrence Mrazek
Sent: Wednesday, March 23, 2005 2:16 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] CrossTab Query - 12 Month "sliding scale" 


Hi:

I'm currently having a bit of problem creating a Crosstab query for a twelve
month period, with the period starting from a user-selected value. 
Thus, if user selects a date in June 2004, I want the report to display data
from June-2004 to May 2005 ... My current code is below. (I want to have
static headings so I can bind the query to a report).

TRANSFORM Sum(qryMonthlyProduction.SumOfBags) AS SumOfSumOfBags SELECT
qryMonthlyProduction.ProductionFacility,
qryMonthlyProduction.ProductionLine, qryMonthlyProduction.Shift,
Sum(qryMonthlyProduction.SumOfBags) AS [Total Of SumOfBags] FROM
qryMonthlyProduction GROUP BY qryMonthlyProduction.ProductionFacility,
qryMonthlyProduction.ProductionLine, qryMonthlyProduction.Shift ORDER BY
"Month" &
DateDiff("m",[DateEntered],DateAdd("m",11,[Forms]![frmWeeklyReportCriter
ia]!
[txtWeek]))
PIVOT "Month" &
DateDiff("m",[DateEntered],DateAdd("m",11,[Forms]![frmWeeklyReportCriter
ia]!
[txtWeek]));

When I run this, it displays the fields in the following order (I'd like the
months to be arranged in order):

Production Facility - ProductionLine - Shift - Total of SumOfBags - Month0 -
Month1 - Month10 - Month11 - Month2 - Month3 ... Month9

Any hints? Thanks in advance!

Larry Mrazek
LCM Research, Inc.
www.lcm-res.com
lmrazek at lcm-res.com
ph. 314-432-5886
fx. 314-432-3304


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com