Need help with running totals

[Jackson]

I've taken the online Access 13 course and I'm just starting a intermediate Access course. I've also signed up for a VBA course after I finish my intermediate class. I've been trying to write a program on my on and although a real programmer would laugh his butt off at my first single attempt I'm rather proud of what it looks like so far. However, I have one routine that is way over my head. I watched dozens of videos and searched online for and answer and can't figure it out. I've even tried to hire a tutor and so far no one has replied. I see what I'm trying to do in other programs but I'm at a lost as to how they do it. What I'm trying to do is create a sub form with the following:



StartingBalance
$100 (This could be a negative balance)

DatePosted AmountDue LatePayment AmountPaid Balance
6/18/2016 $250.00 $25.00 $275.00

I need a running total so that the balance carries down with each record like a checkbook.

I need the same with the following:

AssessmentAmount AmountPaid Balance

[ranman256]

Since this is not how SQL db works, you'd have to use VB to cycle thru the records and reduce balance.
or
by using a report, you won't need vb, but you can set balance in the report and set the field to running sum to reduce the balance field. But only in the report.

[Jackson]

Thanks for the reply - this is not a good thing - my VB class is twelve weeks off and even then I doubt seriously if they will cover that in a basic or the intermediate class. So with that being said I guess I'm up a creek. Sure wish I had asked the question about three weeks ago. Would have save myself a lot of effort.

[rpeare]

Well you can do something like this:

https://support.microsoft.com/en-us/kb/210338

But you'd have to adapt it for a subform

There is another alternative though. Create a report and use the running sum feature or, if you are not using the subform for data entry you can, when you select a record to view, have the data append to a temporary table then display the contents of the temporary table, this is pretty ugly but might give you the result you want.

You will not be able to use a subform using the for data entry using any method I'm aware of other than temp tables.

You could use an unbound form and use a list box to show your running sum as well.

There's always a way it's just a matter of how much time you want to devote to it.

[rpeare]

If you're determined to do it in a query (I would not recommend it because as your database grows it may become quite unmanageable), make SURE you limit the results of the query to ONLY the person /account/whatever you can, the smaller your dataset the easier it will be

example:

tblActivity

PK	DateField	AmountField
1	1/1/2015	$5.00
2	1/2/2015	$10.00
3	1/3/2015	($7.50)
4	1/4/2015	$14.00
5	1/5/2015	($5.00)

Query with running sums:

SELECT tblActivity.PK, tblActivity.DateField, tblActivity.AmountField, DSum("[AmountField]","tblActivity","[Datefield] <= #" & [datefield] & "#") AS RunSum
FROM tblActivity;

[CJ_London]

that only works if you do not have more than one transaction for any given day - you need something that will make the order column(s) totally unique. You could use the PK, but that assumes that all transactions are entered in date order and relies on the PK incrementing for new records - which is not guaranteed

Say you have entered a couple of records - for date just working on day of the month for the purposes of this exercise

PK Date
1...1
2...3

then you realise you have forgotten two transactions that occurred on the 2nd of the month, so add them in

PK..Date..Amount
1...1........5
2...3........8
3...2........4
4...2........9

so the running sum based on date alone would show

PK..Date..Amount..RunSum
1...1........5..........5
3...2........4..........18
4...2........9..........18
2...3........8..........26

you can't just use date<=date and PK<=PK because PK's for day2 are higher than day3 so would not be summed.

Instead you need a new column to define the order correctly. There may be more than one way, but the way I do it is to create a double number. Dates are actually doubles with special formatting to show dates in the way we are used to - today (18th Aug) for example is 42600, yesterday was 42599. Time is shown as a decimal expressing the time as the number of seconds 'to time' divided by the number of seconds in the day (86400).

So lets say our dates are from 1st August (42583), we can calculate a notional time based on dividing the PK by a number that has to be bigger than the largest PK (otherwise it would result in a value >1). this can be looked up using the dmax+1 function or some other means, or a value can be hardcoded. The value should not be so large that it results in the same calculated fraction - for the purposes of this example I will use 1000

PK..Date..Amount..SortNumber..RunSum
1...1........5...........42583.0001..5
3...2........4...........42584.0003..9
4...2........9...........42584.0004..18
2...3........8...........42585.0002..26

I prefer subqueries to domain functions but do agree that the results should be limited as much as possible.

So you would have something like

SELECT PK, DateField, AmountField, (SELECT Sum(AmountField) FROM tblActivity WHERE (Datefield+PK/1000) <= (T.Datefield+T.PK/1000)) AS RunSum
FROM tblActivity T
ORDER BY datefield, PK

there is no need to output the sort field, shown only in the data for illustration purposes.

[Jackson]

You guys are terrific, I may be old but I'm not stupid and can assure you that I'm in over my head with this one. I truly thank everyone for trying to help. But If I want to get this done I'm going to find someone to do it for me and maybe my classes will catch up to this level of expertise at some point. You guys are at the point where I hope to be in the future. Once again thanks for your replies.

Jackson, knife Maker