Dsum Problem

[rfear]

Like many people, I am struggling with getting a cumulative total to work in a query. I think I need to use DSUM but everything I have tried fails ( and I've been trying for hours ).

I have a list of dates ( 203 records in all ) sorted in ascending order, for example,

DATE COMPL
20/06/2006
22/06/2006
22/06/2006
04/07/2006
05/07/2006 etc

Each date corresponds to a job being completed. I want another column that represents the cumulative number of jobs completed, as follows,

DATE COMPL CumTot
20/06/2006 1
22/06/2006 2
22/06/2006 3
04/07/2006 4
05/07/2006 5

Sounds easy, and no doubt it is, but I can't fathom it out ??? Maybe I don't even need DSUM, just determine the position of the record in the recordset.

 

[Alansidman]

Here's one thread on the subject. There are several more on the forum using the search "Running Sum"

http://www.access-programmers.co.uk/forums/showthread.php?t=158845&highlight=Running+Sums

HTH
Alan

 

[Alansidman]

See attached

 

[rfear]

Granted, the example works, but I'm still confused.

How come the query is refering to a table called 'tbl3' when I can't see this in the table view, and how come if I change the SQL to 'table3', ( the table in the table view ) does the SQL fall over e.g.

SELECT Table3.ID, Table3.Date1, Table3.Hours, (Select Sum(Hours) From Table3
WHERE table3.date1 <= table3.date1) AS RunningSum
FROM Table3;

If I do the above all I get is a total ? I am guessing 'tbl3' is a copy of 'table3' and the DSum is doing a comparison ? So how was 'tbl3' created in the query.

Also, another complication, I don't have a field in my table like 'hours' all I have is a list of dates. Maybe DSum isn't the answer, I need to know where the date is in the list. Hope that makes sense.

 

[neileg]

Do you need to do it in a query? It's very easy to create a running sum in a report.

 

[rfear]

The data is for a cumulative flow chart in a report. I am going to try creating the query on the fly using vba and see if I can't add the data that way.

 

[Alansidman]

Rfear;
Here is my source of my information. The table name changes because you give it an alias in the last part of the SQL statement. Perhaps one of our esteemed experts can explain that part for both of us. Here is an explanation of doing running sums, however.

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=279

 

[rfear]

OK, this is getting silly and very frustrating.

If I accept for a second that I have to set up an alias table in my query ( no problem with that bit ) even though I don't know why then how come I now get a syntax error ?

This is my template taken from the database example,

SELECT Tbl3.ID, Tbl3.Date1, Tbl3.Hours, (Select Sum(Hours) From Table3
WHERE table3.date1 <= tbl3.date1) AS RunningSum
FROM Table3 AS tbl3;

As my original table does not have an index I have created a query to add this field to the mix so in the above example 'Table3' is replaced with a query rather than a table. I get,

SELECT tbl3.[ORDER DATE], tbl3.[Arrival Rate Index], (Select Sum(Arrival Rate Index) From NEWQRY_CUMULATIVE_ARRIVAL_RATE1
WHERE NEWQRY_CUMULATIVE_ARRIVAL_RATE1.[ORDER DATE] <= tbl3.[ORDER DATE]) AS RunningSum
FROM NEWQRY_CUMULATIVE_ARRIVAL_RATE1 AS tbl3;

Now I have a syntax error somewhere in the Select Sum statement

How hard can it be to count in MSAccess ?

 

[Alansidman]

Just to be sure, is there a space in your SQL statement after ....ARRIVAL_RATE1 before you put in your carriage return to begin the WHERE portion of your statement.

Also, in the last link I gave you, there was an alternative to this method to do running sums, using DSUM. If you can't get this one to work, then maybe the alternative is an option. Look at what Roger Carson says in the link.

Alan

 

[rfear]

The space was missing, but makes no difference.

SELECT tblalias.[ORDER DATE], tblalias.[Arrival Rate Index], (Select Sum('Arrival Rate Index') From NEWQRY_CUMULATIVE_ARRIVAL_RATE1
WHERE NEWQRY_CUMULATIVE_ARRIVAL_RATE1.[ORDER DATE] <= tblalias.[ORDER DATE]) AS RunningSum
FROM NEWQRY_CUMULATIVE_ARRIVAL_RATE1 AS tblalias;

Interestingly the above syntax appears to get accepted but MSAccess crashes.

 

[rfear]

The second approach doesn't work either, I get an error ???

I think, but I'm not sure, the problem stems from using a date field to control the sum function.

So, forget sum, it doesn't work.

How do I run a query that sorts a set of dates into chronological order and then adds a sequential sequence 1,2,3,4 etc ??? as an extra column.

 

[jal]

So, forget sum, it doesn't work.

Wait a minute, shouldn't this be done with COUNT and not with SUM?

COUNT([Arrival Rate Index])

 

[jal]

Maybe I don't even need DSUM, just determine the position of the record in the recordset.

Yes, a recordset is a viable approach as well. If you open the recordset in date order like this:

SELECT * FROM table1 ORDER BY dateColumn

Then you can loop through the recordset assigning an ordinal value to each record. In fact, if you have a huge number of records, this is probably faster than the query approch because this type of query can be very slow.

 

[rfear]

SELECT qry4.[DATE COMPL], qry4.Index, (Select Sum(Index) From NEWQRY_DR1
WHERE NEWQRY_DR1.[DATE COMPL] <= qry4.[DATE COMPL]) AS RunningSumIndex
FROM NEWQRY_DR1 AS qry4
WHERE (((qry4.[DATE COMPL]) Is Not Null));

Finally, the above syntax has worked. Still don't fully understand why, but to be honest I'm probably beyond caring. 2 days off probably helped. Also carefully modifying the example, field by field seemed to do the trick and then importing the queries to my database. Typing from scratch did not work for some reason.

Anyhow, thanks for the help.

Who would have thought a running sum could be so complicated

 

[jal]

SELECT qry4.[DATE COMPL], qry4.Index, (Select Sum(Index) From NEWQRY_DR1
WHERE NEWQRY_DR1.[DATE COMPL] <= qry4.[DATE COMPL]) AS RunningSumIndex
FROM NEWQRY_DR1 AS qry4
WHERE (((qry4.[DATE COMPL]) Is Not Null));

Finally, the above syntax has worked. Still don't fully understand why, but to be honest I'm probably beyond caring. 2 days off probably helped. Also carefully modifying the example, field by field seemed to do the trick and then importing the queries to my database. Typing from scratch did not work for some reason.

Anyhow, thanks for the help.

Who would have thought a running sum could be so complicated

Now you've got ME confused. I would have thought that "Count" rather than "SUM" would have numbered the dates for you, one by one. Anyway congratulations on success.

 

[rfear]

I'm just relieved it is working and I can move on.

 

[rfear]

Nearly there. I had to turn the query into a make table query as for some reason the chart did not like using the query directly. Not the slickest method in the world but it works. Last challenge, to plot 2 series on the chart.

TRANSFORM Sum(NEWTBL_CUMULATIVEARRIVALANDDEPARTURERATES.RunningSumIndexAR) AS SumOfRunningSumIndexAR
SELECT "Arrival Rate" AS RowHeading
FROM NEWTBL_CUMULATIVEARRIVALANDDEPARTURERATES
GROUP BY "Arrival Rate"
PIVOT NEWTBL_CUMULATIVEARRIVALANDDEPARTURERATES.JOBDATE;

This is the SQL statement to plot one series ( see above ).