Running total.....Again

[DataAssassin]

This seems to be a frequent issue. And I think I have looked at too many posts, and now and have totaly confused myself. That is not I big challenge.

Anyway I am trying to build a Data sheet form with a running total field.

Like so...

ID Date a - b Total
__________________________
1 1/01/02 1 2 1
2 2/03/02 5 3 3
3 2/11/02 12 4 11
4 3/05/02 9 3 17
5 9/18/02 0 5 12

If any a or b value is altered the totals should recalculate
If date is changed it should sort by date and recalculate.

or

I would also like Bacon cheese burger, Independant wealth and a cure for all ailments known to man.

Which ever seems more reasonable an within the scope of the forum.

DataAssassin has struck again!

 

[audrey]

If i understand your question correctly, this is how i would do it:

in a query include all your fields that yoou made reference to.

Then add a new field in the last column with the following entered in field row:

Total: ([a]+)

Then under the date field, in the sort row select ascending.

This will always total fields a and b no matter when they change and always sort by date ascendingly. You can base reports and forms on this query if you need it in a datasheet (or subdatasheet) in a form or report.

 

[Cosmos75]

Anyway I am trying to build a Data sheet form with a running total field.

Like so...

ID Date a - b Total
__________________________
1 1/01/02 1 2 1 (Start at 1?)
2 2/03/02 5 3 3 (1 + [2 from 1/01/02] = 3)
3 2/11/02 12 4 11 (3 + [5+3 from 2/03/02] = 11)
4 3/05/02 9 3 17 confused: ??????)
5 9/18/02 0 5 12 confused: ??????)

If any a or b value is altered the totals should recalculate
If date is changed it should sort by date and recalculate.

What do you want as a running total / sum?

Is your data going to look like this?

ID, Date, a, b, RunningTotal
__________________________
1, 1/01/02, 1, 2, 3
2, 1/01/02, 5, 3, 11
3, 2/11/02, 12, 4, 15
4, 3/05/02, 9, 3, 12
5, 3/05/02, 0, 5, 17

where RunningTotal is a + b and by Date (i.e starting a new running total for each new date) ?

or just running?

ID, Date, a, b, RunningTotal
__________________________
1, 1/01/02, 1, 2, 3
2, 1/01/02, 5, 3, 11
3, 2/11/02, 12, 4, 26
4, 3/05/02, 9, 3, 38
5, 3/05/02, 0, 5, 45

I am confused as to what you are trying to accomplish???

 

[audrey]

Ok, i'm thuroughly confused as to what you are looking for, lol. Like Cosmos said, do you want:

total row 1 = row 1 a + b
total row 2 = row 1 total + row 2 a + b

??????

more details please....

 

[DataAssassin]

Sorry, my confusion is spreading.

I think I led you all astray because I was doing a subtraction
A minus b. Thats where the whacky values came from. Addition makes more sense.
-------
4 3/05/02 9 3 17 ( ??????)
5 9/18/02 0 5 12 ( ??????)
-------

So audrey, yes ...

and sorted by date

total row 1 = row 1 a + b
total row 2 = row 1 total + row 2 a + b

 

[Cosmos75]

DataAssassin,
Will you ever have repeating dates?

e.g. (without running totals and not sorted by date yet)

ID, Date, A, B
1, 1/01/02, 1, 2
2, 3/05/02, 0, 5
3, 2/11/02, 12, 4
4, 3/05/02, 9, 3
5, 1/01/02, 5, 3

 

[audrey]

Ok, this one will take a while to figure out, well, for me any way. I'll play around with it and see if i can figure it out. By the way, why are you wanting to do this, if you don't mind me asking... there might be other ways of managing your data.

I know if you try to do something like this in excel, you get a circular reference error. In other words it won't allow you to do it.

Just curious... but i will still try to see if i can get something to work.

audrey

 

[Cosmos75]

NOTE: For the SQL statements I had to leave a space for the a+b part (i.e. [A]+[B ]), if not it would have treated it as the vB Code for Bold.

Also, named Date to DateAB since Date is a reserved word in VBA.

OK, here's a sample database that does a Running Sum query, but it isn’t sorted by date.

Here’s the SQL

SELECT a.DateAB, a.A, a.B, a.ID, (Select Sum([A]+[B ]) from [tblData] where DateAB=a.DateAB and ID <=a.ID) AS RunningSum
FROM tblData AS a;

So what it does is looks at each DateAB and the sums ([A] + [B ]) for the first ID, then, looks to the next ID Sums that add adds the previous sum and does this till it has encountered the last ID.

That is to say it is incrementing the RunningSum by ID.

