By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Math SUM() - - Database amounts lack positive / negative

P: n/a
I need to Total the amounts for each Property ID in a table.
All the amounts are stored as positive in the database.
Based on a transaction code, the amount should be added or subtracted.
There are about 14 transaction codes, but here is an example:

Transaction Code "Addition" - - add the amount
Transaction Code "Disposal" - - subtract the amount

Can this be done in single select statement ?

Thank you for your assistance!

Dig
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
As long as you're not going to have to do any updates on the table, create a
Union query that "corrects" the values, and then do your query on that:

SELECT Amount FROM Table WHERE TransactionCode = "Addition"
UNION
SELECT 0 - Amount FROM Table WHERE TransactionCode = "Disposal"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Dig314" <di****@yahoo.com> wrote in message
news:d7**************************@posting.google.c om...
I need to Total the amounts for each Property ID in a table.
All the amounts are stored as positive in the database.
Based on a transaction code, the amount should be added or subtracted.
There are about 14 transaction codes, but here is an example:

Transaction Code "Addition" - - add the amount
Transaction Code "Disposal" - - subtract the amount

Can this be done in single select statement ?

Thank you for your assistance!

Dig

Nov 13 '05 #2

P: n/a
di****@yahoo.com (Dig314) wrote in message news:<d7**************************@posting.google. com>...
I need to Total the amounts for each Property ID in a table.
All the amounts are stored as positive in the database.
Based on a transaction code, the amount should be added or subtracted.
There are about 14 transaction codes, but here is an example:

Transaction Code "Addition" - - add the amount
Transaction Code "Disposal" - - subtract the amount

Can this be done in single select statement ?

Thank you for your assistance!

Dig


you mean using a function or something?

Function AddMySign(byval curAmount as Currency, byval
strTransactionCode as string) As Currency

Select Case strTransactionCode
Case "Addition","AnotherAdd"
curAmount=abs(curAmount)
Case "Disposal"
curAmount=-curAmount
end select

or did you mean by nesting IIF statements?
Nov 13 '05 #3

P: n/a
Thank you Doug and Pieter very much.
In this particular instance, a function will work best.
However, a Union statement will come in handy later.

Dig

di****@yahoo.com (Dig314) wrote in message news:<d7**************************@posting.google. com>...
I need to Total the amounts for each Property ID in a table.
All the amounts are stored as positive in the database.
Based on a transaction code, the amount should be added or subtracted.
There are about 14 transaction codes, but here is an example:

Transaction Code "Addition" - - add the amount
Transaction Code "Disposal" - - subtract the amount

Can this be done in single select statement ?

Thank you for your assistance!

Dig

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.