Two simple queries or one nested query? 1

[lionelhill]

I'm trying to work out how to sum a numerical field "A" in one table, where a ThingID field in that table has no matching ThingID field in a second table.

My first thought was to do this in two queries: one would sum "A" for the whole of the first table; the other would be a straightforward join of tables 1 and 2 and sum "A" where ThingID is common to both; the difference between the two results is what I want.

Then I found out about frustrated left join queries, where I sum data in table 1 left-joined (using ThingID) to (SELECT ThingID from table 2 AS nice-new-name) WHERE nice-new-name is null. This should sum the data only where there is no corresponding entry.

I'm interested in which is the better approach? Is it better to package all the work in a single nested query, or is it just as efficient to have two simple queries? Are nested queries safe in Access? I did find the AllenBrowne site page:

http://allenbrowne.com/subquery-02.html

This was rather terrifying:

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.

This puts me off rather! Is it really that bad, or have things improved? I'd be grateful for any guidance.

 

[jrbarnett]

I must admit i have never experienced that in access. Building several layers of stacked queries, using a query rather than a table has worked for me in a number of occasions, both including things I have written myself and inherited from others.

Something like the following code seems to work for me and may be useful as a template for you:

SELECT Sum([table1].[A]) AS SumT1, Sum([table2.A]) AS SumT2, [SumT1]-[SumT2] AS Diff
FROM table1 INNER JOIN table2 ON table1.ThingID = table2.ThingID;

John

 

[lionelhill]

ooh, thanks for that. Another very neat approach to add to the toolbox; I will try it out. It's also great to be reassured that Access can handle stacked and unusual queries. I have a lot of respect for the AllenBrowne site, but it does deal with a vastly greater range of access queries, and access versions, than I'll ever touch, so I suppose logically it should have found a lot more obscure bugs too.