Cumulative number in query

[Garry]

Hi

Is it possible to create cumulative numbers in a query

1) I would like a running total over all

2) I would like a running total over group

also I have a text refference field with 0123, 23198, 23, 348777 etc

I would like to print these with a standard format of 6 characters 000123,
023198, 000023, 348777

is this possible

cheers, Garry

 

[Michel Walsh]

For your running sum, over a field, say dateTimeStamp, you can use something
like:


SELECT a.dateTimeStamp,
LAST(a.value) AS actual,
SUM(b.value) AS running
FROM myTable AS a INNER JOIN myTable As b
ON a.dateTimeStamp >= b.dateTimeStamp
GROUP BY a.dateTimeStamp



If you want the running sum by client, you change it to:

SELECT a.clientID, a.dateTimeStamp, LAST(a.value), SUM(b.value)
FROM myTable AS a INNER JOIN myTable As b
ON a.clientID = b.clientID
AND a.dateTimeStamp >= b.dateTimeStamp
GROUP BY a.clientID, a.dateTimeStamp



Optionally, add an ORDER BY clause, at the end.

Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

For your string representation, use a format:


? Format( 11, "000000")
000011



Vanderghast, Access MVP

 

[Garry]

Would it be possible to have this answer in english, thanks

 

[Michel Walsh]

To have a running sum, you have need two fields: the one you will sum, say
it is called 'theValue', and the one that defines the order of the run, say
it is called 'dateTimeStamp'. Those fields have to be from some table, call
it 'myTable'. Replace the place holder 'theValue', 'dateTimeStamp' and
'myTable' with your own names in:

SELECT a.dateTimeStamp,
LAST(a.theValue) AS actual,
SUM(b.theValue) AS running
FROM myTable AS a INNER JOIN myTable As b
ON a.dateTimeStamp >= b.dateTimeStamp
GROUP BY a.dateTimeStamp


You mentioned you also want to run a summing sum over a group, so, say the
field defining the group is 'clientID', then, in the SQL view of a query,
try:

SELECT a.clientID, a.dateTimeStamp, LAST(a.theValue), SUM(b.theValue)
FROM myTable AS a INNER JOIN myTable As b
ON a.clientID = b.clientID
AND a.dateTimeStamp >= b.dateTimeStamp
GROUP BY a.clientID, a.dateTimeStamp



(again, replace the place holder names 'theValue', 'dateTimeStamp',
'myTable' and 'clientID' with your names).



Vanderghast, Access MVP

 

[Garry]

Hi Michel, thank you for your time, you have probably come to the
conclusion that I am not that knowledgeable with SQL however and you are
correct

Your examples are throwing up some strange results but thought provoking,
perhaps my dialog was not that good so here goes again, I am looking for
this solution

Say client(x) has 4 entries, client(y) has 6

I require :

CLIENT CUMULATIVE OVER GROUP
x 1 1
x 2 2
x 3 3
x 4 4
y 5 1
y 6 2
y 7 3
y 8 4
y 9 5
y 10 6

Thanks once again, Garry

 

[Michel Walsh]

What makes the first record, the first record?


That is not so obvious. If you answer: "it is the first one which is
displayed by Access when I open the table", then, that is a really ...
dangerous... answer. You see, records, in a table, are like candies in an
Halloween bag of candies: they are, right now, in 'a', 'one', given order,
but shake the bag a little (add/erase some records) and your candies can
move in what can appear like chaos. That is one of the big difference
between a database table and a spreadsheet (where the candies, the rows,
will stay in a relative position to each other, even if you insert, move, or
delete some 'other' rows).


So, what makes the first record, the first record? It has to be the values
in a field of the record. Generally, a good choice is a date_time_stamp, the
date and time value when the record has been added to the table!

So, if your table has fields like:

client someInfo dateTimeStamp ' fields name


then, the first query can be:

SELECT a.client, COUNT(*)
FROM myTable AS a INNER JOIN myTable AS b
ON a.dateTimeStamp >= b.dateTimeStamp


and the second query:


SELECT a.client, COUNT(*)
FROM myTable AS a INNER JOIN myTablew AS b
ON a.client=b.client
AND a.dateTimeStamp >= b.dateTimeStamp



but you need to identify which of your field that can act for the 'order'
(who is first, who is second,... ). It is not obliged to be a dateTimeStamp.
An address can be used ( *if* it is unique, without duplicated values ),
but it is preferable to be a numeric type (faster for execution time, but to
have a result is what is the most important, in a first step).


Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

I forgot the GROUP BY clauses... and the first query need an aggregate on
client:


SELECT LAST(a.client), COUNT(*)
FROM myTable AS a INNER JOIN myTable AS b
ON a.dateTimeStamp >= b.dateTimeStamp
GROUP BY a.dateTimeStamp


and

SELECT a.client, COUNT(*)
FROM myTable AS a INNER JOIN myTable AS b
ON a.client=b.client
AND a.dateTimeStamp >= b.dateTimeStamp
GROUP BY a.client, a.dateTimeStamp




Also, since you are using 'client', their address would be a poor candidate,
if you don't have a dateTimeStamp. You have a primary key? that would be
ideal.



Vanderghast, Access MVP