Results 1 to 4 of 4
  1. #1
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16

    How Do I group by and get a count in an Access Query


    I have a simple query where in I am looking at Member Plans in 6 different states. I need to group by State and get a count of total plans per state and then have a grand total at the end can I do this in Access? I am using MS Access 2007 odbc connected to an Oracle Database. I am enclosing a copy of the query design. Thanks!!
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Click on the "Totals" icon on the ribbon. That will add a Totals row to the design grid. Change the default Group By to Count for the relevant field. You may want to drop some of those fields, or change them to Where. Play around with that and see how it does.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    logle0917 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    16
    If I do a count on R1_State I bet and Error that says data type Mismatch in creteria Expression. If I do a count on Subscriber ID I get a count o Subscriber ID per Plan Code. What I need is to have a count of every member who has a plan in say Al no matter what the plan code is I need the total number of people with a plan in that state.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Like I said, I would delete fields to just what you want to group on or count, or have a criteria on.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. nested query: avg, count, group by
    By gap in forum Queries
    Replies: 2
    Last Post: 07-05-2011, 07:48 AM
  2. Replies: 0
    Last Post: 04-25-2011, 07:58 PM
  3. GROUP and COUNT?
    By TheMoodyFiles in forum Queries
    Replies: 2
    Last Post: 02-10-2011, 05:50 PM
  4. Query to group and count field entries
    By newtoAccess in forum Queries
    Replies: 4
    Last Post: 11-29-2010, 09:19 AM
  5. Replies: 2
    Last Post: 11-18-2009, 06:49 PM

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