Solved

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

Posted on 2002-05-28
17
328 Views
Last Modified: 2008-03-10
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
Comment
Question by:katzwhite
  • 9
  • 8
17 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7042029
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7042039
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
 

Author Comment

by:katzwhite
ID: 7042047
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
 

Author Comment

by:katzwhite
ID: 7042070
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
 

Author Comment

by:katzwhite
ID: 7042081
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7042084
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7042110
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
 

Author Comment

by:katzwhite
ID: 7042139
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 54

Expert Comment

by:nico5038
ID: 7042215
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
 

Author Comment

by:katzwhite
ID: 7042256
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
 
LVL 54

Accepted Solution

by:
nico5038 earned 300 total points
ID: 7042717
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
 

Author Comment

by:katzwhite
ID: 7044797
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
 

Author Comment

by:katzwhite
ID: 7044877
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7044903
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
 

Author Comment

by:katzwhite
ID: 7044987
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7045087
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
 

Author Comment

by:katzwhite
ID: 7048729
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now