Cumulative Monthly Totals?

[cbetts75]

Hi,

I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
Month Direct Despatches Cum Direct Despatches
2006/03 1580 21867
2006/04 10681 21867
2006/05 9606 21867

The expression I’m using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)

The query should display the following results -
Month Direct Despatches Cum Direct Despatches
2006/03 1580 1580
2006/04 10681 12261
2006/05 9606 21867

I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response

 

[Jon K]

You need to relate the Month field in the subquery with the Month field in the outer table/query. Assuming the outer query is QRY_DirectDespatches_ByMonth_ByModel itself, you can use the code like this:-

Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel AS s where s.[Month]<=QRY_DirectDespatches_ByMonth_ByModel.[Month])


Note: Using a subquery in a correlated way with an outer table/query is inefficient and will take time if the outer table/query is large.)
.

 

[cbetts75]

Thanks a lot for that. The expression now works perfectly.

You'll have to excuse my ignorance when it comes to Access, but what do you mean by subquery and outer table/query?

Also, would it be possible for you to explain why you used certain part of the 'criteria' section of the expression?!

 

[Jon K]

A subquery is a select query in brackets nested inside another query. Sometimes it is also called a sub-select.


When you switch your original query to SQL View, you will see a SQL statement like this:-
Select QRY_DirectDespatches_ByMonth_ByModel.[Month],
(Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel) as [Cum Direct Despatches]
FROM QRY_DirectDespatches_ByMonth_ByModel

Here the (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel) is called a subquery. And the query inside which the subquery nested is referred to as the outer query. Sometimes the data source (i.e. the table or query) on which the outer query is based is also described as the outer table or outer query.

As the subquery is not related to any fields in the outer query, it will always return the total amount of [Direct Despatches] for each record in the outer query.


Now to make the subquery return a cumulative total for each month (i.e. each record) in the outer query, you need to relate it to the month field of the outer query. Hence you need to modify the subquery like this:-

Select QRY_DirectDespatches_ByMonth_ByModel.[Month],
(Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel AS s where s.[Month]<=QRY_DirectDespatches_ByMonth_ByModel.[Month]) as [Cum Direct Despatches]
FROM QRY_DirectDespatches_ByMonth_ByModel

Since the subquery and the outer query are based on the same query QRY_DirectDespatches_ByMonth_ByModel, the alias AS s helps Access to distinguish the subquery's [Month] field from the outer query's [Month] field. The alias for the subquery is needed only when the subquery and the outer query are based on the same data source (i.e. the same query or table) and are correlated.

From the SQL statement of the modified subquery, you can see that when the Month in the outer query is 2006/04, the <= in the subquery will sum the [Direct Despatches] field only up to 2006/04, hence the correct cumulative total for the month.


When a subquery is not related to the outer query (as in your original query), Access needs to run it only once and simply puts the total in each record in the outer query. So it is fast.

When a subquery is correlated with the outer query (as in the modified query), Access needs to run the subquery once for each record in the outer query. So it is inefficient and will take time if the outer query contains many records.

Hope it helps to explain it.
.