Count query

[Geoffric]

I am used to the Countif function in Excel, whereby if I use
“=countif(a:a,a2)†in an adjacent column to a column of sorted transaction
numbers, and copy that function down against all the rows of data in the
first column, the result gives me the number of times against each
transaction number that number appears, e.g.

C00123 3
C00123 3
C00123 3
C00124 1
C00125 2
C00125 2
C00126 4
C00126 4
C00126 4
C00126 4

Is it possible to do the same thing in Access? Any help gratefully received.

 

[Wayne-I-M]

Hi

The basic layout could be

SELECT Count(TableName.NameOfField) AS CountOfNameOfField,
TableName.NameOfField
FROM TableName
GROUP BY TableName.NameOfField;

This would give
CountOfNameOfField NameOfField
3 C00123
1 C00124
2 C00125
4 C00126

Or you coulde add a sub query to give
3 C00123
3 C00123
3 C00123
1 C00124
2 C00125
2 C00125
4 C00126
4 C00126
4 C00126
4 C00126


Or just create 2 query and read the count from the 1st

SELECT TableName.NameOfField, [1stQueryName].CountOfNameOfField
FROM TableName INNER JOIN 1stQueryName ON TableName.NameOfField =
[1stQueryName].NameOfField;


HTH - Good luck

 

[John Spencer]

There is the DCount function you could use in a query. In a field "cell"
enter the following (all one line).

DCount("*","YourTableName","[TransactionNumber]=""" & [TransactionNumber] & """")

Obviously, you need to put your table and field names in the expression.

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