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

    Union queries and distinct records (Access ay version)

    I have spent some time trying to reconcile figures in different reports.

    One of them uses a union query :

    sql statement1 union sql statement2

    Used by itself sql statement1 returns 318 records
    Used by itself sql statement2 returns just 1 record.

    However the union query returns just 276 records.

    Explanation : Not all the records returned by sql statement1 are distinct. The union query returns each of these only once.

    When I add an additonal field (that I don't need for the report) the union query returns 319 records.

    I would have expected that any record returned by both statement1 and statement2 would only be counted once, but I was surprised to find that duplicates in those returend by statemet1 were eliminated.
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union queries and distinct records (Access ay version)

    A union query first combines all records from the contributing parts, and then weeds out the duplicates. It doesn't check the origin of the duplicates. If you don't want unique records, change UNION to UNION ALL.

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

    Re: Union queries and distinct records (Access ay version)

    Thanks Hans

    I don't think I have met UNION ALL before. But maybe I have just forgot.
    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
  •