You can compute the sum of credits to date and the sum of debits to date in
subqueries and subtract the latter from the former to give the balance. As
there could be more than one transaction on one day, then you also need to
bring the TransID into play to arbitrarily sort the rows on this column
within each day, so the query is like this:
SELECT TransID, T1.Date, Description, Credit, Debit,
(SELECT SUM(Credit)
FROM Transactions AS T2
WHERE T2.Date <= T1.Date
AND ( T2.TransID <= T1.TransID
OR T2.Date <> T1.Date)) -
(SELECT SUM(Debit)
FROM Transactions AS T2
WHERE T2.Date <= T1.Date
AND ( T2.TransID <= T1.TransID
OR T2.Date <> T1.Date)) AS Balance
FROM Transactions AS T1
ORDER BY T1.Date DESC, TransID DESC;
The query is sorted in descending date order, which is normal for financial
statements, but if you wish you can sort it in ascending date order by
changing the last line to:
ORDER BY T1.Date, TransID;
If using the query as the RecordSource of a report, however, then omit the
ORDER BY clause and use the report's internal sorting and grouping mechanism
to sort it. Reports do not always respect the ORDER BY clause of a query.
BTW I'd recommend against using Date as a column name. It’s the name of a
built in function, so could cause confusion and give the wrong results in
some circumstances. Use something more specific like TransDate. Qualifying
it with the table name (or alias in the above case as aliases T1 and T2 are
used to differentiate the separate instances of the Transactions table)
should avoid any confusion, but nevertheless it would be wise to change it.
Another point is that it looks as though you might be leaving the credit or
debit column Null in each row as a appropriate. Its best to give currency
columns a DefaultValue property of zero, however, in table design and set
their Required property to True (Yes). For one thing Null is semantically
ambiguous, and they also cause problems in calculations as any arithmetical
expression involving a Null evaluates to Null, e.g. 100 + Null = Null.
Similarly in comparison operations, e.g. 100 > Null = Null. Null and zero
are not the same thing; zero is a value, Null is the absence of a value.
Ken Sheridan
Stafford, England
Hi Al
Thank for your reply, but:
I would like to see the balance in every row and there is another little
problem, the starting balance
[TransID] [Date] [Description] [Credit] [Debit] [Balance]
1 09/01/01 start balance 30
30
2 09/01/13 deposit 100
130
3 09/02/14 payment 50
80
How can I see in a report or in a query the balance per every row?
Thank again to everybody
kofmf,
Well, actually... your example has a balance of -80
[quoted text clipped - 23 lines]
--
Message posted via AccessMonster.com
.
