Make Recordset Cumulative By The Month

[alwsid]

Hello.... Hello...
Needs Help From You All.

I've a table name "Totalsales".
I want make the records to be cumulative by the month
Of Totalsales.
That's mean when I'm using a parameter query by the month,
the records should be show the current month of Totalsales
and the cumulative months of Totalsales.

waiting response from you all.
thanks.

[Serge]

I'm assuming the table Totalsales also has a field called Month, also, I assume that you're using Access database. Your query should look like this:

Let's call this query GetTotalSalesByMonth


SELECT Month, Sum(Sales) As Total From TotalSales
GROUP BY Month



Then from VB, you can do something like this. Add a reference to Microsoft ActiveX Data 2.x Objects (select the highest version you have on your system), also drop a ListBox control onto your form. Then use this code:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Provider = "Microsoft.Jet.OLEDB.4.0"
'Substitute path to the database with the appropriate one.
cn.Open "C:\MyDB.mdb", "admin", ""

Set rs = cn.Execute("{ call GetTotalSalesByMonth}")

Do Until rs.EOF
List1.AddItem rs("Month") & " - " & rs("Total")
rs.MoveNext
Loop

[alwsid]

Thanks for your response Serge.

What I need like this Serge.
Ex:My Recordset of "Totalsales"

month salesOfthemonth CumulativeSaleofthemonth
Jan $2,500 $2,500
Feb $3,000 $5,500
Mar $2,000 $7,500
Apr $1,500 $9,000
May $3,500 $12,500
Jun $2,000 $14,500
Jul $1,800 $16,300

When I'm using a parameter Query, let say on the "May"
I'll get the result like this.

month salesOfthemonth CumulativeSaleofthemonth
May $3,500 $12,500

Hopefuly you're clear and will help me.

still waiting your response....

[Serge]

What is the SalesOfTheMonth??? Is is the highest sale???

[alwsid]

Hello Serge..

Total of the month mean the total sales of the month.
Every month I want The Total Of Sales to be Cumulative,
like my example above.
What's you've told me above that true.But my problem when I'm using parameter query like to filter the recordset,
what I need example on the month of May, I'll get the result
the current TotalSales of the month and Cumulative TotalSales from Jan to May.
I think its very clear for you.

Thanks

[lychew]

Why don't you store your month as integer?? Then you can still use serge's method with a little change in Query.

SELECT Sum(Sales) As Total From TotalSales
where 1 <= Month and Month <= i