We help IT Professionals succeed at work.

Getting total of fields from same record

John Desselle
on
Hi everyone.  I have one table where each record has about 12 'Original Debt' fields.  I'm trying to show the total of all the "OrigDebt" fields for each record.  I can get this simply by using the below SQL, but if only ONE OrigDebt has a value, the total column is blank.  Is there any easy way around this?
SELECT tblMain.ApplicationID, tblMain.strD_PresentLoanHereOrigDebt, tblMain.strD_OtherFinLoanCompOrigDebt, [strD_PresentLoanHereOrigDebt]+[strD_OtherFinLoanCompOrigDebt] AS TotalOrigDebt
FROM tblMain;

Open in new window


Below I'm showing all this with just using two of my "OrigDebt" fields. Screen shot of query results Screen shot of query results
Comment
Watch Question

You can use the Nz() function to return 0, when there is no value.

SELECT tblMain.ApplicationID, tblMain.strD_PresentLoanHereOrigDebt, tblMain.strD_OtherFinLoanCompOrigDebt, Nz([strD_PresentLoanHereOrigDebt],0)+Nz([strD_OtherFinLoanCompOrigDebt],0) AS TotalOrigDebt
FROM tblMain;

Another tip is having a default 0 when there is not value in fields you need so calculate with, so you avoid having workaround with Nz() function.

Hope this helps,
Daniel
Hi,

You could use something like this...

---
SELECT
tblMain.ApplicationID,
tblMain.strD_PresentLoanHereOrigDebt,
tblMain.strD_OtherFinLoanCompOrigDebt,
IIF(IsNull([strD_PresentLoanHereOrigDebt]),0,[strD_PresentLoanHereOrigDebt])+IIF(IsNull([strD_OtherFinLoanCompOrigDebt]),0,[strD_OtherFinLoanCompOrigDebt]) AS TotalOrigDebt
FROM tblMain;
---

BFN,

fp.
You might update the fields in your Table as well, something like:

UPDATE tblMain SET strD_PresentLoanHereOrigDebt = 0
WHERE strD_PresentLoanHereOrigDebt Is Null


Author

Commented:
Adding the default value of "0" will be the easiest way around this.  I knew it was something simple I was missing.  Thanks
You're welcome.