Cummulative Totals 1

[Danyul]

Hi

I have a table containing transaction data entered from a form. For each record there is a 'value' field.

I need to filter the records by catagory, order by date (both of which I can do) and then include a column that carries a running total of the value.

I could push it all out to excel and then reimport it but that seems like a lot of processing for a fairly small return !

Any help much appreciated

Dan

 

[JoanneM]

Hello Dan

Are you asking for a method of populating the table with this information or are you asking for a query definition that will display a dataset

regards

Jo

ps just a maybe ... have you looke at the Orders form on Northwinds?

 

[Danyul]

Hi Jo,

I've checked out the Northwind dbase and can't find what I need.

I've included a short example

date value c. Tot
3/4/02 10 10
4/4/02 7 17
6/4/02 5 22
7/4/02 11 33

The date and value info I have the c. Tot info I wish to calculate.

The data that I am using is extracted from a larger table by month. At this point its a recordset I would be happy to either manipulate the recordset directly to add in the cumulative totals or drop the whole lot into a table and then calculate the c. tot information.

The reason this is causing me trouble is that I don't seem to be able to add info into a recorset on-the-fly and if I drop it into a table first I don't know how to go about ading the c.tot info in as it would almost be like creating a formula for each record.

It's probably a lot less complicated than I think it is, but then so is life !

any ideas ?

Dan

 

[JoanneM]

method 1

put the table/s you are using in a query

group on Orderdate Ascending sort
group on category Ascending Sort
Sum on Order Total
save it as QryRSum1

Create a second query
Make QryRsum1 as your datasource

category first
orderdate second
ordertotal last
and this will show you a running total by category by date

if you need an example of this using Northwinds let me know..

Method2

If you are trying to see a running sum to date of orders sorted ascending and grouped by Category , orderdate,

First your recordset needs a field to hold the data
you can do this at table level or on the fly again but to populate the field the DSum function would be applicable

cat and OD are variables holding the data
they can be passed thru a query or set in the recordset and the dsum used here

d = DSum(&quot;[orderValue]&quot;, &quot;tablename&quot;, &quot;[Categoryname]&quot; = cat And (&quot;[OrderDate]&quot; < &quot;#&quot; & OD )

C.value should then = d
hope this helps

jo

 

[Danyul]

Jo,

Thanks for those solutions, I shall give them a go and see where it takes me !

Regards

Dan

 

[JoanneM]

< &quot;#&quot; & OD )
should read < &quot;#&quot; & OD & &quot;#&quot

sorry
jo

 

[Danyul]

Jo,

I gave method 1 a try as it seems an elegant solution but can't get it to produce a running total. Any chance of an example as suggested using Northwind ?

Many thanks

Dan

 

[RichUK]

Article ID: Q121509