Thanks to tek-tips I can now do a r

[R00K]

Thanks to tek-tips I can now do a running total in a query. Now I need a second running total that displays the previous day.

Here is the code for the running total.

SELECT ControlDates.BalanceDate,
Sum(Deposit-Withdrawal)
FROM Transactions, ControlDates
WHERE TransDate<=BalanceDate
GROUP BY ControlDates.BalanceDate;

I want to use the same code in a new query that displays yesterday. This new query will be yesterdays balance where the example query is today's balance. I'm sure there is a simple line of code, but i don't know it.

Thank you for your help.

 

[Golom]

I'm not understanding ... doesn't this produce a running total for ALL BalanceDates? ... including Yesterday? For example

ControlDates
BalanceDate

01/01/03
01/02/03

Transactions
TransDate Deposit WithDrawal

01/01/03    40       10       
01/01/03    35       15
02/02/03    15        5
02/02/03    20       15

Your query should produce

Result
BalanceDate  Balance

01/01/03       50
02/02/03       15

 

[SonOfEmidec1100]

I agree its a bit confusing - I see two possibile explanations, the example query shown is not the query that is run in that there is no < just = sign, or only the first row is being examined.

To get just the previous days total I think this should work

SELECT Top 1 ControlDates.BalanceDate,
Sum(contactid)
FROM Transactions, ControlDates
WHERE TransDate<BalanceDate
GROUP BY ControlDates.BalanceDate
ORDER BY BalanceDate DESC

 

[R00K]

I'm sorry for the confusion. It is confusing for me, too, as we are converting our Excel spreadsheets to Access and it is rough going.

I hope this is clearer. Every day, the total balance of the fund is calculated based on todays actions (deposits, withdrawals, profits, losses, fees, foreagn tax withholding, etc.) and yesterdays final balance. For example, today's mangagemnt fee is (.0000547*Yesterday's balance) or today's gain percentage is (profit-loss)*Yesterday's balance. We call yesterday's balance the netnetbalance.

I don't need a query to solve the whole riddle as I have several working queries for most of the sub parts to the final balance. What I really need is the statement that that refers to the previous day in a string that displays todays data.

We wrestled with a circular reference in excel but it worked like this. The fund starts with $100000 dollars, say. The next day the fund earns $5000 and pays out .0000547 mgt fee on yesterday's $100000 (not $105000) resulting in $5.47 mgt fee and a final nnbalance today of $104994.53(99994.53 + 5000). Tomorrow, the process repeats on the new balance of $104994.53 * .0000547 to calculate todays mgt fee, and so on.

I don't blame y'all if this is a little hard to figure out.

 

[R00K]

Ok. I figured it out. Here is the query:

SELECT ControlDates.BalanceDate, Sum(Deposit-Withdrawal) AS Previousbalance
FROM Transactions, ControlDates
WHERE (((Transactions.TransDate)<=[BalanceDate]-1))
GROUP BY ControlDates.BalanceDate;

The -1 in the query gives yesterdays balance on today's date.

Here is the query for today's balance:

SELECT ControlDates.BalanceDate, Sum(Deposit-Withdrawal) AS
Currentbalance
FROM Transactions, ControlDates
WHERE TransDate<=BalanceDate
GROUP BY ControlDates.BalanceDate;

This shows that I need a handy list of functions and ways to use them.

Thanks all.