Results 1 to 6 of 6
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have been asked to help fix up a db created by someone else.

    Typically if I want to add additional fields to a Totals Query, I do it in multiple stages. The first query groups by one or two fields, then subsequent queries join this query back to other tables to allow other fields to be added.

    The designer of this db adds all the tables to the first query, then puts in lots and lots of 'group by's. I have seen queries with 30 group by fields.
    I don't like them, but they do seem to work.
    What are the argument against using multiple 'group by's?
    • Maybe it is unreliable? Could unusual combinations of values lead to the wrong output?
    • Maybe it is slow?
    • Is there a risk that the query will become too complicated for Access to evaluate?
    • It is not very elegant?
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    My reactions:
    • Maybe it is unreliable? - I don't think so.
    • Maybe it is slow? - Access has a query optimizer ("Rushmore") built in, so unless that fails, it shouldn't make much difference whether you use one query or several.
    • Is there a risk that the query will become too complicated for Access to evaluate? - Possibly, there are situations in which a single query will fail, but in my experience that usually has to do with criteria on calculated columns.
    • It is not very elegant? - I don't think so.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks for your reply.

    So you don't see anything wrong with queries like the one in the attachment?

    My reaction was that I did not like them, but when I thought about why I did not like them, I did not know what to say!
    Attached Files Attached Files
    Regards
    John



  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='767520' date='26-Mar-2009 11:09']So you don't see anything wrong with queries like the one in the attachment?[/quote]
    I use frequently such queries without a problem. I don't see anything wrong with that.
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Like Francois, I see no problem with using such a query.

    Note: There should be relationships with enforced referential integrity between the tables!

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    OK

    Thanks to both of you.

    The demo I posted was quickly put together to illustrate the type of query I was talking about, so I just did the bare minimum to make the query work. (i.e no relationships defined.)
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •