INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Two simple queries or one nested query?

Two simple queries or one nested query?

(OP)
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:

Quote:

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.



RE: Two simple queries or one nested query?

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:

CODE --> SQL

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

RE: Two simple queries or one nested query?

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close