Microsoft Access Tips for Serious Users

Provided by Allen Browne, March 2007. Updated April 2010

Surviving Subqueries

This article troubleshoots eight common errors and frustrations you encounter with subqueries (listed at right.)

For a primer explaining what subqueries are and when to use them, see Subquery basics.

Query is not updatable

If the subquery is in the SELECT clause (i.e. if the results are displayed), the results are read-only.

Solutions

• If you do not need to see the results, uncheck the Show box in query design under the subquery. You can still use the subquery in the WHERE clause without making the results read-only.

• If you do need to display the results, use a domain aggregate function such as DLookup() or DSum() instead of a subquery. These execute orders of magnitude slower than a subquery, so you may wish to use them on the form/report rather than in the query. Alternatives such as ELookup() are double the speed, but still slow compared to a subquery.

Error: "At most, one record can be returned from this subquery"

If the subquery might find multiple matches, the main query has just one cell to display the results, so you get this error.

This one can be hard to test: you don't get the error until particular combinations of data result in multiple matches, so the query may work for months, and then suddenly fail. It is therefore crucial to design the subqueries to avoid this issue.

Solutions

• Use the TOP predicate in the subquery, e.g.
      (SELECT TOP 1 MyID FROM ... )

• TOP 1 is not enough! If two records tie, Access cannot decide between them. Unlike some other databases, it returns both. The trick is to give JET a way to choose between equals. To do that, include the primary key in the ORDER BY clause like this:
      (SELECT TOP 1 OrderDate FROM Orders ORDER BY OrderDate DESC, OrderID)
  Since the primary key value is unique, Access can decide which is the TOP 1, even if you have multiple orders on the same date.

• Another alternative is to use aggregation, e.g.
      (SELECT Max(OrderDate) AS MostRecentDate FROM ... )
  This is somewhat less flexible, but preferred where it works.

Error: "Multi-level group by not allowed"

You spent half an hour building a query with subquery, and verifying it all works. You create a report based on the query, and immediately it fails. Why?

The problem arises from what Access does behind the scenes in response to the report's Sorting and Grouping or aggregation. If it must aggregate the data for the report, and that's the "multi-level" grouping that is not permitted.

Solutions

• In report design, remove everything form the Sorting and Grouping dialog, and do not try to sum anything in the Report Header or Report Footer. (In most cases this is not a practical solution.)

• In query design, uncheck the Show box under the subquery. (This solution is practical only if you do not need to show the results of the subquery in the report.)

• Create a separate query that handles the subquery. Use this query as a source "table" for the query the report is based on. Moving the subquery to the lower level query sometimes (not always) avoids the problem, even if the second query is as simple as
      SELECT * FROM Query1;

• Use a domain aggregate function such as DSum() instead of a subquery. While this is fine for small tables, performance will be unusable for large ones.

• If nothing else works, create a temporary table to hold the data for the report. You can convert your query into an Append query (Append on Query menu in query design) to populate the temporary table, and then base the report on the temporary table.

Using aliases

If your subquery uses the same tables as the main query, you have a problem. The subquery can read the fields in the main query, and the fields (from the same table) have the same names. How do you distinguish which ones you are talking about?

Often the subquery needs to use the same tables as the main query. Since the subquery can read the main query fields, there is the potential for confusion about which fields you are referring to.

Solution

Alias the table in the subquery (or in the main query.) Be sure to use the table/alias name every time you refer to a field to avoid misinterpretation.

This example uses the name Dupe as the alias for the duplicate copy of the table in the subquery, so it can compare the subquery record with the main query record:

Error: "Query too complex"

JET gives this generic message any time it cannot pinpoint what's wrong. The cause can be wrong delimiters, misinterpreted data types, mismatched brackets, using reserved words as table or field names or aliases, confusion from Name AutoCorrect, or a host of other possibilities.

Solutions

• In most cases, the solution it to figure out which part Access does not understand. Check the data types match in expressions, joins, and criteria. Explicitly typecast where needed. Explicitly declare parameters, so JET knows their type.

• Verify your names (tables, fields, aliases) are not on the reserved words list. Add square brackets around any suspect names, and explicitly specify their source table, e.g. "SELECT [Table1].[Name] FROM ... " rather than "SELECT Name FROM ... "

