Query Count
Results 1 to 4 of 4

Thread: Query Count
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Query Count

    This is hopefully a simple one.

    How can I count records based on criteria? So it would be a count of 'Active' ('Status' Field) for each 'Group'.

    I'm looking to go from something like this:
    Group RecordID Status
    ABC 0001 Active
    ABC 0002 Closed
    ABC 0003 Active
    DEF 0004 Active
    etc....

    To this:
    Group Active Records
    ABC 13
    DEF 25

    I've tried the below as a field in the query but it returns the wrong numbers:
    Code:
    =DCount("[Group]", "tblRecords", "[SalesStatus] = 'Active'")


    Last edited by QandAdam; Jul 10th, 2019 at 02:32 PM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,084
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Query Count

    This is really easy to do with an Aggregate (Totals) Query.
    Create a new query adding all three of those fields.
    Click on the Totals button in the menu of the Query Builder (look like a Sigma).
    Under the "RecordID" field, change the new Totals row value from "Group By" to "Count".
    Under the "Status" field, change the new Totals row value from "Group By" to "Where".
    Add the word "Active" on the Criteria row of the "Status" field.
    View your results.

    You may wish to read up more on Aggregate/Totals queries to get a better understanding of how they work. You can find lots of articles and tutorials with a Google search.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jan 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query Count

    Quote Originally Posted by Joe4 View Post
    This is really easy to do with an Aggregate (Totals) Query.
    Create a new query adding all three of those fields.
    Click on the Totals button in the menu of the Query Builder (look like a Sigma).
    Under the "RecordID" field, change the new Totals row value from "Group By" to "Count".
    Under the "Status" field, change the new Totals row value from "Group By" to "Where".
    Add the word "Active" on the Criteria row of the "Status" field.
    View your results.

    You may wish to read up more on Aggregate/Totals queries to get a better understanding of how they work. You can find lots of articles and tutorials with a Google search.
    AMAZING! That is easy! Thanks for the help and advice

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,084
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Query Count

    You are welcome.
    Glad I was able to help!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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