How to Aggregate Totals by Date?

[MattLibo]

Hello,

I'm new to Access, and my first 'at home' project is to create a simple database to manage my finances. I'm looking to aggregate my account balance by date, in other words, what is the total amount in my 'account' from my first entry, to each respective date. I've tried using a query that Sums [transaction amount] and Groups By [Date1], but the result is just a total of transaction for each respective date, not an aggregate total from the starting date to each date.

Would anyone know of a method of obtaining running totals by date?

Thanks!

[ranman256]

to sum , you cannot put TRANSDATE in the query. As you see it presents in the result.
uncheck the SHOW button, so transDate only exists in the WHERE clause.

select Sum(amt) from table where transDate between startdate and enddate

[MattLibo]

Hi ranman256,

When I did that I only received the overall total in my account for the current date. What I need is to see the total in my account by day. Are you suggesting running X amount of queries, where X is the number of days since I started recording my transactions, and create a separate query each day?

Thanks!
Matt

[aytee111]

Something like this?

SELECT a.TransDate, a.TransIn, a.TransOut, (SELECT 100+Sum(b.TransIn) - Sum(b.TransOut) AS SumOfTrans
FROM Table1 as b
WHERE (((b.TransDate)<a.TransDate))) AS StartBal
FROM Table1 as a;

Or

SELECT a.TransDate, a.TransIn, a.TransOut, (SELECT 100+Sum(b.TransIn) - Sum(b.TransOut) AS SumOfTrans
FROM Table1 as b
WHERE (((b.TransDate)<=a.TransDate))) AS EndBal
FROM Table1 as a;

[MattLibo]

Hi aytee111,

I tried using both of those codes but the query keeps asking me for 'transin' and 'transout'. I also don't have a variable called 'Transdate'. The two variables I am using are [Date1] = Date and [Transaction Amount] = cost of transaction.

Matt

[aytee111]

Yes, this was my testing names. Change all names to match your setup.