Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Counting queries (Access 2000)

    1. I need to create a query that will count the number of records containing a "1"in a bit field.

    Data:
    [Name], [Flag]
    Tom, 1
    Dick, 0
    Harry, 0
    Tom, 1
    Dick, 0
    Tom, 0
    Tom, 1
    Harry, 1

    Query result:
    [Name], [Flag Count]
    Tom, 3
    Dick, 0
    Harry, 1

    2. I need to create a query that will count the number of occurrences of each of a range of values in a text field.

    Data:
    [Name], [Grade]
    Tom, A
    Dick, B
    Harry, C
    Dick, A
    Tom, B
    Harry, A
    Tom, A
    Harry, C

    Query result:
    [Name], [A, B, C]
    Tom, 2, 1, 0
    Dick, 1, 1, 0
    Harry, 1, 0, 2

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting queries (Access 2000)

    Hopefully this example will help. For your first query, I used the find duplicates query, and just added criteria.

    For the second I created another crosstab query...the wizards do work <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Counting queries (Access 2000)

    Thanks for your help.

    I need to extend the second query to rank the names by a total score for each name.

    The possible grades are A,B,C,D,E,N. I imagine this might involve assigning a value to each grade and somehow calculating a total score for each name that can then be used as a sort criterion (e.g., A=5, B=4, C=3, D=2, E=1, N=0), but can this be done by extending the crosstab query or is there more to it?

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

    Re: Counting queries (Access 2000)

    You can add a Score field to the tblGrades table.
    Next, create a query based on tbl2 and on tblGrades, joined on Grade.
    Add the Name field from tbl2 and the Score field from tblGrades.
    Make the query into a Totals query and set the Total option for Score to Sum.
    Save this query as qryScores.
    Create a new query based on qryGrades and qryScores, joined on Name.
    Add * from qryGrades and SumOfScore from qryScores.
    See attached version.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Counting queries (Access 2000)

    Thanks Hans, that works fine.

Posting Permissions

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