subquery with critera and count?

[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?

 

[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

 

[dd]

select RecKey, count(*) as Frequency from Statements group by RecKey

-Duy

 

[Douglas J. Steele]

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

 

[danheskett]

Thanks Guys!

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

Doug and Duy's solutions both worked very well.