Counting unique values in a field? Help!

# Thread: Counting unique values in a field? Help!

1. Tim
Senior Member
Join Date
Dec 1969
Posts
552

## Counting unique values in a field? Help!

Hi all. Here is what I&#039;m trying to do:<BR><BR>I have a table named "Table1". I have two fields in this table, "ABC" and "XYZ". For each unique value in "ABC", I want to count the number of records for each unique value in "XYZ". In other words, if "Tim" is a value in ABC, and the numbers "1" and "2" are values in XYZ, I would like to count the number of records with a 1 and the number of records with a 2 in XYZ when ABC = "Tim"<BR><BR>I know I can use filters, etc, but I am working with a pretty sizeable database (MS Access aprox 80,000 records) and I would like to find the most efficient way (ie fastest) to do such counts. I am unsure if there are counting commands or functions in VBscript of which I&#039;m unaware.<BR><BR>Any suggestions will be very appreciated- Thanks, Tim

2. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Counting unique values in a field? Help!

intCount = conn.execute("SELECT COUNT(xyz) FROM table1 WHERE abc = &#039;Tim&#039; GROUP BY xyz)<BR><BR>response.write "Number of records = " & intCount<BR><BR>(assuming &#039;conn&#039; is your active connection)<BR><BR>Peter

3. Tim
Senior Member
Join Date
Dec 1969
Posts
552

## RE: Counting unique values in a field? Help! (agai

Thanks Dutch. <BR><BR>"Group by xyz"- will this give me acount of ALL records in xyz with a value, regardless of what the value is? How exactly does this work? I need to have the count of each unique value in XYZ. Will the code above give me the count of just the first unique value in XYZ?

4. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Counting unique values in a field? Help! (agai

It will give you the count of each unique value in XYZ where ABC = &#039;Tim&#039;<BR><BR>So if your table looks like this:<BR><BR>ABC XYZ<BR>Tim apple<BR>Tim banana<BR>Tim apple<BR>John banana<BR><BR>The results will look like this:<BR><BR>apple 2<BR>banana 1<BR><BR>Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•