Multi-Level GROUP BY clause is not allowed in a subquery

[UHNSTrust]

I am unsure where to proceed with this!!

I want a query where I have the following SQL statement. Part of the SQL statment is a field which is a subquery which Ranks the data. The problem is that I can run the query and get the results looking in datasheet view but when I use the query as the recordsource for a report I get Multi-Level GROUP BY clause is not allowed in a subquery

SELECT tblPatient.PatientHospitalNo, [ConsultantSurname] & ", " & [ConsultantTitle] & ". " & [ConsultantInts] AS Consultant, tblGP.GPPCTCode, DeaneslyRef.DeaneslyRefReferralID, Format([ReceiptDateDeanesly],"mmm") AS Month, DeaneslyRef.ReceiptDateDeanesly, (SELECT Count(*) FROM tblReferralDeanesly WHERE (((tblReferralDeanesly.DeaneslyRefReferralID)=[DeaneslyRef]![DeaneslyRefReferralID]) And [tblReferralDeanesly]![ReceiptDateDeanesly]<[DeaneslyRef]![ReceiptDateDeanesly]))+1 AS Ranking
FROM (tblGP INNER JOIN (tblPatient INNER JOIN tblReferral ON tblPatient.PatientID = tblReferral.ReferralPatientID) ON tblGP.GPID = tblPatient.PatientGPID) INNER JOIN (tblConsultants INNER JOIN tblReferralDeanesly AS DeaneslyRef ON tblConsultants.ConsultantID = DeaneslyRef.OncologistHaematologistID) ON tblReferral.ReferralID = DeaneslyRef.DeaneslyRefReferralID
WHERE (((tblPatient.PatientHospitalNo)="d84014") AND ((DeaneslyRef.ReceiptDateDeanesly) Between #1/1/2004# And #7/31/2004#))
WITH OWNERACCESS OPTION;

Can any EXPERT (or anybody cleverer than me) help?

Thanks in advance

Jonathan

 

[UHNSTrust]

I have narrowed it down to the report. If I remove all the groupings off the report (used for formating) the report runs the query.

Can I get around this because I need to group by data to show in the report?

Jonathan