I am trying to create a report whos underlying record source is a table with two fields. One of these fields can contain duplicate data. I am counting each record in the table where this field contains duplicates. I then want to display the name of this field and the number of times it is in the table. I can't seem to find a way to do this because the detail section of the report wants to display every record in the table. An example of what I am trying to do would be the following:

The table contains 100 records. Of these 100 records one of the field names is County. Each county can be in the table multiple times. I want to count the number of times each county is in the table and display one record in the report detail with the County Name and the number of times it is in the table.

Any insight is greatly appreciated.

Thanks

You need to base the report on a query, and do this:

Assume tblState
State - text
County - text

Select the state field, and then select the county field twice. Select View Totals, and then change the second county field to Count.

Your SQL will look like this:

SELECT tblState.State, tblState.County, Count(tblState.County) AS CountOfCounty
FROM tblState
GROUP BY tblState.State, tblState.County;

Then base the report off this query.

Did that help?


mmcdonal

Thanks for your reply. I found a way to do this with the help of a colleague. I do have another report I need to try so I will try this out and let you know. Thanks again so much for your reply.

LP