Link to home
Create AccountLog in
Avatar of gigifarrow
gigifarrow

asked on

Query Calculations taking to long to execute.

I have a query in my form called Percentages. It calculates at what percentage a vehicle is  based on the check mark. Each check mark is worth a certain amount.


Above  this I have a summary that does calculations in textboxes.



It is taking almost 4 minutes for the all the calculations to come up. Also some of the form wont show up either. I have 300 records.  I have no Idea how to fix it.



I have 300 records in the table, however in my sample I put only 19
Below is a sample
HelpWithQuery---NewModRevision30.zip
Avatar of oleggold
oleggold
Flag of United States of America image

why don't just have select  check_mark/amount * 100
Avatar of gigifarrow
gigifarrow

ASKER

Sorry I dont understand what you mean.
Avatar of IrogSinta
This isn't the answer yet to your question on slowness.  I just wanted to find out:
Why do you have a separate table called tblPctCode?  Couldn't you have just added the field PctCode field in your tblFortCarsonFullup table?  
Secondly, at the end of your formulas, you divide by 100 then multiply by 100.  What is your purpose here?  
Your first question why do have tblPctCode in separte table.

So the check marks would calculate I thought that is how I was suppose to set it up.


Second question about why I diveded then multiply by 100 is because that is how I was shown to make it to percentage.




If you know a better way let me know.
I tried the autocorrect.
I tried the compact and repair.


Nothing, still slow.

Could it be my table function? The table Fort Carson full has been used for a year didnt want to mess with it because of my append. queries. but if I need to change it to make it faster I will.


In the form I have a built in query with Percentages = Function tpct(p) As String Which Looks like this:  Percentages: tpct([Production])

Name                        fields

tblPctCode              Production
                               PctCode        


tblFormula                PctCode
qryFormula               Fomula


tblFortCarsonFullup    Production
                                   All check Mark Names
Module

Function tpct(p) As String
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
strSQL2 = "Select formula from qryFormula where production = " & p
Set rs2 = CurrentDb.OpenRecordset(strSQL2)
rs2.MoveFirst
f = rs2!formula
strSQL = "select " & f & " as pct from tblFortCarsonFullUp where production = " & p
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveFirst
tpct = rs!pct
rs.Close
rs2.Close
End Function
Could it be because of the text field calculating queries?
With regards to my earlier questions, if you did added tblPctCode because you did not want to mess with tblFortCarsonFullup, then I can see your reasoning there.  But if you want one less table to deal with then inserting PctCode in tblFortCarsonFullup and using that table instead in qryFormula will work just as well.  

When multiplying and dividing a number by the same number, the result is the same.  If your intent was to use the format property to display the percent symbol (%), then you should just divide by 100 and change your format property to Percentage.

The slowdown is obviously from your tpct function.  Would you mind uploading a sample with more data so I can see what you're seeing speedwise?
Here is an example of all the data I have currently. I have deleted some fields because i cant show that data. when I take out the text boxes with the equivelant fields it moves real fast.  so I dont think it is the function. I think it is my calculations in the text boxes.


This has about 280 records.
HelpWithQuery---NewModRevision30.accdb
If I change it to format to Percentage I get a error because it wont calculate correctly when I put format.
Rather than have all those dcounts and dsums in your form, you should try creating a query that gives you all that information and then create a subform from this query and add that to your form.  If you're not able to figure out this query, I can put one together later but right now I'm going out for a family get together and won't be back till late.
Here is a revised version using a query as a subform.  Go ahead and open frmMain.
NewModRevision.accdb
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
One more suggestion.  You could make a table of Weights in your database similar to this and when your form opens, have a procedure populate a global array which will be used in your module instead of the constants.  That way it's easier to change your weights and check their sums.
Book1.xls