Multi-level Group by Error
Posted on 2007-10-19
I have the following code that is in the on open event of a report. The problem is the recordsource sql statement I am sure. It is causing a error "Multi-level GROUP BY clause is not allowed in a subquery" Can somebody please let me know how to fix this?
strTopBottom = " DESC;"
strGroupBy = "GROUP BY CUNAME "
strOrderBy = "Order By sum(ORESTR)" & Left(strTopBottom, 5)
lblHeader.Caption = "Top " & strCriteria & " Customers"
strRecordSource = "SELECT TOP " & strCriteria & " CUNAME, Count([ORODR#]) AS OCount, Sum(ORESTR) AS ORev, SUM(ORMILE) AS OMiles, Sum(DIEMIL) AS EMiles,sum(DITMIL) as Tmiles, (Select count([ORODR#]) from CustomerTop10 where [ORDV#] in ('097','098','099')) AS BCount, (Select count([ORODR#]) from CustomerTop10 where [ORDV#] not in ('097','098','099')) AS CCount FROM CustomerTop10 " & strGroupBy & strOrderBy
Me.RecordSource = strRecordSource