Count IF

[Roger]

I would like to get a count of a column. The column
contains several values. In Excel these values can be
obtained with countif(a1:a99,"value1"), this yealds a
count of the value1. To obtain a different count, countif
(a1:a99,"value2") yealds unique count of value2.

Question, How is this done in an Access 2002 database?

 

[Media Lint]

Something like this:

SELECT Count(Contacts.City) AS ContactCount
FROM Contacts
GROUP BY Contacts.City
HAVING (((Contacts.City)="San Francisco"));

 

[John Vinson]

I would like to get a count of a column. The column
contains several values. In Excel these values can be
obtained with countif(a1:a99,"value1"), this yealds a
count of the value1. To obtain a different count, countif
(a1:a99,"value2") yealds unique count of value2.

Question, How is this done in an Access 2002 database?

A Totals query will do this. Create a Query based on your table, and
select the field TWICE (or select the field and also the table's
Primary Key field). Change the query to a Totals query using the Greek
Sigma icon; leave the default Group By on the Totals row for the field
that you want to count distinct values, and change it to Count on the
second field.

Bear in mind: a Spreadsheet and a Database are *very different
objects*. Applying spreadsheet thinking will just get you tied in
knots. Unlearning the "way things are always done" can sometimes be
harder than learning relational thinking from scratch, but it's
essential!

 

[Bruce M. Thompson]

I would like to get a count of a column. The column

contains several values. In Excel these values can be
obtained with countif(a1:a99,"value1"), this yealds a
count of the value1. To obtain a different count, countif
(a1:a99,"value2") yealds unique count of value2.

Question, How is this done in an Access 2002 database?

If I'm reading you right, you might be able to use:

In the "Control Source" property setting for a textbox in a report's or form's
footer section:

= Abs(Sum([FieldName]="Value1"))

Using the "DCount()" function to return the value directly (watch for line
wrap - it's all on one line):

varReturn = DCount("*","MyTableName","[FieldName]=""" & Me.txtValue.Value &
"""")

Using a "Totals" query - this returns the count for each distinct value in the
field (you could use the "DLookup()" function against this to retrieve the count
on any one value):

SELECT Count(TableName.Field1Name) AS Field1Count, TableName.Field2Name
FROM TableName
GROUP BY TableName.Field2Name;