Help to improve efficiency of VBA
- Thread starter simon03
- Start date
Well, conceptually, it's relatively straightforward to achieve in a pivot. I just imported the spreadsheet as is, created a basic pivot:
And then messed around with that basic output to do the calc:
The SQL could be clearer, my apologies for that. The downside is that Access doesn't have an UNPIVOT to convert it back into a single row per month, but it was interesting playing with it so far.
Code:
TRANSFORM SUM(1 + tbltest.num / tbltest.den) AS SumOfPart
SELECT
tbltest.name
,YEAR(tabletest.[Date]) AS ryear
FROM tbltest
GROUP BY
tbltest.name
,YEAR(tbltest.[Date])
PIVOT MONTH(tbltest.[date]);And then messed around with that basic output to do the calc:
Code:
SELECT
initial_Crosstab.name
,initial_Crosstab.ryear
,[1]*100 AS jan
,[jan]*[2] AS Feb
,[feb]*[3] AS mar
,[Mar]*[4] AS apr
,[apr]*[5] AS may
,[may]*[6] AS jun
,[jun]*[7] AS jul
,[jul]*[8] AS aug
,[aug]*[9] AS sep
,[sep]*[10] AS oct
,[oct]*[11] AS nov
,[nov]*[12] AS [dec]
FROM initial_Crosstab;The SQL could be clearer, my apologies for that. The downside is that Access doesn't have an UNPIVOT to convert it back into a single row per month, but it was interesting playing with it so far.
Last edited:
That's great but two problems:
1) with my DB if I run the first block over all records in my DB I get an overflow error. If I include a WHERE condition between a range of dates, then it works fine and creates a pivot table.
2) This is pretty bad but: how do I combine the two SQL scripts that you provide me? It looks like the second one calls the first one as 'initial_Crosstab' but I have not declared that anywhere ...
Sorry for the basic questions...
1) with my DB if I run the first block over all records in my DB I get an overflow error. If I include a WHERE condition between a range of dates, then it works fine and creates a pivot table.
2) This is pretty bad but: how do I combine the two SQL scripts that you provide me? It looks like the second one calls the first one as 'initial_Crosstab' but I have not declared that anywhere ...
Sorry for the basic questions...
If you create a query, switch to SQL view and paste that sql, save it called "initial_crosstab" and create that second query in the same way, you can call that one whatever you like. Moving forwards it should be possible to combine them into a single query, I say should as Access can be a bit touchy around subquerying and I haven't tested it so for now I created two queries to play around with the concept.
As for the overflow error, does the "den" column contain any 0 or NULL values? There is no catering for a divide by zero in that initial calc which may be the issue.
As for the overflow error, does the "den" column contain any 0 or NULL values? There is no catering for a divide by zero in that initial calc which may be the issue.
Last edited:
This is one of those rare situations where saving the calculated value is necessary. Yes you can convert the crosstab back to a SELECT query by using a bunch of Union statements but this isn't flexible.
You can do one of two things:
1. Perform the calculation in Excel and import it into your db
Or
2. Do it in a recordset as you originally had it, but this time we can write a better and quicker function.
I think option 1 is your best option because that's an easy run for Excel.
You can do one of two things:
1. Perform the calculation in Excel and import it into your db
Or
2. Do it in a recordset as you originally had it, but this time we can write a better and quicker function.
I think option 1 is your best option because that's an easy run for Excel.
Yes, you are right. The problem is the 0 in Den. If I change the initial formula in:
It works well. I mean does not create the overflow error.
I was trying to work with an IIF condition but it does not look like I am allowed to use it with TRANSFORM.
The rest works great though!
Code:
SUM(1 + tbltest.num /( tbltest.den +1)) AS SumOfPartIt works well. I mean does not create the overflow error.
I was trying to work with an IIF condition but it does not look like I am allowed to use it with TRANSFORM.
The rest works great though!
You can use an iif in the transform, unfortunately I'm typing on a phone at the moment so won't attempt it now.
The +1 you added will cater for zero values but not NULL. If NULL shouldn't be present constrain it on the column by setting required = yes
The +1 you added will cater for zero values but not NULL. If NULL shouldn't be present constrain it on the column by setting required = yes
To be honest I'm not very happy with the horizontal data but I guess that there is a way around to transform it as I like (maybe using another Query?)
For the IIF statement I have solved it, it works as follows:
Although it is not exactly what I had in mind, this first release is a good progress compared to my previous attempt. More suggestions are of course very welcome!
Thanks
For the IIF statement I have solved it, it works as follows:
Code:
SUM(IIF(tbltest.den=0,0,1 + tbltest.num / tbltest.den )) AS SumOfPartAlthough it is not exactly what I had in mind, this first release is a good progress compared to my previous attempt. More suggestions are of course very welcome!
Thanks
Suggestions and comments are in post #26.
Having mulled it over some more and depending on circumstance, this may be a situation where it is actually preferable to hold the calculated value stored.
The reason why everyone leaps in and says Don't hold a calculated value in a table (based on other values on that row) is that there is a risk that somewhere down the line changes are made to one or more of the input values on that row which means that the calculated value is now not reflective of the apparent inputs.
Now we obviously still don't have much context around what this data actually is and what you're doing with it, but I'll outline an approach for calculating the data using a couple of queries and some VBA to hold it all together.
Based on the sample data so far it is relatively straightforward to get the necessary information to perform the calculation using a JOIN based on an Offset of the date. i.e.
So what we are doing is taking the Month we want to calculate (t1) and joining it to the previous month (t2) to obtain its calculated field, assuming the previous month is calculated we now have everything we need to perform the calculation.
Obviously for this to work, we need our "seed" calculations, i.e. the January months to be calculated first, we can sort this out with a basic query, in this case, this updates all rows with a January date with the basic calculation:
That's every row for January seeded. Now that we have our seed calc we can sort out the rest with a query based on the example above and a bit of code, again, we'll calc the whole thing:
I'm going to use a parametised query.
The query: "UPDATE_Table_calcField_ByMonthNo"
(apologies for the formatting, I let access create this one)
and the basic code that now controls the calcs:
These are pretty basic examples to illustrate the point, theres no error handling in either code or SQL (like our divide by zero error earlier) and it is recalculating the entire table when it runs. But I think the code is a lot easier to understand, performance remains to be seen, but a couple of indexes on the name and date columns may help improve the query performance.
The reason why everyone leaps in and says Don't hold a calculated value in a table (based on other values on that row) is that there is a risk that somewhere down the line changes are made to one or more of the input values on that row which means that the calculated value is now not reflective of the apparent inputs.
Now we obviously still don't have much context around what this data actually is and what you're doing with it, but I'll outline an approach for calculating the data using a couple of queries and some VBA to hold it all together.
Based on the sample data so far it is relatively straightforward to get the necessary information to perform the calculation using a JOIN based on an Offset of the date. i.e.
Code:
SELECT
t1.name
,t1.Date
,t1.Num
,t1.Den
,[b]t2.[/b]CalcField
FROM table t1
INNER JOIN table t2 ON
t1.name = t2.name
AND t1.date = DATEADD("m",1,t2.date)So what we are doing is taking the Month we want to calculate (t1) and joining it to the previous month (t2) to obtain its calculated field, assuming the previous month is calculated we now have everything we need to perform the calculation.
Obviously for this to work, we need our "seed" calculations, i.e. the January months to be calculated first, we can sort this out with a basic query, in this case, this updates all rows with a January date with the basic calculation:
Code:
UPDATE table
SET calcField = (1 + Num / DEN) * 100
WHERE MONTH(date) = 1That's every row for January seeded. Now that we have our seed calc we can sort out the rest with a query based on the example above and a bit of code, again, we'll calc the whole thing:
I'm going to use a parametised query.
The query: "UPDATE_Table_calcField_ByMonthNo"
(apologies for the formatting, I let access create this one)
Code:
PARAMETERS [@MonthNo] Int;
UPDATE tbltest AS t1
INNER JOIN tbltest AS t2 ON
(t1.name = t2.name)
AND (t1.date = DATEADD("m",1,t2.date))
SET t1.Calcfield = (1+[t1].[num]/[t1].[den])*[t2].[calcfield]
WHERE
(((MONTH(t1.date))=[@MonthNo]));and the basic code that now controls the calcs:
Code:
Sub test()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim i as Integer
Set db = CurrentDb
'do the January seed data first
Set qdf = db.QueryDefs("UPDATE_table_JanuaryCalc")
qdf.Execute
'Now Update the months
Set qdf = db.QueryDefs("UPDATE_table_calcfieldbyMonthNo")
For i = 2 To 12
'set the month to update
qdf.Parameters("[@MonthNo]").Value = i
qdf.Execute
Next
End SubThese are pretty basic examples to illustrate the point, theres no error handling in either code or SQL (like our divide by zero error earlier) and it is recalculating the entire table when it runs. But I think the code is a lot easier to understand, performance remains to be seen, but a couple of indexes on the name and date columns may help improve the query performance.
Last edited:
It really shouldn't, you're adding a month to the prior date so that it matches the month that you're about to manipulate and gives you the value necessary to perform the calculation.
I.e. if you're about to calculate March you need February's calculated data so we need to add a month to February for the JOIN to work.
If you manipulate the date in t1 instead then you need to use -1.
I debated whether to make it a LEFT JOIN, I think it depends on what your reasoning is for making it a LEFT JOIN actually is. The risk in the current query is that you accidentally calculate January using December's numbers, that could happen if you run the query outside of the code.
If there is no prior month then the calculation cannot work so whether it's a LEFT JOIN or an INNER JOIN is incidental, you end up with NULL as a result in any case and that is something that probably should be checked for.
I'm not trying to pick an argument, but part of the reason for putting the example the way that I did was not so much to give an/the answer as to propose the framework of a method that would achieve the stated aims.
Where's that coming from
Like I said, I only glanced through but I can see that you have already initialised all of January before moving on to the next couple of months. Good thinking!
Long day of being challenged unnecessarily around making perfectly reasonable suggestions elsewhere and it spilled into here. My apologies.Where's that coming from
I was only highlighting what I thought was a slight oversight, but it turned out to be right.Similar threads
