Results 1 to 5 of 5

Thread: Union Queries

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Queries

    I can't get this union query to do what I want. Any help?

    SELECT [DA] AS [District Attorney], COUNT(*) AS [Cases]
    FROM [tblMaster]
    GROUP BY [DA]

    UNION SELECT [DA] AS [District Attorney], COUNT(*) AS [Cases]
    FROM [tblMasterArc2000]
    GROUP BY [DA];

    I'm using it as the record source for a listbox in one of my forms. The listbox contains 2 columns: District Attorney and Cases. I want to get a count of all records for each District Attorney from both tables: tblMaster and tblMasterArc2000. And display the total amount of cases they have handled in the Cases column.
    Thanks in advance.
    Jols

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Queries

    You need to group on the result of the union.
    Base your list on an sql command where the union is grouped and totalled.
    Also you may require UNION SELECT ALL not just UNION SELECT.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Queries

    My list is based on based on the union query. I now have it working somewhat. But I am getting totals for each table, not totals of all records for that DA. IN my listbox the DA is showing up twice, once for the total of cases handled in one table and the second for the total of cases handled in archive. I just want the DA's name to show up once with a total of all the cases that he has handled.
    Also I have added All to the UNION SELECT statement but it didn't help.
    What am I doing wrong or not doing?
    Thanks Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Queries

    If you use a GroupBy in each separate SELECT clause, you'll get counts for each DA for each table. You have to make the GroupBy only turn up at the end of the last SELECT clause to have it apply to the whole union query.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Queries

    You had misunderstood my answer I suspect. Don't do any grouping based on the tables, just get the DA's from the tables. The base another query on the union query where the grouping and counting is done. This new query is the source for your listbox.
    Maybe it will help you to think of the union as an 'append query'. The first query in the union has its results. The second (and further) queries in the union are appended onto this recordset. Hence the duplicate DA's appearing.
    Also note that a union query (on my system and setup) suppresses duplicates. So you may need to use the UNION SELECT ALL to get the results you really want.

Posting Permissions

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