Hi,

I have a table which I would like to count the number of different items 
in it.  However some items are mention more than once.  How can I count 
each different item only once.

Thanks 
Rob

Hi Rob!

There are a number of ways (of course!) to do this.

One technique that I use a lot is to create a standard Select Query using
the Design Grid - put the field that has the variable that you want to count
in one column. Click the "Summation" button on the tool bar and select
"Group By" in the "Total" field. This will give you a query that will list
all unique values of you required field.

If you now add another field from your table to the query and set the
"Total" to "Count" this field will now contain the number of times that each
unique value appears in your underlying table.

The SQL code for this query will look something like this:

SELECT tblMain.[ECR No], Count(tblMain.Ident) AS CountOfIdent
FROM tblMain
GROUP BY tblMain.[ECR No]
HAVING (((tblMain.[ECR No]) Like "*"));

HTH - Spike


> -----Original Message-----
> From: robeetpotts1@h... [mailto:robeetpotts1@h...]
> Sent: Friday, August 23, 2002 12:25 PM
> To: Access
> Subject: [access] Counting different items
> 
> 
> Hi,
> 
> I have a table which I would like to count the number of 
> different items 
> in it.  However some items are mention more than once.  How 
> can I count 
> each different item only once.
> 
> Thanks 
> Rob
> 

Create a new query.
Drag the item name onto the query grid TWICE so that you have two identical columns side by side.
Now that you have two columns click the TOTALS button on the menu bar. It looks like a Greek E.
A new line should have appeared in your grid called Totals with a default value of "Group By". 
Leave the first column as "Group By", set the second column to "Count".
Run your query.
 
Hope this helps...
 
 
 
 
 
 robeetpotts1@h... wrote:Hi,

I have a table which I would like to count the number of different items 
in it. However some items are mention more than once. How can I count 
each different item only once.

Thanks 
Rob






---------------------------------
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes

Thanks guys.  I've got it sorted now