Convertings Months to numeric values

[Angel_G]

Is there a solution for the following?
I would like the following Crosstab query to display the months in numeric
values starting with the current month as "0" the previous month as "1" and
so forth instead of the "yy/mm" format. It will be so much easier for me to
create a rolling Year Sales Report.

TRANSFORM Sum(SDAL_US.LineTotal) AS SumOfLineTotal
SELECT SDAL_US.CustomerID, SDAL_US.CustomerName
FROM SDAL_US
GROUP BY SDAL_US.CustomerID, SDAL_US.CustomerName
ORDER BY Format([PromisedShipDate],"yy/mm")
PIVOT Format([PromisedShipDate],"yy/mm")


Thanks

 

[Jerry Whittle]

TRANSFORM Sum(SDAL_US.LineTotal) AS SumOfLineTotal
SELECT SDAL_US.CustomerID,
SDAL_US.CustomerName
FROM SDAL_US
GROUP BY SDAL_US.CustomerID,
SDAL_US.CustomerName
ORDER BY DateDiff("m", [PromisedShipDate], Date())
PIVOT DateDiff("m", [PromisedShipDate], Date()) ;

 

[Marshall Barton]

Is there a solution for the following?
I would like the following Crosstab query to display the months in numeric
values starting with the current month as "0" the previous month as "1" and
so forth instead of the "yy/mm" format. It will be so much easier for me to
create a rolling Year Sales Report.

TRANSFORM Sum(SDAL_US.LineTotal) AS SumOfLineTotal
SELECT SDAL_US.CustomerID, SDAL_US.CustomerName
FROM SDAL_US
GROUP BY SDAL_US.CustomerID, SDAL_US.CustomerName
ORDER BY Format([PromisedShipDate],"yy/mm")
PIVOT Format([PromisedShipDate],"yy/mm")

I think you can use:

PIVOT DateDiff("m",PromisedShipDate,Date())

I don't think the Order By clause does anything, try
eliminateing it.

 

[Angel_G]

It did the trick.
Thank you very much!

TRANSFORM Sum(SDAL_US.LineTotal) AS SumOfLineTotal
SELECT SDAL_US.CustomerID,
SDAL_US.CustomerName
FROM SDAL_US
GROUP BY SDAL_US.CustomerID,
SDAL_US.CustomerName
ORDER BY DateDiff("m", [PromisedShipDate], Date())
PIVOT DateDiff("m", [PromisedShipDate], Date()) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Is there a solution for the following?
I would like the following Crosstab query to display the months in
numeric
values starting with the current month as "0" the previous month as "1"
and
so forth instead of the "yy/mm" format. It will be so much easier for me
to
create a rolling Year Sales Report.

TRANSFORM Sum(SDAL_US.LineTotal) AS SumOfLineTotal
SELECT SDAL_US.CustomerID, SDAL_US.CustomerName
FROM SDAL_US
GROUP BY SDAL_US.CustomerID, SDAL_US.CustomerName
ORDER BY Format([PromisedShipDate],"yy/mm")
PIVOT Format([PromisedShipDate],"yy/mm")

Thanks