asked on

Getting total of fields from same record

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.

ASKER CERTIFIED SOLUTION

danishani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

[ fanpages ]

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.

danishani

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

UPDATE tblMain SET strD_PresentLoanHereOrigDebt = 0
WHERE strD_PresentLoanHereOrigDebt Is Null

John Desselle

ASKER

Adding the default value of "0" will be the easiest way around this. I knew it was something simple I was missing. Thanks

[ fanpages ]

You're welcome.