Results 1 to 3 of 3
  1. #1
    TheMoodyFiles is offline Novice
    Windows XP Access 2002
    Join Date
    Feb 2011
    Posts
    2

    GROUP and COUNT?

    Hello. I am trying to query a table with three columns--CustomerID, CorpID, and SalesRating.



    I need to first only return results where there are 7 or more entires for the same CorpID. Next, I would group the results by the SalesRating, and in third column, show how many results there are for each grouped SalesRating.

    Anything I've tried with grouping and counting is not working. Honestly, I'm quite new at this. I have attached an example of the data and how I'm trying to get the results to look like.

    If anyone could please give a quick walk though a possible solution, it would be much appreciated. In the meantime, I'll keep working on it!

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Hi,

    Try this query:

    Code:
    SELECT tblSalesRating.CorpID, tblSalesRating.SalesRating, Count(tblSalesRating.SalesRating) AS CountOfSalesRating
    FROM tblSalesRating INNER JOIN (SELECT tblSalesRating.CorpID
    FROM tblSalesRating
    GROUP BY tblSalesRating.CorpID
    HAVING Count(tblSalesRating.CorpID)>6) AS qryCorpRatingCount
     ON tblSalesRating.CorpID = qryCorpRatingCount.CorpID
    GROUP BY tblSalesRating.CorpID, tblSalesRating.SalesRating;
    Cheers

  3. #3
    TheMoodyFiles is offline Novice
    Windows XP Access 2002
    Join Date
    Feb 2011
    Posts
    2
    Perfect! Thanks, ConneXionLost! I didn't think of using a select statement inside the join.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Count and Group By
    By Tyork in forum Programming
    Replies: 11
    Last Post: 01-24-2011, 09:06 AM
  2. Query to group and count field entries
    By newtoAccess in forum Queries
    Replies: 4
    Last Post: 11-29-2010, 09:19 AM
  3. Replies: 5
    Last Post: 11-29-2010, 08:16 AM
  4. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  5. columns for group detail but not group header?
    By Coolpapabell in forum Reports
    Replies: 0
    Last Post: 08-21-2009, 08:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums