How do I use VB to obtain cumulative totals by year month?
Posted on 2002-05-28
I have a graph that requires cumulative data by year month for 5 columns of numeric data. i.e. Month 1 data adds to months 1 thru 12 data, month 2 data adds to months 2 thru 12 data, etc. so that each month shows year to date totals for each column of data. I know I can do this using a series of queries or dsum but these are slow and I would like to use VB.
I need to be able to call the VB as if it were a query and to return the totals to the graph. I've think I've seen VB called by queries before and have a general idea how this is done but am too lite in VB to write the code, so help is needed with an example of what the vb code would look like, how the vb code would be called by the qraph row source property and how the VB code would call the next query.
I have a query (qry1) that provides the following sorted by Year Month:
Year, Month, Data1, Data2, Data3, Data4, Data5
I can limit the data to no more than 5 years, which would be 60 rows of data if using an array but
I prefer to write records into a temp table and then use a query to sum the rows in the temp table. This way I don't have to worry about limiting the data.
Using the temp table I think it would work like this.
1. Read the first row from the query,
2. Write the record to the temp table
3. reduce the month by 1
4 repeat 2 & 3 until month = 0
5. read the next record.
6. repeat 2 - 5 until last query row read
7. then Pass control to the sumQuery to sum the temp table by year month which I can write.
Thanks for the help.