Sum or Duplicate records in a recordset.

[TheUltimateChampion]

How do I count the number of times a field has duplicate data in a Record in a Table? Example:
Rows 1,2, & 3 have a Field Heading of Silverwear. In Rows 1, 2 I have "Fork". In row 3, Knife.
I would like a (in a Module) to return 2 for a Fork query.

I would also like to do this in a query (but one thing at a time).

Thank you.

 

[PHV]

Something like this ?
SELECT Silverwear, Count(*) AS DupCount
FROM yourTable
GROUP BY Silverwear

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheUltimateChampion]

That is SQL isn't it?
I was hoping for VBA. Something like Count Me.Recordset...

Thank you.

 

[PHV]

Are you aware you posted in the ANSI_SQL forum ?
Anyway take a look at the DCount VBA function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244