• Make sure Name AutoCorrect is off. Then compact/repair the database.

• If necessary, break the query down to find the problem. Drop half of the WHERE clause, omit the GROUP BY clause until the query works. Then start adding thing back until you pinpoint the culprit.

• Occasionally, the query actually is too complex: too many UNIONs, more than 32 tables, 100 ANDs in the WHERE clause, or nesting queries more than 50 levels deep. For a list of the limits, open Help in the main Access window, and enter specifications. In Access 2007, go to help topic HA10030739.

Performance problems

Complex subqueries on tables with many records can be slow to run. As a desktop database, JET does not give you the ability to specify an order of execution, but you do have some alternatives.

Solutions

• Use stacked queries instead of subqueries. Create a separate saved query for JET to execute first, and use it as an input "table" for your main query. This pre-processing is usually (but not always) faster than a subquery. Likewise, try performing aggregation in one query, and then create another query that operates on the aggregated results. This post-processing can be orders of magnitude faster than a query that tries to do everything in a single query with subqueries.

• Where performance matters, avoid nested subqueries (a subquery that contains another subquery.) They will almost certainly be the slowest approach in JET.

• Experiment with the SQL: you may be able to keep the subquery, but coerce it to execute differently.

• Use the undocumented ShowPlan to gain insight into the JET's execution plan. The article does not cover Access 2007 or later, but the registry key is:
      [HKLM\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Debug] "JETSHOWPLAN"="ON"

Access crashes

Subqueries can crash Access. You know the drill, "... closed down by Windows. Sorry for the inconvenience."

Unpatched bugs plague all versions of JET, so the extra complexity of a subquery can kill Access quite easily. Since we are talking about multiple diverse flaws, there is no single solution. The (rather unsatisfactory) workaround is to figure out what JET is having problems with, and take a different approach that does not trigger this particular bug.

Solutions

• One of the most common is JET's inability to handle Nulls in Yes/No fields. Using Nz() or IIf() constructs to explicitly convert the Null to False avoids this one if you perform the conversion at a low enough level, i.e. before the subquery or stacked query returns the Null or attempts to operate on it (comparison, sorting, grouping, ...)

• Separate articles deal with JET's unpatched bugs in the PARAMETERS clause, SELECT clause, DISTINCT predicate, FROM clause, WHERE clause, GROUP BY clause, and ORDER BY clause.

• Break the query down into multiple stacked queries instead of trying to do it all in one. (Unfortunately this doesn't always work: you may find the lower level query with the subquery works, but the higher level query based on that still fails.)

Unknown JET error

When working with subqueries, you may receive the error message:
    Unknown JET error
or in Access 2007 or later:
    Unknown Access database engine error

This tends to happen when you use a query with a subquery as a source "table" for another query, and join it to other tables.

Solution

There can be several causes for this error. The JET query optimizer is one of them. For this case, force JET to materialize the query by adding TOP 100 PERCENT to the underlying query (not subquery.)

For example, if the lower level query is:
    SELECT a.ID, (SELECT Count(b.ID) AS HowMany FROM b) AS HowMany FROM a;
change it to:
    SELECT TOP 100 PERCENT a.ID, (SELECT Count(b.ID) AS HowMany FROM b) AS HowMany FROM a;

Incorrect results

Sometimes the subquery results are wrong, or the the query that relies on the subquery results goes haywire.

A typical example involves a query with some outer joins (at least 3 tables) and a subquery. JET returns the wrong record from the subquery, or matches the subquery results incorrectly, or performs the joins in the wrong order.

Solution

Again, we are talking bugs in JET, so we cannot give you a single solution for all cases. Some suggestions:

• If the bug is with the JET optimizer, you may be able to bypass the optimizer and work around the bug with an arcane construct such as this:
      (SELECT TOP 100 PERCENT ... )

• It may be necessary to drop the subquery, and use stacked queries or even temporary tables.

Conclusion

Subqueries are amazingly flexible and incredibly frustrating. They have an important place in your repertoire, but you must have other tricks up your sleeve as well.

In general, subqueries are slower and less stable than JOINs. You must test them with real-world data samples to know how they will perform.

They are certainly worth pursuing and mastering for the flexibility they provide.