Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Function (Access 97)

    Greetings, This question might be posted already but I was unable to find what I was looking for.
    I have a small database. I use initials in my "staff" field. I want to count all records in database and have a count for each set of initials. I have been looking at MS website for help and other sites but cannot figure out what I need to do.
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Function (Access 97)

    You can use a totals query for this:
    - Create a query based on your table.
    - Add the initials field twice to the query grid.
    - Select View | Totals or click the Totals button on the toolbar.
    - Leave the Total option for the first column as it is (Group By) and set it to Count for the second column.
    - Switch to datasheet view.

  3. #3
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Function (Access 97)

    That is not really what I want. I have 5000 records, my initials field is my staff initials. Out of the 5000 records I want to know how many are for a particular set of initials. I just want a number count. Example, for initials "JA" there are 200 records for this set of initials.
    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Function (Access 97)

    Enter "JA" in the Criteria line under the first column of the query I described, and set the Total option to Where. This will automatically turn off the Show check box.

  5. #5
    Star Lounger
    Join Date
    May 2004
    Location
    Asheville, North Carolina, USA
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Function (Access 97)

    That fixed the count, thanks. Now lets add a new spin on this. I want to get a record count of "JA" but between certian dates.
    Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Function (Access 97)

    Add the date field to the query grid.
    Set the Total option to Where.
    Enter an expression such as the following in the Criteria line:

    Between #1/1/2004# And #12/31/2004#

    If you want the date range to be variable, you can use parameters:

    Between [Start Date] And [End Date]

    Each time you run the query, Access will prompt you to enter the start date and end date.

Posting Permissions

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