I need to count records with a specific value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that returns records indicating who answered correctly in a
contest. One field is [District]. I also have a table called [Districts]
that contains a list of the 166 different possible districts.

I am attempting to create a query that lists the 166 different districts
along with the number of records in the first query that use each different
district code. I'm having no luck with Dcount (but I've never used it before
so I could be doing it wrong) and count works only with a criteria thus
forcing me to create 166 seperate queries, which seems both inefficient and
inflexible. (If the [Districts] table gets a new record, I would need to
create a new query for it, etc.)

Any help is greatly appreciated.
 
Create a query that includes both the first table and the Districts table.
Join the tables on District. Click on the join line and in the dialog that
appears, change the join type to the one that says include all the records
from the District table and only those from the first table that match. Pull
down into the query grid District Name from the district table and the
primary key from the first table. Click on the Sigma (looks like E) button
on the toolbar at the top of the screen. Under the primary key of the first
table in the query grid, change Group By to Count. The query will now give a
total count of records in the first table by District.

If some districts are blnak because they have no records, do the following.
Create another query based the first query. Pull down the District Name.
Enter the following expression in the second query field:
DistrictCount:NZ([CountOf...Whatever it says in the query in the
window...]),0)
This second query will give you the count for the district where there are
records and 0 for the districts where there are no records.
 
Spot On!!!

Thank you VERY much!


PC Datasheet said:
Create a query that includes both the first table and the Districts table.
Join the tables on District. Click on the join line and in the dialog that
appears, change the join type to the one that says include all the records
from the District table and only those from the first table that match. Pull
down into the query grid District Name from the district table and the
primary key from the first table. Click on the Sigma (looks like E) button
on the toolbar at the top of the screen. Under the primary key of the first
table in the query grid, change Group By to Count. The query will now give a
total count of records in the first table by District.

If some districts are blnak because they have no records, do the following.
Create another query based the first query. Pull down the District Name.
Enter the following expression in the second query field:
DistrictCount:NZ([CountOf...Whatever it says in the query in the
window...]),0)
This second query will give you the count for the district where there are
records and 0 for the districts where there are no records.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Eric Carl said:
I have a query that returns records indicating who answered correctly in a
contest. One field is [District]. I also have a table called [Districts]
that contains a list of the 166 different possible districts.

I am attempting to create a query that lists the 166 different districts
along with the number of records in the first query that use each different
district code. I'm having no luck with Dcount (but I've never used it before
so I could be doing it wrong) and count works only with a criteria thus
forcing me to create 166 seperate queries, which seems both inefficient and
inflexible. (If the [Districts] table gets a new record, I would need to
create a new query for it, etc.)

Any help is greatly appreciated.
Click to expand...
Click to expand...
 
Back
Top