Nested SQL Statements causing slowness and errors

[Joel Maxuel]

I am working on a query for one of my reports. It takes a percentage of
selected diagnoses (and some diagnosis groups). To save the number of
queries, I have nested a SQL statement for each diagnosis (diagnosis group).
For a single diagnosis, I use the following expression:
Primary Dx Schizoaffective Disorder: ((SELECT
Count([qryuAxisIDiag].[DiagValue]) AS Count FROM [qryuAxisIDiag] WHERE
((([qryuAxisIDiag].[DiagDetail])="Schizoaffective Disorder")))/[Total Number
of Clients.CountOfGender])
For a diagnosis group, I use:
Primary Dx PD: ((SELECT Sum([qrygAxisDxPD].[DxCount]) FROM
[qrygAxisDxPD])/[Total Number of Clients.CountOfGender])
Which links to another query with the following:
SELECT qryuAxisIDiag.ID, IIf(Count([DiagValue])>0,1,0) AS DxCount
FROM qryuAxisIDiag
WHERE (((qryuAxisIDiag.DiagValue)="Personality Disorder"))
GROUP BY qryuAxisIDiag.ID;

Note that I use the IIf(Count([DiagValue])>0,1,0) as (in this case) a
patient may have multiple personality disorders, but want to return that a
personality disorder is present. Also, qryuAxisIDiag is a union query
listing all diagnoses for each patient.

In total, there are 26 fields in this query and have found that it runs very
slow and often get the "Cannot open any more databases" error. At that point
I will have to close and reopen the database. Design view of the report is
horribly slow as well *(and sometimes cannot find the controlsource of the
fields while in design view).

I was hoping to simplify the query, by using Sum() and mathematical
expressions instead of the nested SQL statements, but I would get the
following error message:
"You tried to execute a query that does not include the specified expression
'Sum(Iif(>0,1,0))/[CountOfGender]' as part of an aggregate function."

Short of creating dozens of additional queries to break up the calculation
(which I want to avoid), any ideas on fixing this problem?

 

[jlute]

How many forms are open when you run the query? If you have several
open that have lots of lookup controls then that can contribute to the
"cannot open any more databases" failure. That's typically the problem
I run into.

 

[Joel Maxuel]

The only form open is a "Main Menu" which has several buttons, and three
unbound controls, one being a combobox where you can select patients, and two
date fields used to report on specific patients that have an admission date
between say, Jan 1 and Dec 31 2007.

I should add that ID is the primary key, each value being a different
patient (and date admitted). qryuAxisIDiag has the following fields:
ID
DiagOrder - Separates the general Dx type (and which text field the record
came from) - does not get used in this example
DiagValue - The Diagnosis group (i.e. "Anxiety Disorders")
DiagDetail - The specific diagnosis (i.e. "Panic Disorder")

 

[jlute]

What version of Access are you using? I believe pre-2003 versions have
fewer posible connections possible which more readily leads to the
cannot open any more db's.

I use 2003 and can tell you that this has been a problem for me for
quite some time. I recently developed an intensely complicated query
that I would say is far more complex than what you've posted. It has
64 columns and LOTS of subqueries and calculations going on - I mean
LOTS. I have to run it from my Main Menu, too which has a few more
controls than what yours does.

Your subquery idea sounds feasible and the error:
"You tried to execute a query that does not include the specified
expression
'Sum(Iif(>0,1,0))/[CountOfGender]' as part of an aggregate function."

I believe means that you need to remove Totals from your query - but I
could be wrong. Maybe one of the MVP's will confirm.

 

[Joel Maxuel]

I use Access 2003, but the database is saved in 2000 format (to be more
compatible for the clients that will use it). Just for fun, I tried out the
report in Access 2000, and I don't get the crippling error message, however
it still takes 12 seconds to load (split database, front end on local
harddrive, back end on network share). As a comparison, opening a form with
over 100 bound controls takes less than a second.

What version of Access are you using? I believe pre-2003 versions have
fewer posible connections possible which more readily leads to the
cannot open any more db's.

I use 2003 and can tell you that this has been a problem for me for
quite some time. I recently developed an intensely complicated query
that I would say is far more complex than what you've posted. It has
64 columns and LOTS of subqueries and calculations going on - I mean
LOTS. I have to run it from my Main Menu, too which has a few more
controls than what yours does.

Your subquery idea sounds feasible and the error:
"You tried to execute a query that does not include the specified
expression
'Sum(Iif(>0,1,0))/[CountOfGender]' as part of an aggregate function."

I believe means that you need to remove Totals from your query - but I
could be wrong. Maybe one of the MVP's will confirm.

The interesting part is that if you take out the denominator the query
calculates without error. This gives me an idea. I may try calculating the
percentage as a separate query or even as an expression in each report
control. Will follow up one this.

 

[Joel Maxuel]

I think I've got it. I moved the percentage expressions to the report, thus
avioding the aggregate error messages. For each diagnosis being used in the
report, I broke apart the nested SQL statements and replaced them with
something like "IIf([DiagDetail]="Schizoaffective Disorder",1,0)". I threw
the ID field in the query as well. I then created another query that ran the
sum of each field of the previous query (the raw data of the firsgt had 6
lines for each ID, this query wraps it up to 1 per ID). Finally, I added the
fields to the main query with the dianosis groups (mentioned in the previous
posts) where everything is added together.
All in all, the report load time decreased from 12 seconds to 4 seconds, and
have not experienced the "cannot open any more databases" error since.
The only limitation now is that modifying the report in design view is still
lagging. This won't be much of a problem as I won't be editing this much,
but I do recall broken reports can be fixed by exporting its configuration to
a text file, deleting the report, and recreating it with the text file. Is
anyone familiar with this?

 

[jlute]

Glad to see that you got it!

Hopefully someone can help you with your other question.

 

[Joel Maxuel]

I was able to find te VBA script that exports and imports reports via text
files, and the performance has not changed. I also looked into a persitent
connection (found http://www.granite.ab.ca/access/performanceldblocking.htm),
however did not notice any change. I have a combo box on my main menu that
brings up a list of patients so that may have been my persistent connection
all along.