Cumulative Data query in Access 1

[cogivina]

Hi,
I have this problem and I don't know how to write a query in MS access. I'd like to create the cumulative sales for every following month based on the table below:

Year Mos Sales
2000 1 55
2000 3 78
2000 6 62
2000 10 45
2001 2 36
2001 12 59

----------------------
mos1 = 55
mos2 = sale(mos1) + sale(mos2) = 55+0 = 55
mos3 = mos2 + sale(mos3) = 55+0+78 = 133, ....

The cumulative sale table:

Year Mos Sales
2000 1 55
2000 2 55
2000 3 133
2000 4 133
2000 5 133
2000 6 195
2000 7 195
2000 8 195
2000 9 195
2000 10 257
2000 11 257
2000 12 257
2001 1 0
2001 2 36
2001 3 36
.............
2001 12 95

Thanks in advance.

 

[fneily]

See:
faq701-5268

 

[dhookom]

cogivina,
This looks like the same exact question as thread707-1513383. Please don't waste our time by posting the same question more than once.

Duane
Hook'D on Access
MS Access MVP

 

[rekrabnai]

Hi, I have what I think is a solution.
Essentially I created a query for the current months figures, a query for the previous months figures, then linked and totaled.
It looks a little complicated, but does the trick if I understood the requirements okay.

If not.... ho hum !

Now to figure out if i cannt attach the file!

 

[rekrabnai]

Okay, the upload flopped. so here are is the sql statements for the queries.

Query1

SELECT ([Year]-2000)*12+[Mos] AS YrMos, ([Year]-2000)*12+[Mos]-1 AS YrMos2, tblMOS.Year, tblMOS.Mos, tblMOS.Sales AS SalesCurr
FROM tblMOS;

Query2

SELECT ([Year]-2000)*12+[Mos] AS YrMos3, IIf([Sales]=0,0,[Sales]) AS SalesPrevious
FROM tblMOS;

Query3

SELECT Query1.YrMos, Query1.YrMos2, Query2.YrMos3, Query1.Year, Query1.Mos, Query1.SalesCurr, Query2.SalesPrevious, IIf([SalesPrevious]<>0,[SalesPrevious],0) AS SalesPrevious2, [SalesCurr]+[SalesPrevious2] AS CumulativeSales
FROM Query1 LEFT JOIN Query2 ON Query1.YrMos2 = Query2.YrMos3;