Quoting Adam <s3057043@...>:

Adam
Generally one field for the amount and another to identify the type of
transaction is best. You could then extend it to identify debit errors
and credit errors with another number, say 3&4.
e.g. Your table (Trans_Table) may look like this:
Trans_Date Date
Trans_Amount Numeric(9,2)
Trans_Type Integer (1=debit, 2=credit)
Then you can get your results easily with either a stored procedure,
or on the fly, similar to the following:

select Trans_Date,
sum(case when Trans_Type=1 then Trans_Amount else 0 end),
sum(case when Trans_Type=2 then Trans_Amount else 0 end)
from Trans_Table
group by Trans_Date

Alan

> Hi Group,
>
> I have a table that stores debit and credit transactions. At the
> moment, a positive value in the amount field means a deposit and a
> negative value in the amount field means a withdrawal.
>
> I need to display the total deposits, total withdrawals and balances,
> but using the current table structure that requires three subselects.
>
> This is obviously not an unusual problem, Is the way I have done it
> the best way to do it, or would it be better to have separate fields
> for debits and credits?
>
> TIA
>
> Adam
>
>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>

--
Alan J Davies
Aldis
Tel: +44 (0)1926 842069
Mob: +44 (0)7885 372793