Convertings Months to numeric values

A

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
 
J

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()) ;
 
M

Marshall Barton

Angel_G said:
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")
Click to expand...


I think you can use:

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

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

Angel_G

It did the trick.
Thank you very much!

Jerry Whittle said:
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.

Angel_G said:
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
Click to expand...
Click to expand...
 
Top