Counting Records in a Query

[dmugan]

Hello:

I have a table with fields Name and Contract. A person may be listed more than once with several contracts. In the query, I just want to count (records), that is the number of times the Name appears. I tried a Totals query, replaced Count in the name field but it puts a 1 as output.

Thank you.

 

[FoFa]

Count will count the number of records, but you have to be grouping them, and it sounds like you are not. You could use a subselect as a column returniong the count as a column in your query using the name (or whatever) as part of the subselects criteria. If you use a report you can count the records for a group also, yet still show the detail lines.

 

[Mile-O]

Also, you can use the query wizard to build a Find Duplicates query and basically, with a bit of maths:

The count is: (Count in your original query - Count in Find Duplicates)

 

[Pat Hartman]

The following will give you a count of contracts by customer when run against the contract table.

Select Customer, Count(*) As ContractCount
From YourTable
Group By Customer;