frequency of a number, Dcount

[krisleech]

Which SQL function is used to calculate the frequency of a paticular number in a number field.
I have tried both count and Dcount but dont get the exact results im looking for.

Using count i get the number of records, but not the number of records that contain, say, the number 9.

Using Dcount i can add a criteria eg. "[field] = 9" which give me the correct results, but i also have a keyfield called [ID] and i wish to calculate the frequency of say 9 for each unique ID.
Maybe you could add multiple criteria to Dcount, or add a [Please enter ID] criteria to the [ID] field?

Many thanks Kris.

 

[krisleech]

thanks that should work perfectly, but i need to display the frequency of values per unique ID field. ie. frequency of value 1 for ID 7


Best wishes, kris

 

[The_Doc_Man]

Select YourID, YourField, Count(*) As RecCount
From YourTable
Group By YourID, YourField;

Now use the report wizard to build an autoreport from this query.