Query Group by Problem

[D. Stacy]

I have a query that I'm having trouble getting it to GROUP by the correct
field.

SELECT TblServiceLines3.ServiceLineName,
Sum(DateDiff("n",TblOR_Log_Data!INOR,TblOR_Log_Data!OOR)) AS CaseLength
FROM TblOR_Log_Data LEFT JOIN TblServiceLines3 ON TblOR_Log_Data.Service =
TblServiceLines3.ServiceCode
GROUP BY TblServiceLines3.ServiceLineName, TblOR_Log_Data.Anesthetist
HAVING (((TblOR_Log_Data.Anesthetist) Is Not Null));


Produces Results like this....

ServiceLineName CaseLength
Cardiology 817
Cardiology
Cardiology 11403
Cardiology 22196
Cardiology 365
DENTISTRY 155
DENTISTRY 118
EAR NOSE AND THROAT 417
EAR NOSE AND THROAT 196
EAR NOSE AND THROAT 198
EAR NOSE AND THROAT 254
Endos 2935
Endos 3213
Endos 4981
Endos 1543
Endos 2848


I want it to Group by ServiceLineName; I really don't care if it groups by
TblOR_Log_Data.Anesthetist or not but I only want data if this field is not
null.


Thanks

 

[Michel Walsh]

Maybe:


SELECT TblServiceLines3.
ServiceLineName,
Sum(DateDiff("n",TblOR_Log_Data.INOR,TblOR_Log_Data.OOR)) AS CaseLength
FROM TblOR_Log_Data LEFT JOIN TblServiceLines3
ON TblOR_Log_Data.Service = TblServiceLines3.ServiceCode
WHERE (((TblOR_Log_Data.Anesthetist) Is Not Null))
GROUP BY TblServiceLines3.ServiceLineName


(in the DateDIff expression I also change the two ! into . Not
required, but preferred.)



Vanderghast, Access MVP