Query Help.. Creating a running Total Field?????

[Darryn Ross]

Hi

I need to have a running total field in my of my queries and i can't get it
to work!!!

My Fields are ClientCode, Debit, Credit, Running Balance (Debit - Credit)

i need the running balance to calculate the running balance of every
transaction for each ClientCode then go back to 0 eg...

ClientCode, Debit, Credit, Running Balance (Debit - Credit)

1, 100.00, 0.00, 100.00
1, 200.00, 0.00, 300.00
1, 0.00, 100.00, 200.00
2, 0.00, 100.00, -100.00
2, 300.00, 0.00, 200.00
3, 500.00, 0.00, 500.00
4, 50.00, 0.00, 50.00

I do not want a Group By i need all transactions listed individually with
the accurate running balance for each code??

Is This even possible?? or do i need to do it in my application? was hoping
access could do all the processing for me.


Regards

 

[Tom Ellison]

Dear Darryn:

To make this work it is essential that there be a column or set of
columns that uniquely orders the rows. It must be unambiguously
obvious from the data which rows precede any give row, and which rows
follow. Otherwise, without an unambiguous order, the running sum
cannot be unambiguously calculated.

The information that creates this unambiguous ordering must be
recorded somewhere in each row. I don't see that. Perhaps you have
some other rows that could provide that ordering.

To put it another way, if you wrote each row on a slip of paper and
threw them all in a hat, drawing them out at random, could you come up
with a singe value for the running sum for each slip of paper? If
not, then neither can a query do this for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Brian Hoops]

Tom...

I'm assuming that this could be done provided that there was an AutoNumber
(or another incremental) PK? If so, could you point me in the direction of
some documentation on how to do this? It's nothing I need now, I'm
intrigued by the prospect that a query is aware of the preceding rows.

Thanks!

-Brian

 

[Tom Ellison]

Dear Brian:

The fundamental technique is based on aliasing the table(s) involved
and creating a correlated subquery.

Aliasing permits SQL to be written to make two (or more) independent
references to the rows of each table.

Correlation of a subquery allows references to be made back to the
outer "main" query. This can be used to filter the subquery to "all
preceding rows" assuming the definition of "all preceding rows" can be
made unambiguously. If your PK can be guaranteed to be incremental,
so that it actually does define an unambiguous order that matches with
the chronological sequence of the rows, then this could be done.

Notice that, using such a PK as you suggest, there would be
insurmountable problems if someone forgets to enter one of the rows
that belongs to today's data. When this rows is missed, then added to
the table a week later, then that row would be grossly out of
sequence. An autonumber is not likely to be adequate for this
purpose. A date/time column would likely be more appropriate. It
could default to the time the row was begun, but be altered to place
it in the proper sequence when something like ths omission I suggested
has happened.

The query to do something like this may look like this:

SELECT *, (SELECT SUM(Amount) FROM YourTable T1
WHERE T1.DateTime <= T.DateTime) AS RunningSum
FROM YourTable T
ORDER BY DateTime

This technique is appropriate for Ranking rows as well as running
sums. There is an expecially rich set of things that can be done
using Ranking.

Both ranking and running sums can be set to start over within "groups"
of rows based on identical values in one or more columns.

In the above, the DateTime column would have to be guaranteed unique
in order to prevent there being two rows that would add into the
running sum simultaneously. In some applications, this is not
essential. For example, my inventory systems typically do not give a
quantity on hand except on a daily basis. It is not essential how
many of an item is on hand at 3:00 PM. What is needed is to know how
many are on hand at this moment, or at the end of any historic date.

As you can probably begin to see, this is an important technique to be
used with any transactional system. This can be inventory,
accounting, production, purchasing, well, almost anything.

So, how can I help you specifically?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Darryn Ross]

Hi Tom,

Thanks for your help but i am still a little confused with this... your hat
theory explained it well but now i am not sure exactly how to make it work?
adding a PK like an autonumber will not help because the list needs to be
ordered by ClientCode and Date and the running totals re-set everytime the
clientcode changes.

