Error on Multi-Level Group by not allowed in Sub Query

S

SRussell

This looks to be a reporting issue, in that the Query runs fine, but inside
the report it can't work if I have a group???


Here is my query:
SELECT Jobs.ROnumber, Jobs.POnumber, Jobs.CustomerID, Jobs.Location,
Jobs.AircraftNo, Jobs.AircraftType, Jobs.CallIn, Jobs.Stop,
Stoppages.StopTimeStart, Stoppages.StopTimeEnd, Jobs.OnHoldTime,
DateDiff("n",[Stoppages.StopTimeStart],[Stoppages.StopTimeEnd])/60 AS
StopNet, (select top 1 WriteUps.JobDescription from WriteUps where
WriteUps.RONumber = Jobs.RONumber order by 1 ) AS JobDescription,
StoppageType.StopType, Jobs.AircraftSeries, Jobs.[Aircraft Location]
FROM ((Jobs LEFT JOIN Stoppages ON Jobs.ROnumber=Stoppages.ROnumber) LEFT
JOIN WriteUps ON Jobs.ROnumber=WriteUps.ROnumber) LEFT JOIN StoppageType ON
Stoppages.StopTypeId=StoppageType.StopTypeId
WHERE (((Jobs.Stop) Between Forms!FrmRptOutofServiceHrs2!StartDate And
Forms!FrmRptOutofServiceHrs2!StopDate) And
((Jobs.CustomerID)=Forms!FrmRptOutofServiceHrs2!CmbCustomerName))
ORDER BY Jobs.CustomerID, Jobs.AircraftType,Jobs.AircraftNo, Jobs.ROnumber,
Stoppages.StopTimeStart
;

My Grouping in the report:
AircraftType, AircraftNo, RONumber.

My detail is the stoppage detail of Type, Start / Stop and Net

Ok, I took out the multiple groups (down to just the RO #) but I get the
same error ??

I have seen others with this problem and a rewrite the query. In my query I
am bringing it some simple text that defines WHAT was the initial call for
this job. As the job continues there are changes to the initial "what" and
flow in a timely fashion in this other table.


TIA
 
M

Marshall Barton

SRussell said:
This looks to be a reporting issue, in that the Query runs fine, but inside
the report it can't work if I have a group???

Here is my query:
SELECT Jobs.ROnumber, Jobs.POnumber, Jobs.CustomerID, Jobs.Location,
Jobs.AircraftNo, Jobs.AircraftType, Jobs.CallIn, Jobs.Stop,
Stoppages.StopTimeStart, Stoppages.StopTimeEnd, Jobs.OnHoldTime,
DateDiff("n",[Stoppages.StopTimeStart],[Stoppages.StopTimeEnd])/60 AS
StopNet, (select top 1 WriteUps.JobDescription from WriteUps where
WriteUps.RONumber = Jobs.RONumber order by 1 ) AS JobDescription,
StoppageType.StopType, Jobs.AircraftSeries, Jobs.[Aircraft Location]
FROM ((Jobs LEFT JOIN Stoppages ON Jobs.ROnumber=Stoppages.ROnumber) LEFT
JOIN WriteUps ON Jobs.ROnumber=WriteUps.ROnumber) LEFT JOIN StoppageType ON
Stoppages.StopTypeId=StoppageType.StopTypeId
WHERE (((Jobs.Stop) Between Forms!FrmRptOutofServiceHrs2!StartDate And
Forms!FrmRptOutofServiceHrs2!StopDate) And
((Jobs.CustomerID)=Forms!FrmRptOutofServiceHrs2!CmbCustomerName))
ORDER BY Jobs.CustomerID, Jobs.AircraftType,Jobs.AircraftNo, Jobs.ROnumber,
Stoppages.StopTimeStart
;

My Grouping in the report:
AircraftType, AircraftNo, RONumber.

My detail is the stoppage detail of Type, Start / Stop and Net

Ok, I took out the multiple groups (down to just the RO #) but I get the
same error ??

I have seen others with this problem and a rewrite the query. In my query I
am bringing it some simple text that defines WHAT was the initial call for
this job. As the job continues there are changes to the initial "what" and
flow in a timely fashion in this other table.
Click to expand...


Try using a DMin instead of the Top1 subquery.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error on multlevel group by not allowed in a SubQuery 2
Multi-level group-by clause not allowed in a sub-query 2
Error 3612: Multi-level GROUP BY clause is not allowed in subquery 4

Top