SQL Transform Statement

[Blake Rice]

Please help make this work, I have tried any number of different ways to escape the quotes and not one of them has worked.

It always comes up with a Syntax error, which tells me that I am just not trying it properly and HOPEFULLY someone out there will know the correct syntax

TRANSFORM Sum(Backlog.Volume) AS SumOfVolume
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume]
FROM Backlog
GROUP BY Backlog.SupplyingCompanyID
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan '" & YEAR(CURDATE()), ... {through to Dec});

Any help that anyone can give will be much appreciated.

[Rabbit]

Jet SQL doesn't use the IN clause for PIVOT. It does it automatically.

[Blake Rice]

Thank you for your reply! But I am not sure I understand you, what do you mean when you say it does it automatically? I have seen some examples for using IN clause after PIVOT.

For example:

TRANSFORM Sum(Backlog.Volume) AS SumOfVolume 
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume] 
FROM Backlog 
GROUP BY Backlog.SupplyingCompanyID 
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan '11", "Feb '11", ... {through to "Dec '11"});

Would give me the desired results that I am looking for, however if I do it that way, then the " '11 " is hard coded in and wont roll over in the next year. I am looking to dynamically pull the year part of the CURDATE() function in SQL and have it use that as part of the string "Jan '" & YEAR(CURDATE()) " type of thing, so that next year the I only get '12 volumes.

I have looked for the last couple days for some help with this TRANSFORM statement and can't really find what I am looking for.

[Rabbit]

Like I said, Jet SQL doesn't use the IN clause.

TRANSFORM Sum(Backlog.Volume) AS SumOfVolume  
SELECT Backlog.SupplyingCompanyID  
FROM Backlog  
GROUP BY Backlog.SupplyingCompanyID  
PIVOT Format([MonthEnding],"mmm 'yy")

[Blake Rice]

Then could you explain why this code would work?

TRANSFORM Sum(Backlog.Volume) AS SumOfVolume  
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume]  
FROM Backlog  
GROUP BY Backlog.SupplyingCompanyID  
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan '11", "Feb '11","Mar '11","Apr '11","May '11","Jun '11","Jul '11","Aug '11","Sep '11","Oct '11","Nov '11","Dec '11");

If you go to the Microsoft web site they provide the syntax for it as well with an optional IN clause after the PIVOT.

http://office.microsoft.com/en-us/access-help/transform-statement-HP001032277.aspx

I am not trying to be ungrateful, I appreciate the fact that you are trying to help me out with this problem, I am just not sure if I am asking my question correctly.

[Rabbit]

The IN clause is optional. If you're trying to limit it to the current year, then you can use the where clause.

WHERE Year([MonthEnding]) = Year(Date())

[Blake Rice]

HA! that is brilliant! I should have thought about that. That did the trick for sure. Thank you so much for your help! Now I will be posting another SQL question on the main site ...

For those interested final code was:

TRANSFORM Sum(Backlog.Volume) AS SumOfVolume   
SELECT Backlog.SupplyingCompanyID, Sum(Backlog.Volume) AS [Total Of Volume]   
FROM Backlog
WHERE Year([MonthEnding]) = Year(Date())   
GROUP BY Backlog.SupplyingCompanyID   
PIVOT Format([MonthEnding],"mmm 'yy") In ("Jan", "Feb","Mar","Apr","May","Jun","Jul","Aug","Sep,"Oct","Nov","Dec");