GROUP SUM Query Assistance PLEASE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

GROUP SUM Query Assistance PLEASE

Author

Topic

browser
Starting Member

2 Posts

Posted - 2004-07-05 : 10:34:24

My Query shall calculate the SUM(S) of the odometer readings what are entered daily.
Some of this entries are not complete or are missing (OStart and OFinish)

I having problems excluding this particular entries from my SUM calculation.

//Query Start

SELECT Vehicals.VehicalType, Vehicals.VehicalID, Vehicals.VehicalNum, Vehicals.Make, Vehicals.Year, Vehicals.Model, Vehicals.PurchaseDate, Vehicals.Store, Sum(Dailys.KeylockFuel) AS SumOfKeylockFuel, Sum(Dailys.MRLoads) AS SumOfMRLoads, Dailys.OtherFuel1, Sum(Dailys.OtherFuel) AS SumOfOtherFuel, Sum([OStart]-[OFinish]) AS MileageSum
FROM (Vehicals LEFT JOIN Dailys ON Vehicals.VehicalNum = Dailys.TruckID) LEFT JOIN Jobs ON Vehicals.VehicalNum = Jobs.VehicalID
WHERE (((Vehicals.VehicalNum)='28'))
GROUP BY Vehicals.VehicalType, Vehicals.VehicalID, Vehicals.VehicalNum, Vehicals.Make, Vehicals.Year, Vehicals.Model, Vehicals.PurchaseDate, Vehicals.Store, Dailys.OtherFuel1;

//Query END

MileageSum is not correct

if I add the following to my criteria expression
....
WHERE (((Vehicals.VehicalNum)='28') AND ((Dailys.OStart) Is Not Null) AND ((Dailys.OFinish) Is Not Null))
...
it will not exluded the entries where OStart or OFinish = Null

Can somebody please help me to exclude rows in my SUM calulation where the OStart or OFinish field is empty or null??

Thank you
Your input is very much appreciated

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-05 : 12:18:46

SUM(ISNULL(OStart,0)-ISNULL(OFinish,0)) ??

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.