• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

How do I use VB to obtain cumulative totals by year month?

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.

Elton



 
0
katzwhite
Asked:
katzwhite
  • 9
  • 8
1 Solution
 
nico5038Commented:
You can create a GroupBy that will do this in one "session":

SELECT Year & right("00"&Month,2) AS YearMonth, Data1, Data2, Data3, Data4, Data5
FROM qry1
Group By Year & right("00"&Month,2) AS YearMonth
Having YearMonth >= (year(date)-5)& right("00"&Month()date,2);

It's not tested, but I guess you get the idea.
When you change the query type into a make-table or append query you can create/fill your temp table.

Clear ?

Nic;o)
0
 
nico5038Commented:
BTW I would recommend to rename your Year and Month field as they are also Access functions.
Use someting like MyYear and MyMonth. The above query would look like:
SELECT MyYear & right("00"&MyMonth,2) AS YearMonth, Data1, Data2, Data3, Data4, Data5
FROM qry1
Group By MyYear & right("00"&MyMonth,2) AS YearMonth
Having YearMonth >= (year(date)-5)& right("00"&Month()date,2);

Nic;o)
0
 
katzwhiteAuthor Commented:
Nic;o)

I was hoping to hear from you.

I don't think its quite as simple as a "Group By".  Each Year Month Row only has data for that Year Month.  I need to total each month into the succeeding months of the year so that each month shows the cumulative totals for the year.

Elton
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
katzwhiteAuthor Commented:
Nic;o)
I think our comments are passing each other.

Can you explain what the group by below is actually doing?  I don't think I understand the year, date Month part of it.

  SELECT MyYear & right("00"&MyMonth,2) AS YearMonth, Data1, Data2, Data3, Data4, Data5
                 FROM qry1
                 Group By MyYear & right("00"&MyMonth,2) AS YearMonth
                 Having YearMonth >= (year(date)-5)& right("00"&Month()date,2);

Thanks

Elton
0
 
katzwhiteAuthor Commented:
Actually the code is suggested above is incorrect.  It needs to work as follows:

1. Read the first row from the query,
                 2. Write the record to the temp table
                 3. increase the month by 1
                 4 repeat 2 & 3 until month >12
                 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

Elton
0
 
nico5038Commented:
Oops, read too fast ;-)

Then I would probably use the DSUM function (yes slowing down, but not too bad when storing the result in a temp table)
Query will look like:
SELECT MyYear & right("00"&Month,2) AS YearMonth, DSUM("Data1","qry1","Year="&[MyYear]&" AND MyMonth<="&MyMonth), DSUM("Data2","qry1","Year="&[MyYear]&" AND MyMonth<="&MyMonth), DSUM("Data3","qry1","Year="&[MyYear]&" AND MyMonth<="&MyMonth), DSUM("Data4","qry1","Year="&[MyYear]&" AND MyMonth<="&MyMonth), DSUM("Data5","qry1","Year="&[MyYear]&" AND MyMonth<="&MyMonth)
FROM qry1
WHERE YearMonth >= (year(date)-5)& right("00"&Month()date,2);

This will accumulate per year.

Or do you really want recordset processing ?

Nic;o)
0
 
nico5038Commented:
Oops, read too fast again ;-)
And indeed the commenting looks more like a chat-session...

What you need is what we call a "progressing year sum"
Selecting five years of data will deliver four years of output as e.g. 01-01-1990 will produce the first yeartotal when added up till 31-12-1990, the second will need to add up 01-02-1990 till 31-01-1991, etc.

With the query this would imply you need to extract a SUM with a BETWEEN like:
DSUM("Data1","qry1","[YearMonth] BETWEEN Year="&[MyYear]-1&Right("00"&MyMonth,2) AND Year="&[MyYear]&Right("00"&MyMonth-1,2)")

Then it would be best to create the yearmonth field in qry1.
Indeed slow ;-)

Let me see if there's another way....

Nic;o)
0
 
katzwhiteAuthor Commented:
Nic;o)

Believe me, I've done this for three month averaging using a series o f3 querries to change the dates as suggested in my code.  It works very fast.

I'm just trying to avoid 12 querries with the VB. I have about 16 graphs where this type of data accumulation is required so some VB code would be much easier to add and modify than 12 querries each time.

Is there a way to call the VB from the graph, have the VB read the rows from qry1 and pass the data back to the graph as if the graph called a query.
If so the VB code should not be difficult.

Also I'm using PurMonth and PurYear so year and month aren't a problem.

PurMonth 02 needs to add into months 02-12 for the PurYear being processed, PurMonth 04 into months 4-12, etc. No overlapping of years
0
 
nico5038Commented:
OK, back to the start.
You need for each year:
yyyy01 jan
yyyy02 jan+feb
yyyy03 jan+feb+mar,
etc.
Thst's exactly what:
DSUM("Data1","qry1","Year="&[MyYear]&" AND MyMonth<="&MyMonth)
Will do.
The change of the dates in your queries are puzzeling me.
I would guess using the Date() function could make the queries "date independent", always producing say the last 5 year.

