Totals - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Totals

Author

Topic

mfazio
Starting Member

17 Posts

Posted - 2006-01-20 : 09:40:02

Hey guys. I have a small problem. Developing a microsoft access driven database to keep records of costings for a company. Basically i have a query that is built from three other queries which total three different costs.

1. Despatch Items
2. Subcontractor Work
3. Payroll

My problem is that each of these tasks must give a numerical value however if for example there are no payroll items yet its value is null, where as it must be at the least a 0 so that i can easily total it with the others.

Here is one of my SQL statements which totals the payroll to a site

SELECT ContractedWork.SiteName, Sum(ContractedWorkItems.Cost) AS SumOfCost
FROM ContractedWork INNER JOIN ContractedWorkItems ON ContractedWork.ContractedWorkID = ContractedWorkItems.ContractedWorkID
GROUP BY ContractedWork.SiteName;

This works fine however i have 3 seperate statements like this which are then used in a total query giving the 3 together with theyre total.

qryTotalCost
/ | / | / | / | / | / | / | qryPayCost qryDispatchCost qrySubcontractorCost

so basically, the three queries which make up the total query must be able to give a value of 0 if there are no records in the DB where as now they only give a null. IS there some sort of SQL function that can make the nulls numeric?

Thanks for lending me your brains guys.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-20 : 10:20:42

ISNULL ( Sum(ContractedWorkItems.Cost) , 0)

mfazio
Starting Member

17 Posts

Posted - 2006-01-20 : 22:25:09

quote:
Originally posted by RickD

ISNULL ( Sum(ContractedWorkItems.Cost) , 0)

This dosnt seem to work. "Wrong number of arguments used with function in query expression." This is OLEDB if that makes any difference?

mfazio
Starting Member

17 Posts

Posted - 2006-01-20 : 22:44:24

Oh, ive worked it out. ISNULL is the VBA equivilant? I was looking for something that did it on a SQL level.

So if u want to do it in VBA ISNULL(Expression, Replacement)
Or on an sql level nz(Expression, Replacement)

Thanks guys. Hope this can help others

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-21 : 01:14:41

Transact SQL has an ISNULL(Arg1, Arg2) function.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-23 : 02:49:46

>>Or on an sql level nz(Expression, Replacement)

nz is for Access and its SQL Server equivalent is ISNULL

Madhivanan

Failing to plan is Planning to fail

mfazio
Starting Member

17 Posts

Posted - 2006-01-23 : 11:21:40

My bad i should have clarified myself.

VBA has an ISNULL(expression, replacement) function
T-SQL also has the ISNULL(expression, replacement) function

however i was enquiring about Access

Access equivilant is

nz(expression, replacement)

Thanks for the follow ups guys. Hope this helped others

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-23 : 11:38:41

Proceed directly to the Access forum. Do not pass GO. Do not collect $200.

http://sqlteam.com/Forums/forum.asp?FORUM_ID=3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources