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

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

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

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" <dig314@yahoo.com> wrote in message
news:d7effb67.0408201044.1370b1dc@posting.google.c om...[color=blue]
> 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[/color]

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

dig314@yahoo.com (Dig314) wrote in message news:<d7effb67.0408201044.1370b1dc@posting.google. com>...[color=blue]
> 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[/color]

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?

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

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





dig314@yahoo.com (Dig314) wrote in message news:<d7effb67.0408201044.1370b1dc@posting.google. com>...[color=blue]
> 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[/color]