Totals and Cumulative Queries/Graphs In Graf-FX

Graf-FX will allow you to drilldown using 3 different types of queries. These are normal consolidations (Group By) functions, Totals where the group by function has been excluded and cumulatives where a preliminary query must be setup before running the cumulative functions. This help details how to run the three different queries for the following demonstration example.

Group By Queries

So if you have the Display Option box set to Group as follows and select the button on Level 1, you will produce the SQL query as follows

Select ProductName ,Sum(Sales) as SumSales from zSales_Demo group By ProductName;

Totals Queries

Set the Display option group to Totals for the example shown above will produce a SQL query as follows

Select 'Totals' as Totals ,Sum(Sales) as SumSales from zSales_Demo;

The difference between this and the group by queries is the consolidation by product is not included in the query. You can switch between GroupBy and Totals at any time and at any level without changing the selections on the screen.

Cumulative Queries

A cumulative query is one where the data is summed row by so that a running total is generated on the ordered data. This query requires a few Access tricks to work correctly and includes a set of special Graf-FX functions. The cumulative functions is a two stage process as where the examples uses the demo data as follows including the Display option box set to display.

Now run and save the Query as "ProductQry" as shown above

Now select the query "ProductQry" in the select box, set the Display option to Cumulative and setup the screen as follows.

You can then select only the two level 0 buttons to either query or graf the cumulative results. This will then produce a cumulative graf as follows

Note that you have to be careful viewing queries that have been generated using this cumulative technique as Access can double count the numbers in query mode if more than 15 rows of results are show.