Aggregate query problem

[SimonPGreen]

I would appreciate if anyone could tell me why this doesn't work (and how it should be done!)

SQL:

select tblcontracts.contractref as 'Contract Number',tblcontracts.contractname as 'Contract Name',tbljobcards.jobcardno as 'Jobcard',sum(tblbookings.basic) as 'Normal Time',sum(tblbookings.timehalf) as 'Time & Half',sum(tblbookings.double) as 'Double Time',sum(tblbookings.basic) + sum(tblbookings.timehalf) + sum(tblbookings.double) as 'Total Hours'
from
(tblcontracts
inner join
tbljobcards
on tblcontracts.contractid = tbljobcards.contractid)
inner join
tblbookings
on tbljobcards.jobcardid = tblbookings.jobcardid
group by
tblcontracts.contractref,
tblcontracts.contractname,
tbljobcards.jobcardno

The problem is that the last part of the select <= (sum(tblbookings.basic) + (sum(tblbookings.timehalf) + sum(tblbookings.double) as 'Total Hours' => only produces the sum of all three aggregates if they all calculate to a value. If any of them equates to 0 (Null?) i.e. no timehalf then the sum of them all is 0(Null?)

It would be really helpfull if someone could tell me why it logically doesn't work.

Many thanks

Simon

 

[jimirvine]

Hi,
Arithmetically, any calulation involving a Null will always result in a null. Handle Nulls by using the NZ function:
NZ(Number,ValueToReturn if Null)
e.g. NZ(Sum([MyField]),0)

HTH
Jim

 

[SimonPGreen]

Thanks for the pointer Jim - worked a treat!

As an aside is the way I am doing it correct - or is there a better way.

Regards,

Simon

 

[jimirvine]

There may be an argument for a little bit of additional normalizing to the table structure and having all of the Hours info in a single column with a flag to indicate whether it is Basic, Half or Double Time, but The query based on the tables at the moment appear fine.
Jim