SELECT DISTINCT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ; IIF( NET.Route == NET.Mcl_Rte, " ", NET.Mcl_Rte) as ContrNum, NET.Family, ; 0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, ; IIF( NET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType ; FROM NotETM NET ; JOIN Operator op ; On NET.Operator == op.Code ; LEFT JOIN RF1 ; On NET.Operator == RF1.Operator ; WHERE ( ( m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ; ...In this case there is just 1 NotETM (NET) record that meets the criteria for each operator
SELECT DISTINCT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ; IIF( ET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType, ; IIF( ET.Route == ET.Mcl_Rte, " ", ET.Mcl_Rte) as ContrNum, ET.Family, ; SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ; SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ; FROM ETMData ET ; JOIN Operator op ; On ET.Operator == op.Code ; LEFT JOIN RF1 ; On ET.Operator == RF1.Operator ; WHERE ( ( ET.date BETWEEN ldStartCurr and ldEndCurr) ; OR ( ET.date BETWEEN ldStartPrev and ldEndPrev) ); ...but this, since I had to add the LEFT JOIN, now takes FOREVER. Obviously it's creating too big a dataset, or intermediate data, but I don't see why either code is taking so long or failing. I can't really trust this second SQL now.
>> IIF( NET.Operator == RF1.Operator ; >> and NET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType ; >>>>
>> and NET.Route == RF1.ERoute ; >>>>
>>SELECT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ; >> IIF( NET.Route == NET.Mcl_Rte, " ", NET.Mcl_Rte) as ContrNum, NET.Family, ; >> 0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, ; >> IIF( NET.Operator == RF1.Operator ; >> and NET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType ; >> FROM NotETM NET ; >> JOIN Operator op ; >> On NET.Operator == op.Code ; >> JOIN RF1 ; >> On NET.Operator == RF1.Operator ; >> and NET.Route == RF1.ERoute ; >> WHERE ( ( m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ; >> ... >> >>