Performance of nested querydefs

[Javier Hernández]

Hi everyone. I'm trying to implement an ANOVA analysis for some data and it requires to go back and forth a little. I'm not a fan of nesting enough queries within the same SQL since the code ends taking dozens of lines and it is a pain to discover what goes wrong when it does and editing can be tiresome too. Also, as of Access 2003 (we're soon updating to 2013, AFAIK), the handling of parenthesis and braces are poor on some cases and queries sometimes don't compile.

So I'm creating several querydefs and using them in cascade to get the analysis done. Additional benefit of having to edit a querydef just once and have the change reflected on all further queries. However, this raises the question about whether it could affect the performance. I plan to modify the Filter property on runtime once people choose the data they want to peform the ANOVA on. I expect the last query to restrict the processing to the filtered data but how about the previous ones? Do they also get restricted automatically or they perform the calculus on all their domain and then the last query retrieves only the data it wants?

[orange]

Have you considered Excel for ANOVA? https://www.youtube.com/watch?v=0YOpPVVeLaI

[Javier Hernández]

Of course. I have done spreadsheets with formulas in cells in order to perform ANOVA "manually", but I want to explore the possibility of doing it on Access to improve several disadvantages:

• Copying and pasting the data from Access to Excel to do the ANOVA gets cumbersome when it has to be done a lot of times, and we already have a bunch of historical data.
• If the sample size varies, I have to adjust the ranges in the formulas (like "A2:A15" to "A2:A10"), making the process much slower and requiring more steps.
• Since for ISO reasons we need to have evidence resguarded, this means having to save at least one spreadsheet for each ANOVA performed. Spreadsheets gave to place to xls files and eventually the evidence founds itself scattered in a hell of multiple directories on different hard drives (server itself or local machines). Believe me, that has already happened.

Doing the ANOVA without ever getting out of Access sounds good to me since the analyisis would be performed on runtime and no evidence would be necessary to keep. Right now I'm only using the FInv and TInv function from the Excel library in a VBA module. I guess the whole data may be exported, processed via Excel and returned to Access with old office automation, but I'm not an expert on that. Is it complicated? I don't want to invest much time in a technology that we may be dropping as soon as we have Office 2013 in the uh...office.

[orange]

I haven't done ANOVA or any statistics really since university 30 + years ago, so don't have operational experience at this point.
However, you can call and use Excel functions from Access. My feeling is if you have vba to do the analysis, you could use most of that vba
and use Access and some Excel functions.
Can you post the vba? I'm sure there are others on the forum that do more statistics currently (or call/use Excel functions) than I do.

[June7]

I had some analysis that required matrix multiplication. I compared the calcs in Excel cells, in VBA calling Excel functions, and in VBA without Excel functions. I got different results with each. Which is 'correct'? I told my boss and we decided the VBA without Excel functions is good enough.

I don't quite follow the 2 questions of your first post. Are you applying filter criteria (static or dynamic) to each query in the sequence? They will automatically be applied when the final query is opened.