Count Records in a Query

[Steve]

I have a query that has, lets say, 500 results. Some of the results in the
field I am interested in are duplicates. I am trying to build a calculated
field in my query that shows on each of the 500 results how many duplicates
there are for that row's result only. So for instance, if the first row's
result is "ABC123" and "ABC123" is in 4 other rows in the query results (a
total of 5 instances), I want 5 to show in the calculated field in every row
where "ABC123" appears.

Thanks for your help.

 

[John Spencer]

You could use DCount, but it will be slow

Field: CountRecs: DCount("*","YourTableName","YourField=""" &
[YourField] & """")

If that is too slow, post back with some information on your table and
field names.

You can build a query to return the field and the count and then join
that to your current query to get the duplicate count

SELECT YourField, Count(YourField) as RecCount
FROM YourTable
GROUP BY YourField

Then the new query would be
SELECT RecCount, A.*
FROM YourTable as A INNER JOIN TheSavedQuery as S
ON A.YourField = S.YourField

All in one
SELECT RecCount, A.*
FROM YourTable as A INNER JOIN
(SELECT YourField, Count(YourField) as RecCount
FROM YourTable
GROUP BY YourField) as S
ON A.YourField = S.YourField


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Steve]

i figured out I can accomplish what I wanted by doing a JOIN between a query
and a table in the Query Builder. I have the results I needed now. Thanks
anyways.