[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