Slow Access Query

Hi all,
I've been battling lots with a complex query that is now turning out to be way too slow.
I've attached a stripped out database if someone is willing to put some fresh eyes on the problem.

1. The Slow Query is called "FPStatus-New"
2. I'm sure the problem lies with QA and QI getting their data from Query called: "QualiCompTypeFinal"
3. I created a table to test all results called "TestMe" - So the final query should deliver exactly the same data as contained in "TestMe"

Would really appreciate some help with this.

Thanks a lot
Pierre KAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
I'm not sure I agree that the problem is getting data from QualiCompTypeFinal.  You query, FPStatus-New, has a significant number of calculated columns with conditional and Switch statements.  For every record in the dataset, and for each of those columns, a calculation/decision is being performed.

When I run QualiCompTypeFinal by itself I get records returned almost immediately.
When I run FPStatus-New it takes seconds to generate the records and there are only 8!  This is why I suspect the calculated column expressions is where your slowness is coming from.

Not sure yet on what to suggest to you.  Am hoping/expecting another expert to chime in with some solid advice ;)
OM Gang
NEVER, EVER use domain functions in a query.  Change the DLookups() to either join directly to the lookup table or, if necessary, create a query with criteria and join to that.

Suggestion:Prefix either the table names or the query names so it is easy when looking at a query to identify if the references are to tables or queries.  I prefix both.  tbl for tables and q for queries.  Also, avoid spaces and special characters in table and column names.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pierre KAuthor Commented:
Thanks - I do agree - but test by taking out the QA and QI columns in the slow query and once you skip the parameter errors you will see the result is very fast.
Compared to other queries, the QualiCompTypeFinal one takes a bit of time.

Look this was a very difficult task to get the result I needed but in between I'm sure I created duplication where it was not needed but you know the feeling when you under stress for days to get the thing to run with loads of rules set by the Client hence all the IIF and Switching going on

QA and QI makes use of a slow DLookup function to get data while the query is opening (on the fly) and I'm sure if I can just get that data more "direct" the query will be much faster but the relationships in driving me crazy

Thanks again
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Pierre KAuthor Commented:
@Pat - I hear you - as per my post above the problem is with the DLookup but I just can't seem to get the data in otherwise. That is probably the main reason for posting here eventually as I'm at the end now
Pierre KAuthor Commented:
I'm acepting your sulution simply due to the fact that I know you are correct that one must not use DLookup on the fly but I was under deadline stress to deliver something for the Client to test.
Due to my limited time at the moment I resolved to something you said in your comment which set a light bulb on.

"create a query with criteria and join to that"

For now I'm running a make table query on the event that triggers the form using the slow query to open.
The slow query is now super fast since it points to a "static" table.
Will re-visit this later, but at least the Client can test tonight with a fast enough app.

Thanks again
You don't need the make table query.  Access uses tables and queries interchangeably for most operations.  Create a select query with the criteria that your domain function is using.  Save the query.  Then replace the domain function with a join to the new query.  Using make-tables causes database bloat.  Access cannot recover space until you run a compact and repair.   So every time you run the make-table, new space is allocated.  The original space is not reused.  It is abandoned.  The same problem occurs if you use delete and append queries.  You append a thousand rows.  You delete them.  You append another thousand rows - they do not reuse the space used by the original records.  New space is allocated.  That is bloat and you have to remember to compact regularly to get rid of it.
Pierre KAuthor Commented:
@Pat - thanks yes - but if you look at my db attached you will see that it is no easy task to create a select query to give me the results I need.
If you have time and energy maybe you can give it a shot - I'd be most appreciated since I closed the call already.
In theory I agree with you but the proper select query is above my skills at the moment :(
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.