For that reason, I am unable to sort by Date (See below for another way). So if you data is as such;

ID, Date, A, B, RunningSum
1, 01/01/2002, 1,2, 3
2, 02/15/2002, 4,6, 13
3, 03/20/2002, 10, 5, 28
4, 01/01/2002, 4,5, 37
5, 02/15/2002, 2,2, 41

If you try to sort by date, it’ll give you

ID, Date, A, B, RunningSum
1, 01/01/2002, 1,2, 3
4, 01/01/2002, 4,5, 37
2, 02/15/2002, 4,6, 13
3, 03/20/2002, 10, 5, 28
5, 02/15/2002, 2,2, 41

Worse still, if you don’t enter dates in sequence (i.e. your Autonumber doesn’t follow the date sequence), see below

e.g.

ID, Date, A, B, RunningSum
1, 06/20/2002, 1,2, 3
2, 01/01/2002, 4,6, 13
3, 03/02/2002, 10, 5, 28
4, 12/13/2001, 4,5, 37
5, ,05/23/2002 2,2, 41

One way to get around this is to not have ID be an Autonumber but a user inputed number, that would solve it. So the previous example you would have

UserID, Date, A, B, RunningSum
1, 12/13/2001, 4,5, 9
2, 01/01/2002, 4,6, 19
3, 03/02/2002, 10, 5, 34
4, 05/23/2002 2,2, 38
5, 06/20/2002, 1,2, 41

This would work even if you have records with the same date, the query would just sort it by the UserID (ranking) you give it.


IF, you will never enter an A and B value for the same DateAB

e.g. This would never hapen
DateAB, A, B
1/01/2001, 4,5
1/01/2001, 6,12

THEN

I can sort it by date.

SQL is then

SELECT b.DateAB, b.A, b.B, b.ID, (Select Sum([A]+[B ]) from [tblUniqueDates] where DateAB<=b.DateAB) AS RunningSum
FROM tblUniqueDates AS b
ORDER BY b.DateAB;


Sample database has two tables and two queries

Tables

tblData – query can’t be sorted
-ID
-DateAB
-A
-B

tblUniqueDates – DateAB is index so that there can be no duplicates
-ID
-DateAB
-A
-B

Queries

qryRunningTotal – based on tblData and RunningSum is sorted on ID

qryUniqueDates – based on tblUniqueDates and RunningSum is sorted by date.

Phew, my longest post YET!


Hope this helps! (and hopefully I haven't added to the confusion!)

 

[Rich]

You need a sequential key field and the RunSum function on the form, although you can change the date order out of sequence you need to requery the form using the timer event. It's not recommended on a large recordset though

 

[Cosmos75]

Creating Form?

Rich's post reminded me of another thing. While you can base your form on the query, the data (DateAB, A and B) can't be changed like in a normal select query which may be problamatic when trying to create a form....

 

[Cosmos75]

Found out another thing,

For tblUniqueDates, you can remove the index for DateAB and have multiple records for the same date.

The query will work BUT! for dates that have multiple entries, the RunningSum shown will be for that date NOT record

e.g
Date, A, B, RunningSum
01/01/2001, 1 , 2, 3
02/15/2002, 4, 6, 27
02/15/2002, 12, 2, 27
03/02/2002, 3, 4, 34

 

[DataAssassin]

Wow! You have all been quite Busy.

audrey,

I building a financial app tool package, very small scale no Enron/Arthur/Andersen sized stuff.

In the tool kit I am am adding component to allow one to massage income/debt by date and look at how schedule changes impact funds through the date period they are examing.

So Cosomos75 I can pretty much guarantee I will need to accomodate Multiple similar dates.

I did look at Exel and yes that proved frustrating.

I will munch on what you have posted thus far.
Thanks.
I will post again when my head hurts or I have a Eureka! moment.

 

[Cosmos75]

JonK, help us!

So Cosomos75 I can pretty much guarantee I will need to accomodate Multiple similar dates.

So, you should be able to use the example I posted then, sortof....

If you only need to show the RunningSum and DateAB, you can have a query based on qryUniqueDates

SELECT qryUniqueDates.DateAB, Avg(qryUniqueDates.RunningSum) AS AvgOfRunningSum
FROM qryUniqueDates
GROUP BY qryUniqueDates.DateAB;

We can average RunningSum across the same date since they all show the same RunningSum value.

IF you need each record to be shown as well as the RunningSum then I haven't been able to figure out a way to have a RunningSum for each record (which may have multiple extries with the same DateAB). The SQL I've been using I obtained from JonK. He might be able to tell you if doing that is possible....