Cumulative Query

[lars7]

Hi There,
Is there any way that a cumulative Query can be done in access? I would like to do a graph that grows month after month but can't work out how to do the query that would supply the information.

 

[rac2]

Cumulative values are somewhat awkward to obtain in SQL. Here is an idea that may get you started.

Use the IIf( condition, value_for_true, value_for_false) function to write expressions for each month. You will need twelve expressions. Give each expression a name like Jan, Feb, Mar, etc.

Use the DatePart(interval, date_value) function in a condition to identify the month that a detail value occurs in.

Define a criteria that begins on January 1 of a year and ends on December 31 of the same year. This will limit the detail included in the query to data during one year.

SELECT 
       SUM(
           IIf( DatePart( "m", date_of_item) = 1,
                value_of_item, 0)
           ) AS "Jan",
       SUM(
           IIf( DatePart( "m", date_of_item) = 2,
                value_of_item, 0)
           ) AS "Feb",
       ...
       SUM( IIf( ... ) ) AS "Dec"
FROM MyDetailItems
WHERE date_of_item BETWEEN #01/01/2005# AND #12/31/2005#

 

[lars7]

Thanks rac2 I'll give it a go.