Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Unanswered: Grouping records from two Tables

    I have two Queries (Query1 and Query2). Each Query has two Fields named "DRG" and "Covered". I am trying to count the number of "Covered" records for each DRG for each Query1 and Query2. For example, for DRG = 876, Query1 has 7 records and Query2 has 2 records. The below SQL gives me the answer "7" for both CountOfCovered1 and CountOfCovered2. For DRG = 876, the correct answer should be CountOfCovered1 = 7 and CountOfCovered2 = 2. Can anyone please suggest an SQL that gives me the correct unique counts for each DRG (i.e. 7 and 2 for DRG = 876).

    SELECT Query1.DRG, Count(Query1.Covered) AS CountOfCovered1, Query2.DRG, Count(Query2.Covered) AS CountOfCovered2
    FROM Query1 INNER JOIN Query1 ON Query1.DRG = Query2.DRG
    GROUP BY Query1.DRG, Query2.DRG;

    Thank you for any help.
    Jim

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by jpgalasso View Post
    I have two Queries (Query1 and Query2). Each Query has two Fields named "DRG" and "Covered". I am trying to count the number of "Covered" records for each DRG for each Query1 and Query2. For example, for DRG = 876, Query1 has 7 records and Query2 has 2 records. The below SQL gives me the answer "7" for both CountOfCovered1 and CountOfCovered2. For DRG = 876, the correct answer should be CountOfCovered1 = 7 and CountOfCovered2 = 2. Can anyone please suggest an SQL that gives me the correct unique counts for each DRG (i.e. 7 and 2 for DRG = 876).

    SELECT Query1.DRG, Count(Query1.Covered) AS CountOfCovered1, Query2.DRG, Count(Query2.Covered) AS CountOfCovered2
    FROM Query1 INNER JOIN Query1 ON Query1.DRG = Query2.DRG
    GROUP BY Query1.DRG, Query2.DRG;

    Thank you for any help.
    Jim
    Jim,

    I would create a union query to make a single recordset from the two queries. Now you can make you totaling query usinf this third query

    Query 3
    Code:
    SELECT Query1.DRG,  Query1.Covered FROM Query1
    
    Union All 
    SELECT Query2.DRG,  Query2.Covered FROM Query2;
    Save the above query.

    Now you can get the totals

    Code:
    SELECT Query3.DRG, Count(Query3.Covered) AS CountOfCovered
     FROM Query3
    GROUP BY Query3.DRG
    Hope this helps ...
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    Thank you, HiTechCoach.
    If I followed your instructions correctly, I got a single recordset that totaled the record counts from Query1 and Query2. That is, for my DRG = 876, I got 9 (7 from Query1 plus 2 from Query2). I am trying to get a single recordset that shows each the 7 from Query1 and the 2 from Query2 versus the sum of the two. Is that an easy fix?

    Again, thanks much for your help.
    Jim

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by jpgalasso View Post
    Thank you, HiTechCoach.
    If I followed your instructions correctly, I got a single recordset that totaled the record counts from Query1 and Query2. That is, for my DRG = 876, I got 9 (7 from Query1 plus 2 from Query2). I am trying to get a single recordset that shows each the 7 from Query1 and the 2 from Query2 versus the sum of the two. Is that an easy fix?

    Again, thanks much for your help.
    Jim
    Jim,

    I misunderstood. I was thinking you wanted a grand total from both queries.

    Note: Using generic name like Query1, Query2, etc makes it difficult to figure out what you are doing.

    What is the data type for Covered? What values will it have?

    Will bit Q1 and Q2 had the exact same DRG records?


    You could add a dummy columns to the Union Query to keep the Counts in separate columns. Without known the data type for Covered, it is hard to say how.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Feb 2004
    Posts
    67
    Sorry. I thought it was easier to use "Query1" & Query2". This will probably show why: Actual names are:"qryCY09_NtWk_Inpt" and "qryCY09_OON_Inpt". The DRG Field includes claim codes in Text Format but are actually long integers. The "Covered" Field includes claim dollars in Currency format. There are a bunch of other Fields (about 20) several of which I am trying to Sum by the DRG groupings. Both of the Queries have identical Field Names but one Query (i.e. "qryCY09_OON_Inpt") has a lot fewer defined DRG's than the other. I am trying to get a single recordset that would include the following Fields:

    DRG NtWkClaimCount OONClaimCount SumClaimsNtWk SumClaimsOON

    I hope I did not make it even more confusing. Thanks again for all your help.
    Jim

Posting Permissions

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