How do I make my queries run faster?

[sirskeezy]

Hi all,

I am in the midst of moving an Excel model and database into Access and have come up against the very annoying problem of my query taking wayyyyyyyy to long to run.

First to provide a better understanding of my situation, here is how my model in Access is structured:

First I have a table holding all my raw data

Second, I have multiple queries with each having 1 calculated field drawing on my original table
(I cannot combine these queries together which I will explain later)

Now grabbing these queries into a new query, I am trying to calculate a percentile value for each calculated field. At this stage my query already stalls when I try to run it. As of now, I do not have these queries linked together in my final query because doing so will mess with my percentile values (because there are null values that I needed to filter out)

Can someone please help me get past this annoying issue???

 

[sirskeezy]

I just realized that I did not explain why I have multiple queries instead of one query holding multiple calculated fields. It is because each calculated field has null values (which I can't count because it messes up my percentile calculations) and I need to set criteria "is not null" for me to calculate percentile value later. If i have all the calculated fields in one query, then I will not be able to set "is not null" for each calculated field without excluding some fields.

 

[barryna]

how long do each of your calculate queries take to run? Can you process your calculations using a module to populate additional fields in a new table that you can then simply join to to simplify your percentile query? I bet you are simply reaching the limits of Access. How many rows of raw data do you have? How many calculations? Joining too many things together in Access does make it unhappy.


Creator of

http://www.meltedjoystick.com

- Game Reviews, Game Lists, and much more!

 

[dhookom]

It's nearly impossible to make suggestions without you providing table structures, number of records, calculations, index information, and SQL statements.

Duane
Hook'D on Access
MS Access MVP