about sum function

[hui]

in access I have a table that have 4 columns,
deptID, employeeID, wage,bonus
every row in deptID and employeeID are stored in data, but in wage and bonus
column, some are empty. when I run the SQL:
select deptID,sum(wage+bonus) as pay from TableName group by deptID
the PAY column is empty or only zero in it.

why?

thank you for your help.

hui

 

[pietlinden]

in access I have a table that have 4 columns,
deptID, employeeID, wage,bonus
every row in deptID and employeeID are stored in data, but in wage and bonus
column, some are empty. when I run the SQL:
select deptID,sum(wage+bonus) as pay from TableName group by deptID
the PAY column is empty or only zero in it.

why?

thank you for your help.

hui

for any columns that could contain nulls, use NZ([fieldname],0) and
then sum that. It converts all the nulls to zeroes.

 

[hui]

ok, I made a procedure to change all null to zrero. so I get the right
value.

thank you for your NZ

hui

<[email protected]>
??????:cb813c8b-f18d-462e-ab85-9210e0f8c993@m44g2000hsc.googlegroups.com...

in access I have a table that have 4 columns,
deptID, employeeID, wage,bonus
every row in deptID and employeeID are stored in data, but in wage and
bonus
column, some are empty. when I run the SQL:
select deptID,sum(wage+bonus) as pay from TableName group by deptID
the PAY column is empty or only zero in it.

why?

thank you for your help.

hui

for any columns that could contain nulls, use NZ([fieldname],0) and
then sum that. It converts all the nulls to zeroes.