Perform subquery first, and then only apply outer query (with outer query clauses) on results of subquery, for faster, better queries
When I use a subquery in a query, I expect the outer query to only work on the results of the subquery. However, today, it appears that the outer query (the WHERE clause in particular), is not being strictly run on just the results of the subquery. Instead, the join in the subquery appears to be being ignored, and the WHERE clause is run on the much bigger dataset of the subquery minus its join. This is counter-intuitive and probably incorrect. Please fix this. I had a subquery that only returned two rows, but it appeared Access was performing an outer query function on the subquery without its join which happened to be millions of rows--it was very slow.
