Hello All,

Thank you very much for all the useful information you all provide.

I've run into a problem creating a report. The Main Query (DataInputQuery) I have is below (Thanks again to Paul for walking me through setting this up):

SELECT InputRecordsTable.ID, InputRecordsTable.InputDate, InputRecordsTable.LiftStationName, InputRecordsTable.Pump1Reading, InputRecordsTable.Pump2Reading, InputRecordsTable.RainFall, LiftStations.Pump1PumpRate, LiftStations.Pump2PumpRate, IIf([Pump1Reading]>0,(Select Pump1Reading FROM InputRecordsTable AS Alias WHERE Alias.[LiftStationName] = InputRecordsTable.[LiftStationName] AND Alias.[InputDate]=(Select MAX([InputDate]) FROM InputRecordsTable AS Alias2 WHERE Alias2.[LiftStationName] = InputRecordsTable.[LiftStationName] AND Alias2.[InputDate] < InputRecordsTable.[InputDate])),0) AS PrevPump1, IIf([Pump2Reading]>0,(Select Pump2Reading FROM InputRecordsTable AS Alias WHERE Alias.[LiftStationName] = InputRecordsTable.[LiftStationName] AND Alias.[InputDate]=(Select MAX([InputDate]) FROM InputRecordsTable AS Alias2 WHERE Alias2.[LiftStationName] = InputRecordsTable.[LiftStationName] AND Alias2.[InputDate] < InputRecordsTable.[InputDate])),0) AS PrevPump2
FROM InputRecordsTable INNER JOIN LiftStations ON InputRecordsTable.LiftStationName = LiftStations.LiftName
WHERE (((InputRecordsTable.InputDate) Between [Forms]![PrintingReports]![StartDate] And [Forms]![PrintingReports]![EndDate]));


When setting up a Report using DataInputQuery and setting Grouping Levels of InputDate and LiftStationName, I get the infamous "Multi-Level Group By clause...."

I can get around this error by doing a subquery (DataInputSubquery). I can then create a report w/ the above mentioned grouping levels. All fields will be populated, except for PrevPump1 and PrevPump2 (see attached report.doc ) and any fields calculated from these 2 fields.

If I do a report w/out Grouping, I get every field populated...but I need the Grouping Levels. Any suggestions would be greatly appreciated!

Thank you,

Daniel