Link to home
Start Free TrialLog in
Avatar of asrgrant
asrgrant

asked on

sum field and Show empty records ( IS NULL) ??

I want have 2 tables: Personnel and Unit_time. Im currently displaying a grid, attached, but want to show the "personnel.surname" that have not added to "unit_time.hour". So i basically want to show a full grid of people including those that have not entered "hours"
-------------------------------------
SELECT personnel.rank, personnel.surname, Sum(unit_time.hours) AS SumOfhours, unit_time.personID, Count(unit_time.personID) AS CountOfpersonID

FROM personnel INNER JOIN unit_time ON personnel.PersonID = unit_time.personID
WHERE unit_time.datestamp  between firstdate AND secdate

GROUP BY personnel.surname, unit_time.personID,personnel.rank
-----------------------------------------

Many Thanks
ee1.gif
Avatar of jogos
jogos
Flag of Belgium image

Then it's a left join + condition not in where but in join
SELECT personnel.rank, personnel.surname, Sum(unit_time.hours) AS SumOfhours, unit_time.personID, Count(unit_time.personID) AS CountOfpersonID

FROM personnel
 LEFT  JOIN unit_time ON personnel.PersonID = unit_time.personID
AND unit_time.datestamp  between firstdate AND secdate 

Open in new window

Avatar of asrgrant
asrgrant

ASKER

Hey changed the join and nothing happened.
Just offline back tomorrow. Its 14:38 GMT (ZULU TIME)
MANY THANKS
ASKER CERTIFIED SOLUTION
Avatar of jogos
jogos
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial