cross tab query help needed 1

[robojeff]

I have a cross tab query that pulls a part number (MPROD) and description (IDESC) as row headings along with a date (MRDTE) and a quantity (MQTY) from my table (MRP_tbl) but I would like to display the sum of each month in a column but need some help with this...

The SQL for my cross tab query is as follows:

TRANSFORM Sum(MRP_tbl.MQTY) AS SumOfMQTY
SELECT MRP_tbl.MPROD, MRP_tbl.IDESC, Sum(MRP_tbl.MQTY) AS [Total Of MQTY]
FROM MRP_tbl
GROUP BY MRP_tbl.MPROD, MRP_tbl.IDESC
PIVOT MRP_tbl.MRDTE;

and I have tried to change the Pivot command to include the following:

PIVOT Format([MRDTE],"mm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

But I am not sure how the syntax of this should be as the format of MRDTE in the table is yyyymmdd

How can I set the format to place the sum of all items in a given month into columns in my cross tab query?

thanks

 

[PHV]

I'd try something like this:

PIVOT MonthName(Mid([MRDTE],5,2),True) In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robojeff]

thats works great... thanks PH