Count records in Access

[Ken Pollard]

I have an access database with a field named color. There are several records containing a color name. I want to list the unique values with the record count of each. Like Red 5, Blue 23, Green 14 etc. How can I accomplish this?

I will be using asp/vbscript. Thanks

[Creativ_Technologies]

You can create a recordset for each color like so:

SELECT Count(*) AS Total
FROM colors
WHERE colorName = 'colorName'

Or you could create loops for all the colors to loop through a single recordset to keep count of the colors. Simliar to:

<%
while not rs.eof
dim redCounter
dim blueCounter

if rs.fields.item("colorName") = 'blue' then
blueCounter += 1
elseif rs.fields.item("colorName") = 'red' then
redCounter +=1
end if

loop
%>

That is just off the top of my head, you will need to do some tweaking to get it to work for your application. That should point you in the right direction.

[lands1ide]

SELECT COUNT (*) AS TotalRows
FROM tablename
WHERE field = 'red';