Help needed with Cumulative Totals and Graphs

[Natasha Garcia]

Hi all,

New user here so apologies if I post in the wrong place.

My colleague and I are trying to put together a database which automates a very time consuming process which is currently being done in Excel.

The purpose of the database is to pull together a load of actuals from SAP and then the forecasts we have put together and then chuck out a whole load of graphs and a summary spreadsheet/ report for the upper echelons;ons of management.

The data is in the database and so far it is all going well - but we have hit a stumbling block. We need Access to calculate some cumulative totals so that we can throw the whole lot into our excel graphs but we can't figure out how to do it.

We are using ye olde Access 97 so our options are a little limited.

Can anyone give us a starter for 10? If you need anything more technical, I can post whatever information you need.

Thanks
Tasha

 

[Karma]

Group by queries are used to sum/count/stdev etc lots of examples on here - search facility of the forum menu.

http://www.access-programmers.co.uk/forums/search.php

Alternatively you can link pivot charts in excel to external datasources such as Access which will do the grouping and sorting and produce your graphs.

HTH

K.

 

[Len Boorman]

It is sort of relatively straightforward to generate you graphs direct in Access.

I say sort of straightforward cos it can be a little tricky

Basically use queries to do whatever you want to do in terms of massaging your data until you get to the point where a single query gives you all the totals etc along with the X Axis that you want to chart

ASt this point do not worry about parameters to limit the dataset by dates or similar. You can do that afterwards

Use the chart wizard and its not too bad

Once the basic chart is created you can tweak it for presentation

The source query can have parameters added etc and the chart will respond accordingly

I have done this in 97 so that I actually prompt the User for about 3 parameters and I only had to create the one chart

Len

 

[Rich]

Sounds as though you need a Crosstab Query which Access will then pivot on

 

[Natasha Garcia]

Thanks for your help with creating the graphs guys I appreciate it

Any ideas on the cumulative totals?

 

[Len Boorman]

Cumulative totals spreadsheet style can be tricky. Now I have done this but my solution is probably far from elegant and there may be some guru's out there that can improve on it substantially

Basically the method is
1) Create by query a new table that contains the basic data in the sequence you want cumulative totals

2) Add new column Autonumber

3) Add new column Cumulative total

4) Update Cumulative total to total where autonumber =1

5) for N = 2 to X

vba loop update Cumulative where autonumber =N to Cumulative Total where Autonumber = N-1 plus Total where Autonumber =N
Next N

Thats the nutshell of it and and some guru out there will be laughing his socks off

So there may well be better/alternative solutions

But its a thought

Len

 

[Rich]

You only need to use the running sum property of a textbox on your report to get a cumulative total

 

[Natasha Garcia]

Thanks again guys

Is it possible to get a cumulative total on a query rather than on a report?

 

[Vesp]

Hi people,

Tasha and I have now solved this problem but I thought I would post the solution in case it helps anybody else.

The data to be cumulatively summed is in Table1, which contains the columns "Discipline", "Month" and "THEDATA".
The expression for the cumulative sum by discipline is as follows:

Cumulative Sum: DSum("[THEDATA]","Table1","[Month]<= #" & Format([Month],"mm/dd/yyyy") & "# AND DISCIPLINE='" & [DISCIPLINE] & "'")

Hope this helps someone.

Vesp