Are your graphs showing one year ?
BTW are your changes related to the output of the cross-table with it's fixed rows?

Nic;o)
0
 
katzwhiteAuthor Commented:
By changing the dates in each queries using an iff statement, I end up with the row being grouped into each month, the same way you are doing with DSUM, except it isn't slow.  i.e. purmonth 02 gets duplicated in 03 thru 12.  So then using a "group By"  Purmonth  sums it into each of the months 02 thru 12.

Query 2 looks like:

SELECT cP.Yr, IIf(cP.Mo<02,02,Null) AS Mo, IIf(cP.Mo<02,cP![OilP],Null) AS OilP, IIf(cP.Mo<02,cP![GasP],Null) AS GasP, IIf(cP.Mo<02,cP![OilC],Null) AS OilC, IIf(cP.Mo<02,cP![H2OC],Null) AS H2OC, IIf(cP.Mo<02,cP![OthC],Null) AS OthC
FROM AreaStkdBarCumBeqQryA1 AS cP;

Using this approach I changed the query time from just under two minutes to less than 3 seconds for one of my graphs.
0
 
nico5038Commented:
In VBA you can use this function:

Function fncRunningYearMonth()

Dim rsInput As Recordset
Dim rsOutput As Recordset
Dim intI As Integer
Dim intStart As Integer

Set rsInput = CurrentDb.OpenRecordset("tblYearMonthInput")
Set rsOutput = CurrentDb.OpenRecordset("tblYearMonthOutput")

'empty table
CurrentDb.Execute ("delete * from tblYearMonthOutput;")

rsInput.MoveFirst

While Not rsInput.EOF
   intStart = Val(Right(rsInput!yearmonth, 2))
   For intI = intStart To 12
     rsOutput.AddNew
     rsOutput!yearmonth = Left(rsInput!yearmonth, 4) & Right("00" & intI, 2)
     rsOutput!value1 = rsInput!value1
     rsOutput!value2 = rsInput!value2
     rsOutput.Update
   Next intI
   rsInput.MoveNext
Wend


End Function

Just create the two tables like:
tblYearMonthInput
YearMonth
Value1
Value2
(Ofcourse you can use qry1 instead)
and the same for the tblYearMonthOutput.

The only thing not accounted for is the "max yearmonth" as a whole year will be "padded with the last month's data.
Just check with query:
SELECT tblYearMonthOutput.YearMonth, Sum(tblYearMonthOutput.Value1) AS SumOfValue1, Sum(tblYearMonthOutput.Value2) AS SumOfValue2
FROM tblYearMonthOutput
GROUP BY tblYearMonthOutput.YearMonth;

Nic;o)
0
 
katzwhiteAuthor Commented:
Nic;o)

Function fncRunningYearMonth() is what I'm looking for.

But being the dummy I am, what I think of as small changes gives me much difficulty.

I need to call the function from a query as every graph access will probably require different data, so this would work best if called by a query.

I set it up in a module called SummaryQueries as a public function:

Public Function fncAreaStkdBarCumBeqQryB()
' This function outputs rows, one for each month,
'  starting with the row-month thru the 12th month
' This output will be summarized in the AreaStkdBarCumBeqQryC
'  query to calculate yeartodate beq for each month.

 Dim rsInput As Recordset
 Dim rsOutput As Recordset
 Dim intI As Integer
 Dim intStart As Integer

     Set rsInput = CurrentDb.OpenRecordset("AreaStkdBarCumBeqQryA")
     Set rsOutput = CurrentDb.OpenRecordset("AreaStkdBarCumBeqQryC")

     'empty table
    ' CurrentDb.Execute ("delete * from tblYearMonthOutput;")

     rsInput.MoveFirst

     While Not rsInput.EOF
        intStart = Val(rsInput!Mo)
        For intI = intStart To 12
            rsOutput!Yr = rsInput!Yr
            rsOutput!Mo = Right("00" & intI, 2)
            rsOutput!OilP = rsInput!OilP
            rsOutput!GasP = rsInput!GasP
            rsOutput!OilC = rsInput!OilP
            rsOutput!H2OP = rsInput!H2OP
            rsOutput!OthP = rsInput!OthP
            rsOutput.AddNew
          Next intI
            rsInput.MoveNext
      Wend
End Function

I'm trying to access it with the following query but keep getting the error " Can't find the table or query"   I've tried a number of combinations but no luck.

SELECT * from [fncAreaStkdBarCumBeqQryB()];

Any suggestions?

I'm increasing the points, know this doesn't make up for all the time you are spending but its a token of my appreciation.

Thanks

Elton
0
 
katzwhiteAuthor Commented:
Nic;o)

Function fncRunningYearMonth() is what I'm looking for.

But being the dummy I am, what I think of as small changes gives me much difficulty.

I need to call the function from a query as every graph access will probably require different data, so this would work best if called by a query.

I set it up in a module called SummaryQueries as a public function:

