Set date column output

[Ali29J]

Hi all

Been hacking away at trying to output in preferred format, and so far best i can get is below, with one limitation.

Have the following SQL code:

SELECT DISTINCTROW Customers.Customer, Format$(ForecastSales.date,'mmmm yyyy') AS [Date By Month], (Sum(Nz(ForecastSales.calculatedtotal1,0))+Sum(Nz(ForecastSales.calculatedtotal2,0))+Sum(Nz(ForecastSales.calculatedtotal3,0))+Sum(Nz(ForecastSales.calculatedtotal,0))) AS [Sum USD]
FROM ForecastSales RIGHT JOIN Customers ON ForecastSales.Customer = Customers.Customer
GROUP BY Customers.Customer, Format$(ForecastSales.date,'mmmm yyyy'), Format$(ForecastSales.Date,'yyyymm')
ORDER BY Customers.Customer, Format$(ForecastSales.Date,'yyyymm');

At present the output format is

Customer 1 Nov $20
Customer 1 Dec $10
Customer 2 Oct $5
Customer 2 Dec $20

Is it possible to out put it to fixed months, i.e Jan to Dec across the top, if no calc for noth then 0 is enterred. so would be as below:

Oct Nov Dec
Customer 1 0 $20 $10
Customer 2 $5 0 $20

Thanks

Ali

 

[GingerR]

Make it a crosstab query and set the Column Headings property to

"Jan", "Feb", etc.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html