crosstab query error 3141 1

[vba317]

After alot of help I have converted a crosstab query in vba that works. It is as follows:

strSql = "TRANSFORM Sum(" & strFld1 & ")" & "AS SumOf" & strFld1 & _
'         " SELECT " & strSel1 & "," & strSel2 & _
'         " FROM " & strTbl & _
'         " WHERE " & strWhr & ">" & [iWhr] & _
'         " GROUP BY " & strSel1 & "," & strSel2 & _
'         " PIVOT " & strWhr & ""

After reviewing the data I have realized I need to factor out if a field is greater than 0 I don't want any results. I have been working all kinds of different combinations, all day, in the where clause with no luck. Now I am trying the having clause and I am getting the error 3141. Is it possible to write a crosstab query with two sums? I have been using the debug.print statement to verify the issue is not spaces. Any help in this matter would be appreciated.


Debug.print results
TRANSFORM Sum(proc)AS SumOfproc SELECT uci,readingprovname FROM PROC_ReadingProvider WHERE billpd>369 GROUP BY uci,readingprovname HAVING SUM proc>0 PIVOT billpd

Current code

strSql = "TRANSFORM Sum(" & strFld1 & ")" & "AS SumOf" & strFld1 & _
         " SELECT " & strSel1 & "," & strSel2 & _
         " FROM " & strTbl & _
         " WHERE " & strWhr & ">" & [iWhr] & _
         " GROUP BY " & strSel1 & "," & strSel2 & _
         " HAVING SUM " & strFld1 & ">" & 0 & _
         " PIVOT " & strWhr & ""

 

[PHV]

HAVING SUM[!]([/!]" & strFld1 & "[!])[/!]>0" & _

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

 

[vba317]

PHV I am now getting the an error 3143 sytax error in the transform statement.

Here is the debug.print statement

TRANSFORM Sum(proc)AS SumOfproc SELECT uci,readingprovname FROM PROC_ReadingProvider WHERE billpd>369 GROUP BY uci,readingprovname HAVING Sum(proc)>0 PIVOT billpd

 

[PHV]

BTW, you can't use an HAVING clause in the TRANSFORM statement.
What about this ?

strSQL = "TRANSFORM Sum(T." & strFld1 & ")" & " AS SumOf" & strFld1 & _
         " SELECT T." & strSel1 & ",T." & strSel2 & _
         " FROM " & strTbl & " AS T" & _
         " WHERE T." & strWhr & ">" & [iWhr] & _
         " AND (SELECT SUM(" & strFld1 & ") FROM " & strTbl & _
         " WHERE " & strWhr & ">" & [iWhr] & _
         " AND " & strSel1 & "=T." & strSel1 & " AND " & strSel2 & "=T." & strSel2 & ")>0" & _
         " GROUP BY " & strSel1 & "," & strSel2 & _
         " PIVOT T." & strWhr

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

 

[vba317]

This causes an error 3070. Microsoft jet engine does not recognize 'T.uci' as a valid field.

Tom

 

[PHV]

And this ?

strSQL = "TRANSFORM Sum(" & strFld1 & ")" & " AS SumOf" & strFld1 & _
         " SELECT " & strSel1 & "," & strSel2 & _
         " FROM " & strTbl & " AS T" & _
         " WHERE " & strWhr & ">" & [iWhr] & _
         " AND (SELECT SUM(" & strFld1 & ") FROM " & strTbl & _
         " WHERE " & strWhr & ">" & [iWhr] & _
         " AND " & strSel1 & "=T." & strSel1 & " AND " & strSel2 & "=T." & strSel2 & ")>0" & _
         " GROUP BY " & strSel1 & "," & strSel2 & _
         " PIVOT " & strWhr

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

 

[vba317]

I am still getting an error 3070. MIcrosoft Jet Engine database does not recognize T.uci as a valid field name or expression.

 

[PHV]

And this ?

strSQL = "TRANSFORM Sum(" & strFld1 & ")" & " AS SumOf" & strFld1 & _
         " SELECT " & strSel1 & "," & strSel2 & _
         " FROM " & strTbl & " INNER JOIN (" & _
         "SELECT " & strSel1 & " AS F1," & strSel2 & " AS F2 FROM " & strTbl & _
         " WHERE " & strWhr & ">" & [iWhr] & " GROUP BY " & strSel1 & "," & strSel2 & _
         " HAVING Sum(" & strFld1 & ")>0) AS S" & _
         " ON " & strTbl & "." & strSel1 & "=S.F1 AND " & strTbl & "." & strSel2 & "=S.F2" & _
         " WHERE " & strWhr & ">" & [iWhr] & _
         " GROUP BY " & strSel1 & "," & strSel2 & _
         " PIVOT " & strWhr

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

 

[vba317]

PHV, thanks once again for your patience and thoughtful help. I wish I could star you more than once.