Public Function fncAreaStkdBarCumBeqQryB()
' This function outputs rows, one for each month,
'  starting with the row-month thru the 12th month
' This output will be summarized in the AreaStkdBarCumBeqQryC
'  query to calculate yeartodate beq for each month.

 Dim rsInput As Recordset
 Dim rsOutput As Recordset
 Dim intI As Integer
 Dim intStart As Integer

     Set rsInput = CurrentDb.OpenRecordset("AreaStkdBarCumBeqQryA")
     Set rsOutput = CurrentDb.OpenRecordset("AreaStkdBarCumBeqQryC")

     'empty table
    ' CurrentDb.Execute ("delete * from tblYearMonthOutput;")

     rsInput.MoveFirst

     While Not rsInput.EOF
        intStart = Val(rsInput!Mo)
        For intI = intStart To 12
            rsOutput!Yr = rsInput!Yr
            rsOutput!Mo = Right("00" & intI, 2)
            rsOutput!OilP = rsInput!OilP
            rsOutput!GasP = rsInput!GasP
            rsOutput!OilC = rsInput!OilP
            rsOutput!H2OP = rsInput!H2OP
            rsOutput!OthP = rsInput!OthP
            rsOutput.AddNew
          Next intI
            rsInput.MoveNext
      Wend
End Function

I'm trying to access it with the following query but keep getting the error " Can't find the table or query"   I've tried a number of combinations but no luck.

SELECT * from [fncAreaStkdBarCumBeqQryB()];

Any suggestions?

I'm increasing the points, know this doesn't make up for all the time you are spending but its a token of my appreciation.

Thanks

Elton
0
 
nico5038Commented:
Basically you should create a table named:
"AreaStkdBarCumBeqQryC"
having the same fields as "AreaStkdBarCumBeqQryA" (e.g. by running "AreaStkdBarCumBeqQryA" once as a maketable query)

If you have more info on the "differences per graph", then it's perhaps possible to add parameters to the function to make it work for all. I normally would create a "steering table" filled with all needed Input and Output query/tables and then pass those to the function, or even make the function read the "steering table" to process all in one session. When you need to add or delete a graph, all needed is to add/delete that row from the "steering table"
Get the idea ?

Nic;o)
0
 
katzwhiteAuthor Commented:
Nic;o) ,

OK, so I can be sure I understand:

1) Is it possible to call Public Function fncAreaStkdBarCumBeqQryB() from a query as if it were a query.

2) If not, then creating a table named  "AreaStkdBarCumBeqQryC" will store the output and all it to be processed by other queries.  However, when I access the  "AreaStkdBarCumBeqQryC" table with a query it will not provide dynamic data, only data previously input to the table using Public Function fncAreaStkdBarCumBeqQryB().

 Is this correct?

Right now the query "AreaStkdBarCumBeqQryA" provides the variable info based on parameters the user sets and this query is the imput to  Public Function fncAreaStkdBarCumBeqQryB().  

So if I can use this function as dynamic input to my summary query feeding the graph everything will work quite nicely.

Thanks

Elton
0
 
nico5038Commented:
I guess your users always apply the same parameters (e.g. start and enddate), this enables you to pass these to the function and execute the query from code.
When you base the graph on the query of the resulting table, the whole process is "closed".

Clear ?

Nic;o)
0
 
katzwhiteAuthor Commented:
Nic;o),

You've saved me several times in the past with answers to difficult ?s.  Thanks again for all the time you spent.

The answer I have accepted addessed the ? of how to use a dataset to accomplish the VB processing I was asking about and thought me alot about datasets and how to use them.  

However, the original ? "I need to be able to call the VB as if it were a query and to return the totals to the graph." was never quite accomplished as the required table output doesn't allow the flexibility of a query.  I can think of several ways to trigger updating the table using the dataset Function each time the graph loads, which would update the table with the custom data needed for the graph but this doesn't seem quite the thing to do.

I spoke with Garry Robinson, http://www.vb123.com/toolshed/tips-fx.htm who suggested a way to generate 12 rows of data as output from one row of input. At first I didn't see how to get YTD from this, but using the WHERE clause to select just the rows needed for YTD makes it work really well and its quite fast.

1)  set up a Table:
 
Mo12 with Mo Column and rows:
1
2
3
4
etc
12
 
And then a query:

SELECT TestSTkd.Yr, Mo12.Mo, sum(TestSTkd.GasP) AS GasP, sum (TestSTkd.OilP) AS OilP, sum(TestSTkd.OilC) AS OilC, sum (TestSTkd.H2OC) AS H2OC, Sum(TestSTkd.OthC) AS OthC
FROM TestSTkd, Mo12
WHERE Mo12.Mo between TestSTkd.Mo-0 and 12
GROUP BY TestSTkd.Yr, Mo12.Mo;

I haven't looked into an alternate for  "TestSTkd.Mo-0" (second to last line) but it solves a type mismatch error.

This provides Year-To-Date totals for each month.  The query (TestSTkd) needs to be enhanced to insure that the beginning data includes a full year or that year's data will not reflect actual YTD.

Again  Thanks for the help.

Elton
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now