Newbie: counting unique records

[icsottawa]

I have a table with (among other fields) a Postal Code field and a
City field. Since many postal codes can belong to the same city, how
can I count the number of unique cities in my table?

Thanks in advance...Carlos

 

[icsottawa]

Build a recordset with the Distinct modifier: "SELECT DISTINCT [City] FROM
tablename;"
Then see how many records are returned:  intCount = recordsetname.RecordCount

I have a table with (among other fields) a Postal Code field and a
City field. Since many postal codes can belong to the same city, how
can I count the number of unique cities in my table?

Thanks in advance...Carlos

Thanks June7, but I am too new at this to really understand what you
are talking about. Is there a way to do what I need to accomplish in
the Design query window of Access?

Thanks again...Carlos

 

[John Spencer]

Open a new query.
== Add your table
== Add the City field
== Right Click in the blank grey area near the table and select Properties
== In the QUERY properties window change Unique Values to Yes
== The query will return one record for each city value.

Another option is Allen Browne's ECount function.

ECount() - an extended DCount()
at:
http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

Once you have copied and pasted the function into a VBA module, you should be
able to get the count with this expression

ECount("City","[Your Table Name]",,True)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Build a recordset with the Distinct modifier: "SELECT DISTINCT [City] FROM
tablename;"
Then see how many records are returned: intCount = recordsetname.RecordCount

I have a table with (among other fields) a Postal Code field and a
City field. Since many postal codes can belong to the same city, how
can I count the number of unique cities in my table?
Thanks in advance...Carlos

Thanks June7, but I am too new at this to really understand what you
are talking about. Is there a way to do what I need to accomplish in
the Design query window of Access?

Thanks again...Carlos

 

[icsottawa]

Open a new query.
== Add your table
== Add the City field
== Right Click in the blank grey area near the table and select Properties
== In the QUERY properties window change Unique Values to Yes
== The query will return one record for each city value.

Another option is Allen Browne's ECount function.

     ECount() - an extended DCount()
at:
     http://allenbrowne.com/ser-66.html

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

Once you have copied and pasted the function into a VBA module, you should be
able to get the count with this expression

   ECount("City","[Your Table Name]",,True)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Build a recordset with the Distinct modifier: "SELECT DISTINCT [City] FROM
tablename;"
Then see how many records are returned:  intCount = recordsetname.RecordCount
icsottawa wrote:
I have a table with (among other fields) a Postal Code field and a
City field. Since many postal codes can belong to the same city, how
can I count the number of unique cities in my table?
Thanks in advance...Carlos

Thanks June7, but I am too new at this to really understand what you
are talking about. Is there a way to do what I need to accomplish in
the Design query window of Access?

Thanks again...Carlos

Thank you John. Will try both suggestions. Bye for now...Carlos