what is the syntaxis for a pivot table

[sven2]

Hello,

what is the right way to make a pivot query in vba based on another query.

I have the following:

'strSQL = " SELECT CustomerGroup.Klantnaam, Grammages.Grammage, Grade.Grade, "
'strSQL = strSQL & " Widths.Width, Purchased.WidthNr "
'strSQL = strSQL & " FROM Widths INNER JOIN (Grade INNER JOIN (Grammages INNER JOIN (CustomerGroup "
'strSQL = strslq & " INNER JOIN Purchased ON CustomerGroup.Klantnummer = Purchased.Klantnummer) "
'strSQL = strSQL & " ON Grammages.GrammageNr = Purchased.GrammageNr) ON "
'strSQL = strSQL & " Grade.GradeNr = Purchased.GradeNr) ON Widths.WidthNr = Purchased.WidthNr;"
 
'strSQL = " TRANSFORM Count(QryBasicCrosstab.WidthNr) AS AantalVanWidthNr "
'strSQL = strSQL & " SELECT QryBasicCrosstab.Klantnaam, QryBasicCrosstab.Grammage, "
'strSQL = strSQL & " QryBasicCrosstab.Grade, Count(QryBasicCrosstab.WidthNr) AS [Totaal WidthNr] "
'strSQL = strSQL & " FROM QryBasicCrosstab "
'strSQL = strSQL & " GROUP BY QryBasicCrosstab.Klantnaam, QryBasicCrosstab.Grammage, "
'strSQL = strSQL & " QryBasicCrosstab.Grade "
'strSQL = strSQL & " PIVOT QryBasicCrosstab.Width; "

The transform query should be based on the first query. So the first query is actualy the QryBasicCrosstab.

How can I combine these queries in vba?

Best regards,
Sven.

 

[namliam]

First glance it seems to be the proper syntax, anything in particular going wrong?

 

[sven2]

Hello,

the syntax is ok but I have to replace the queryname QryBasicCrosstab to the name? of the first SQL and I don't know how.

Best regards,
Sven.

 

[namliam]

Lets first clean up the code a bit... then you can try something along the lines of...

oldSQL = " SELECT CustomerGroup.Klantnaam, Grammages.Grammage, Grade.Grade, Widths.Width, Purchased.WidthNr " & _
         " FROM Widths " & _
         " INNER JOIN (Grade " & _
         " INNER JOIN (Grammages " & _
         " INNER JOIN (CustomerGroup " & _
         " INNER JOIN Purchased       ON CustomerGroup.Klantnummer = Purchased.Klantnummer) " & _
                                    " ON Grammages.GrammageNr = Purchased.GrammageNr) " & _
                                    " ON Grade.GradeNr = Purchased.GradeNr) " & _
                                    " ON Widths.WidthNr = Purchased.WidthNr "


strSQL = " TRANSFORM Count(QryBasicCrosstab.WidthNr) AS AantalVanWidthNr  " & _
         " SELECT QryBasicCrosstab.Klantnaam, QryBasicCrosstab.Grammage, QryBasicCrosstab.Grade, Count(QryBasicCrosstab.WidthNr) AS [Totaal WidthNr]  " & _ 
         " FROM (" & oldSQL & ") as QryBasicCrosstab " & _
         " GROUP BY QryBasicCrosstab.Klantnaam, QryBasicCrosstab.Grammage,QryBasicCrosstab.Grade  " & _
         " PIVOT QryBasicCrosstab.Width; "

It can be done a little cleaner... but this (or something along these lines) should work.