# Thread: Counting queries (Access 2000)

1. ## 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:
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. ## 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>

3. ## Re: Counting queries (Access 2000)

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. ## Re: Counting queries (Access 2000)

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.