Convertings Months to numeric values

  • Thread starter Thread starter Angel_G
  • Start date Start date
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
 
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()) ;
 
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.
 
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...
 
Back
Top