Get transaction balance prior to a certain date

[Ian]

I have a table called transactions. It's the same one as from the Ledger
template slightly modified.

I am trying to get a balance for all transactions prior to a certain date
(I need this on a report as the first line).

I have this query

*******************************************
Query1

SELECT Transactions.AccountID, Transactions.WithdrawalAmount,
Transactions.DepositAmount, Accounts.AccountTypeID,
Transactions.TransactionDate
FROM Accounts RIGHT JOIN Transactions ON Accounts.AccountID =
Transactions.AccountID
WHERE (((Accounts.AccountTypeID)=1) AND ((Transactions.TransactionDate)<#
9/1/2003#));
*******************************************

which returns the transactions for all accounts of type 1 before the date
(I will eventually get the date from a form).

and Query 2 to create the balance

*******************************************
SELECT Query1.AccountID, Sum([Query1]![WithdrawalAmount])-Sum([Query1]!
[DepositAmount]) AS Balance
FROM Query1
GROUP BY Query1.AccountID;
*******************************************

Is this the only way of doing it?

 

[John Viescas]

You can do it in one query:

SELECT Transactions.AccountID, Sum(Transactions.DepositAmount) -
Sum(Transactions.WithdrawalAmount) As Balance
FROM Accounts INNER JOIN Transactions
ON Accounts.AccountID = Transactions.AccountID
WHERE (Accounts.AccountTypeID = 1) AND
(Transactions.TransactionDate < #9/1/2003#)
GROUP BY AccountID

I have no clue why you have a RIGHT JOIN in the first query - that won't
have any effect because you include a predicate on the left table in the
WHERE clause. Also, isn't Balance Deposits minus Withdrawals?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Ian]

You can do it in one query:

SELECT Transactions.AccountID, Sum(Transactions.DepositAmount) -
Sum(Transactions.WithdrawalAmount) As Balance
FROM Accounts INNER JOIN Transactions
ON Accounts.AccountID = Transactions.AccountID
WHERE (Accounts.AccountTypeID = 1) AND
(Transactions.TransactionDate < #9/1/2003#)
GROUP BY AccountID

I suspected you might but I am having a hello of a time getting this
report to work

Thanks for that will give it a go now.

I have no clue why you have a RIGHT JOIN in the first query - that
won't have any effect because you include a predicate on the left
table in the WHERE clause. Also, isn't Balance Deposits minus
Withdrawals?

I think the right join is in there because it's what worked after lots of
trial and error. I really must exhume my SQL knowledge from the lower
depths of my brain.

I agree that the balance should be deposits - withdrawls but that was the
least of my problems.

Again thanks for that.

Now to convince this statement report that the starting balance is what I
say it is.