Challenge!!Complex Query

[benc]

Hi everyone

I have this huge query that i need to run below is the SQL code:-

aaah

SELECT tblOpRotation.ArrDate AS Dte, Format(tblOpRotation.ArrATA,"hh:nn") AS ATA, tblOpRotation.TailNo AS TailNum, tblOpRotation.ArrAirportName AS AirportDscr, tblOpRotation.ArrFlightNo AS CallSign, tblOpRotation.ArrPilot1 AS Pilot, tblOpRotation.CustName AS CustName, tblOpRotation.AircraftName AS AircraftDscr, IIf([ArrFlightType]="OCAA" Or Left([ArrFlightType],1)="C",[ArrFlightType],Null) AS CType, IIf([ArrFlightType]="OPOL" Or Left([ArrFlightType],1)="P",[ArrFlightType],Null) AS PType, IIf([ArrFlightType]="OMIL" Or [ArrFlightType]="MM",[ArrFlightType],Null) AS MType, tblOpRotation.ArrNoPass AS NoPass, IIf(tblOpRotation.ArrNight="YES","Y","") AS Night, IIf(tblOpRotation.ArrIntl="YES","Y","") AS Intl, IIf(IsNull(tblOpRotation.ExemptCode) Or tblOpRotation.ExemptCode="","","Y") AS Exempt
FROM ((tblOpRotation INNER JOIN tblLocation ON tblOpRotation.Location = tblLocation.Location) LEFT JOIN tblOpCusts ON tblOpRotation.CustID = tblOpCusts.CustID) LEFT JOIN tblOpRegn ON tblOpRotation.TailNo = tblOpRegn.RegnNo
WHERE (((tblOpRotation.Location)="BAT") AND ((tblOpRotation.ArrDate) Between #1/1/2003# And #6/30/2003#) AND ((tblOpRotation.ArrATA) Is Not Null) AND ((tblOpRotation.Fuel)<>"YES"));

This is a long query and prob more complex than needed to be, however i need to output the results to a report and then calculate the total for individual records for each day in the 6month Period. Does anyone know how to do this.

Thanks

 

[Pat Hartman]

You're right, the query is more complex than it needs to be. Why are you renaming all the fields and some of the IIf()s seem to be unnecessary. You also probably don't want hard-coded dates as selection criteria. You should either prompt for the dates or get them from an input form.

Save the query and then use it as the recordsource for your report. The report has grouping options that will let you group by day or you could do that in the query.