Complex Expressions on Form Too Slow - Any Better Way?

[accessbeginner12]

Hi,



I'm building out a form that will eventually contain 150 or so calculations against a query with 2M+ records. The calculations are primarily Average IIfs and Count IIfs with multiple criteria. My problem is that I'm not even 1/4 finished with building out the expressions and already the sheet takes a really long time to finish calculating (5-10 minutes). My main issue is that it's hard to confirm that the expressions actually work. I've commented out a lot of them so they're not actually calculating but that is a bit of a pain.

I'm wondering if there is a different/better way to get to my desired end state of a summary table that contains all the desired calculations. This is my first time using Access and I had to start because Excel could not accomodate the number of records I have so I figure there could very well be a better way. A couple sample expressions are listed below. The summary table has 13 different metrics as the rows and 12 different categories as the columns.

Any suggestions are greatly appreciated, or even just confirmation that this is the only way to do it would be great too. If you see something I can be doing better with my sytax, improvements would be much appreciated as well.

Thanks!
Kevin

=Count(IIf([CAT] In ("F","G","H","I","J","K") And [TYP]="AF",1))/Count(IIf([TYP]="AF",1))
=Count(IIf([CAT] In ("F","G","H","I","J","K") And [TYP] In ("AF","DE","SR","KR","OP","RT"),1))/Count(IIf([TYP] In ("AF","DE","SR","KR","OP","RT"),1))
=Count(IIf([SCORE]="R" And [TYP] In ("AF","DE","SR","KR","OP","RT"),1))

I've only tested out the Average IIfs, I have not actually built them out yet

[June7]

Not sure this will help with speed but each of your IIf expressions does not have the value if False. Instead of Count try Sum

Sum(IIf([TYP]="AF",1,0))

[rpeare]

150 calculated fields is going to be a bitch on 2 million records no matter what you do, especially if the ORIGINAL records have many fields, keep in mind you're also limited to 255 fields in a table or query. I'm guessing your summary 'table' is actually a query (unless you're appending a summary to a temporary table), with that in mind what you MIGHT be able to do (and I'm not sure it would increase performance or not) is to either create a new table with a FK to your main table and perform your calculations per record in that secondary table. Then, as long as you perform regular updates the actual updates won't take too long and instead of running calculations at run time they'll be set up and it's just a matter of summarizing data in your calculation table. Normally I would not recommend storing calculated values in a table but if your data is not likely to change after the data gets to you then it may be a way to go.

[accessbeginner12]

Thanks for the suggestions! So....it turns out there's a very good reason Access has the DAvg and DCount functions - they speed the calculation process up tremendously. I completed all of the formulas the improper way, using avg(iif()) and the calculations took around 30 minutes to finish. Continuing to look into it, I figured I'd give the DFunctions a try - I'd tried to use them at the outset but since I wasn't familiar with the syntax in Access, I was unable to get them to work. Turns out they speed the calculations up dramatically. It still takes a few minutes to finish but it is a much much shorter time period.

Thanks again for your suggestions! Setting up the secondary table is a pretty creative solution, I may have to keep that one in mind as I get better with this program.

Kevin

[June7]

Interesting. I have read claims that the domain aggregate functions slow down processes. Some developers have a passionate disdain for the DA's. I have them throughout my app, especially DLookup in VBA code instead of opening a recordset. Not much use of others. I have one db with a form that had DLookup in textboxes and I could watch the boxes populate. I eventually changed the form to not use DLookup. I have used DA's in query and watched the calc refresh on each row as I scrolled the records, the scroll usually outpaced the refresh so would see blank fields down the screen.