Results 1 to 7 of 7

Thread: Union Query

  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Can a union query be used with 3 select queries? I have 3 queries that give me the results for Allocated, Completed, and Outstanding, and I want to bring them all together for a report.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Easily done - just add each new query like it was a new section. The trick is to ensure you have the same number of output fields in each query and also make sure they have the same name in each section. I also found it best to get create the union query with 2 select queries first and get that working, then add the third query.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I agree but it sounds like you don't really need a union.
    Instead you could just have a single query where the criteria is "Allocated or Completed or Outstanding"

    I should add that I can easily imagine situations where I am wrong about this.

    Usually you only need a union if the data is coming from different tables.
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks John, Felton. I think I've got that part sorted. I used a union to join two of the queries, then added that union to another query which seems to work. I can't see how a single query would work. I need to show the Allocated, Completed, Outstanding for each Actionee. There's another addition to the problem now. They want to know, how many of the Allocations, were issued in the last 7 days. I created another query, which shows just the people who have actions allocated to them this week, but doesn't show the figures for those who haven't.

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    UNION ALL is usually superior to UNION. UNION sorts the result and removes duplicate rows, while UNION ALL dcoesn't.
    If you do want the effectes of UNION, then use UNION ALL between all but the last pair of queries, since you need to sort and remove duplicates only once.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by robm View Post
    Thanks John, Felton. I think I've got that part sorted. I used a union to join two of the queries, then added that union to another query which seems to work. I can't see how a single query would work. I need to show the Allocated, Completed, Outstanding for each Actionee. There's another addition to the problem now. They want to know, how many of the Allocations, were issued in the last 7 days. I created another query, which shows just the people who have actions allocated to them this week, but doesn't show the figures for those who haven't.
    I dont understand what you wish to do.

    Would you give us an example of the source data and expected results also by example.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by robm View Post
    There's another addition to the problem now. They want to know, how many of the Allocations, were issued in the last 7 days.
    Add a calculated field to the query (or each of the component queries)
    Last7: IIf([IssueDate] >= Date() -7, true, false)
    This field will be true for any allocations issued in the last 7 days, and false for the rest. You can then Group By this for counting purposes.
    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
  •