Microsoft Access Tips for Serious Users

Provided by Allen Browne, March 2007. Updated April 2010


Contents:

Query is not updatable

Error: "At most, one record ..."

Error: "Multi-level group by not allowed"

Using Aliases

Error: "Query Too Complex"

Performance problems

Access crashes

Unknown JET error

Incorrect results

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


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


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


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:

SELECT VehicleLogID, LogDateTime, VehicleID, Mileage,
   (SELECT TOP 1 Dupe.Mileage FROM tblVehicleLog AS Dupe 
   WHERE Dupe.VehicleID = tblVehicleLog.VehicleID        
     AND Dupe.LogDateTime > tblVehicleLog.LogDateTime    
   ORDER BY Dupe.LogDateTime, Dupe.VehicleLogID)          AS EndMileage
FROM tblVehicleLog;

 


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


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


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


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:


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.


HomeIndex of tipsTop