CODEWITHSUNDEEP
sqlms-accessvba
emilios
emilios

Reputation: 375

Multi-level Group By clause is not allowed in a sub query

The Query is running fine in query design when i use TR.DocNo field in My report. an error msg pops up : 

Multi-level Group By clause is not allowed in a sub query.

any ideas how can i fix it thanks 

SELECT T.traID, 
           IIf(T.trawrhIDTo,"Purchase"," Sales ") AS FormSorP, 
           T.traDocNoID, 
           W.wrhName, 
           TS.trsPrice, 
           TS.trsVatRate,
           TS.trsAuxField, 
           WI.whiAdjustment, 
           W.wrhCountry,
           T.traDate AS TDATE, 
           TS.trsSubTotalGross,
           C.cstName AS CName, 
           TR.DocNo
         (SELECT tblTransactionsSC.traDocNoID AS DocNo
            FROM tblTransactionsSC
           WHERE (((tblTransactionsSC.traID)=[Forms]![frmReturns]![tratraID]))) AS TR
      FROM tblWarehouse AS W 
          INNER JOIN ((tblTransactionsSC AS T LEFT JOIN tblCustomer AS C ON T.tracstID = C.cstID)   
          INNER JOIN (tblTransactionsSubSC AS TS  
          LEFT JOIN tblWarehouseItem AS WI ON TS.trswhiID = WI.whiID) ON T.traID = TS.trstraID) ON W.wrhID = T.trawrhID
      WHERE (((T.traID)=[Forms]![frmReturns]![traID]));

Upvotes: 0

Views: 197

Answers (1)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

You are missing comma check it

SELECT T.traID, 
           IIf(T.trawrhIDTo,"Purchase"," Sales ") AS FormSorP, 
           T.traDocNoID, 
           W.wrhName, 
           TS.trsPrice, 
           TS.trsVatRate,
           TS.trsAuxField, 
           WI.whiAdjustment, 
           W.wrhCountry,
           T.traDate AS TDATE, 
           TS.trsSubTotalGross,
           C.cstName AS CName, 
           TR.DocNo,
         (SELECT tblTransactionsSC.traDocNoID AS DocNo
            FROM tblTransactionsSC
           WHERE (((tblTransactionsSC.traID)=[Forms]![frmReturns]![tratraID]))) AS TR
      FROM tblWarehouse AS W 
          INNER JOIN ((tblTransactionsSC AS T LEFT JOIN tblCustomer AS C ON T.tracstID = C.cstID)   
          INNER JOIN (tblTransactionsSubSC AS TS  
          LEFT JOIN tblWarehouseItem AS WI ON TS.trswhiID = WI.whiID) ON T.traID = TS.trstraID) ON W.wrhID = T.trawrhID
      WHERE (((T.traID)=[Forms]![frmReturns]![traID]));

Upvotes: 1

Related Questions