number of rows that equal a specified value

[mdb]

I have a table called applications in MS Access
In the table there is a column called status
How would I go about getting the number of the cells that equal a specified value?

| status |
a
a
b
c
b
d
d

The values are actually numbers if that helps.

A = 2
B = 2
C = 1
D = 2

Thanks!

[candelbc]

Do you mean a total of all the values OR find out how many A's, B's, C's and D's there are?

I'm sorry, but I just need a little more clarification..

-Brad

[mdb]

Sorry, i don't even know what mean!

Yes, I would like to find out how many A's, B's, C's and D's there are.

The end result is to display some text telling the user how many
A's, B's, C's and D's.....

[candelbc]

If I am understanding you correctly, the following SQL will give you the information you should need

SELECT Count(applications.status) AS CountOfStatuss, applications.status
FROM applications
GROUP BY applications.status;


It will return a count of the rows grouped by status letter.

[mdb]

That works, but I don't know yet if it accomplish what I want to do

So how would I display the result of
CountOfStatus
if it equals A, etc?

[russell]

SELECT Count(status) FROM applications WHERE status = 'A'

[candelbc]

Well, a lot depends on just how and when you are opening your recordset in my opinion, but adding a

WHERE UCase(applications.status) = "A"

in your SQL code should allow you to get the value for just "A".

As for retrieving that value, Rst.Fields("CountOfStatus") should do the trick.

-B

[mdb]

(Recordset1.Fields.Item("Expr1000").Value) displays 1
(Recordset2.Fields.Item("CountOfStatuss")) displays 1

I assume that rs1 obvious shows "A" because that is what is in the sql statement.
and rs2 is showing the first row which is "A"



WHERE UCase(applications.status) = "A"
works for me but if I want to show the results of all the "letters"
Separately on the same page will I need to create 5 recordsets
or can that be taken care of in one SQL statement using asp or vb to write it.
i.e
you have 1 A
you have 2 B's
you have 1 C

[russell]

candelbc already showed that

SELECT Count(applications.status) AS CountOfStatuss,
applications.status
FROM applications
GROUP BY applications.status;