Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query - Summing the highest category (2003)

    I am running a query that has the following fields; State,Market,Fee Sched,subspecialty, and dollars. What I am trying to do is to run this query and when the dollars are summed have it grouped where it brings back the subspecialty with the highest dollars not all subspecialties. For example FLorida,Tampa,1002,Chiropractic, $40000.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query - Summing the highest category (2003)

    First i would create a totals query Q1 that sums the amounnt by State,Market,Fee Sched and subspecialty like:
    SELECT State,Market,[Fee Sched],subspecialty, Sum(Amount) as SumAmount
    FROM tablename
    GROUP BY State,Market,Fee Sched,subspecialty

    I would then create a TOP 1 query based upon the first query:
    SELECT TOP ! State,Market,Fee Sched,subspecialty, SumAmount
    FROM Q1
    ORDER BY SumAmount DESC

Posting Permissions

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