subquery with critera and count?

D

danheskett

I have a table that looks like this:

+-------------+
| Statements |
+-------------+-----+
| ID Primary Key |
+-------------------+
| RecKey Text(128) |
+-------------------+

The RecKey is indexed but duplicates are allowed. I want to produce a
query that shows a list of each unique RecKey with the number of times
the RecKey is present in the table;

I was thinking:

SELECT ID, RecKey, RecKey as R, Count(SELECT ID FROM Statements Where
RecKey=R)

But of course this quite right; for one it doesn't work, I can't figure
out to make Access recognize R as the value, and not a literal;
additionall, if this did work, it would not show only unique values of
RecKey.

Anyone have some tips on what missing link I am without?
 
A

Al Camp

If the RecKey values you want only have an "R" in that field...
RecKey = "R"
Given that the field is 128 in length I'm thinking you may have RecKey
values like R1423, R9392876, etc...
In that case...
RecKey Like "R" & "*"
should do it.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
D

Douglas J. Steele

SELECT RecKey, Count(*) AS RecKeyCount
FROM Statements
GROUP BY RecKey
 
D

danheskett

Thanks Guys!

I was making it very difficult, when it's not.

Doug and Duy's solutions both worked very well.
 
Top