I did find an example form the MS Access website but it uses a Totalling
Query with Group by... and i tried to manipulate it to fit into my scenario
however the query wasn't liking my changes. How do i go about ordering or
recording information to allow the 'Hat' theory to be correctly reflected?

Regards

Darryn

 

[Darryn Ross]

Hi Tom,

The problem with this, is that my transactions can have the same dates the
same clientcodes and the same amounts! there is nothing unique about any of
my transactions except my TransNum (AutoNumber) that i have in my table...
and this doesn't help me in creating the required result in my query,
because like you mention below, transactions can be added in at anytime for
any date so my query needs to order the resulted list by ClientCode & Date
as each grouping... making it in true order, however this would cause the
TransNum counter to be out of whack! and there is no way the query can tell
what it's meant to be using to generate the running totals??

If you could shed some more wisdom on the matter i'd be very grateful! as i
appears i am stuck on this problem.

Regards

Darryn

 

[Tom Ellison]

For a case like this, you can do one of two things:

- Just have a daily total, not a complete running total

- Using the ClientCode, Date, and TransNum as a combined unique key,
perform the running total. The TransNum will arbitrarily order the
rows within each date, but any rows posted out of order will still at
least be shown in the correct date, since that should be a higher
order sort column than the TransNum. This gives the appearance of a
running sum in consecutive order, although it is not usually possible
to keep the autonumber in exact sequence to the actual sequence of the
events. But then, your database hasn't been designed to be able to
accurately sequence the rows within a date. If you had a date/time
and both the date and the time were accurately recorded, then you
would have such a guaranteed sequence. The only thing you get out of
the database is what you have put into it. That creates the upper
limit of what you can retrieve.

Make sense? Your TransNum can be the final arbiter of the order of
rows WITHIN EACH DATE. To the extent the TransNum is not in sequence,
the rows within each date would not be in correct sequence, but by
sorting by date first the rows will at least be in order by date.
Given the data you have, that's the best you could reasonably hope
for.

So, when I design a database, if I want to be able to sort my rows
within a date in actual chronological order, I have to include a time
along with the date. If the user's can't provide a time, or don't
want to be bothered with it, then they cannot expect the database to
somehow "miraculously" put the rows into an order they haven't
provided. And the actual physical order the rows are added can't be
counted on to do this for them. Remember the row that was omitted,
then added a week late? The physical order in which data is added to
the table doesn't represent the chronological order of the events it
represents.

It boils down to Garbage In Garbage Out. You can do your best to
design around it, but the more information that is needed the more
information must be entered. That's why a "daily" balance system is
so often the best workable compromise. If the data can be gathered at
a time clock, then we can probably sequence it and have a realistic
running total showing the balance as of 1:30 PM. If that's too much
bother, then we really can't sequence the transactions at all, and all
you can realistically get is a daily total.

To expect more is to attribute magic to the computer. It's not going
to happen.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Darryn:

Are we now down to cases? Here's an approximate query:

SELECT ClientCode, [Date], Autonumber,
(SELECT SUM(T1.Debit - T1.Credit) FROM YourTable T1
WHERE T1.ClientCode = T.ClientCode
AND (T1.[Date] < T.[Date]
OR (T1.[Date] = T.[Date] AND T1.Autonumber < T.Autonumber)))
AS RunningBalance
FROM YourTable T
ORDER BY ClientCode, [Date], Autonumber

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Darryn Ross]

Tom,

Ok i think i understand it now... i will give it a go and see what i can do.

I have enough info in my table to sort the list correctly i think, in my
initial posting i did leave quite a few fields out just to make it easier to
understand!

I have a Date/Time, ClientCode, TransNum... Although i probably don't even
need the TransNum (AutoNumber).. because of the reasons below.

Thanks for all your help!

Regards

Darryn