SUM function on a predicated YES/NO column

[btturner]

Question... this query returns a summed value of a customers transactions (TransAmount):

SELECT B.FirstName, B.LastName, C.CreditCardNumber, Sum(C.TransAmount) AS SumOfTransAmount
FROM tblCustomers AS B, tblTransactions AS C
WHERE B.SSN=C.SSN
GROUP BY B.FirstName, B.LastName, C.CreditCardNumber;

Problem is that the TransAmount can be a Debit or Credit (both stored as positive #'s in the DB)

How can I interrogate an Access defined column Debit/True = YES, Credit/False = NO
to derive a positive + negative summation of the true value?

1. 25.00 (Debit - represented as a positive number)
2. 35.00 (Debit - positive number)
3. 25.00 (Credit - negative number)
======
+ 35.00

Is there a way to have the TransType field represent postive/negative so that the RunningBalance query will calculate it as a Debit or Credit?

 

[SQLBill]

You need to post this in an ACCESS forum. This forum is for Microsoft SQL Server.

-SQLBill

Not all SQL was created equal.

Posting advice: FAQ481-4875

SELECT 'forum'
CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701',
'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'
ELSE 'FORUM669'
END

 

[JohnEck]

A quick and dirty solution is another table in this form
Debit Factor
Y 1
N -1
Join this to the transaction table by the Debit field and multiply the factor by the Transaction Amount.

 

[btturner]

SQL Bill - I agree w/ the CASE Statement idea. You've supplied a beginner level CASE statement. How do you perform the dual function:

CASE WHEN TransType = NO (indicating negative Credit amount)
SUM(TransAmount)
along w/
CASE WHEN TransType = YES (indicating positive DEBIT amount)
SUM(TransAmount)

In one SQL statement, I'd like to derive the total TransAmount -- understanding that any value in TransAmount is a positive value but can reduce the total amount only if it is a TransType = NO (credit)

 

[cmmrfrds]

SELECT B.FirstName, B.LastName, C.CreditCardNumber,
Sum(Case When TransType = 1 Then C.TransAmount
Else C.TransAmount * -1 End) AS SumOfTransAmount
FROM tblCustomers AS B, tblTransactions AS C
WHERE B.SSN=C.SSN
GROUP BY B.FirstName, B.LastName, C.CreditCardNumber;

 

[Transcend]

btturner ...

I don't think (but I could be wrong) that Access supports CASE statements.

If you look closely at the CASE statement in SQLBill's post you may realise it is posted as a signature, and one which indicates that you have posted in the wrong forum.

Transcend

 

[SQLBill]

btturner,

As I said in my post, you are posting in the wrong forum. You stated you are using Microsoft Access....this is not the Access forum. Microsoft SQL Server (which is what this forum is for) doesn't recognize Access' YES/NO datatype.

You need to post in the proper forum. Look at my 'case' statement in the signature block of my earlier post and you will find links to Access forums as Transcender also pointed out.

-SQLBill