Slow Access Query

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

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

@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

@Pat
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.

@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 :(