Trying to get a count of the number of records for each code; that is: 90000
records with a code field; some code "a" records have 1000 records and some
code "b" records have 20000 records; I'd like to know !
In other words; ther are perhaps 20 codes; there is a "code" field with
these repeating codes; I'd like a count of how many records contain code "c"
and how many have code "d", etc.
I've tried this , and many many other variations !

SELECT contacts.code Count(contacts.code) AS CountOfItemName
FROM
(SELECT DISTINCT code ) As q
GROUP BY code;

On Mon, 28 Feb 2011 12:02:49 -0500, "barret bondon" <(E-Mail Removed)>
wrote:

>Trying to get a count of the number of records for each code; that is: 90000
>records with a code field; some code "a" records have 1000 records and some
>code "b" records have 20000 records; I'd like to know !
>In other words; ther are perhaps 20 codes; there is a "code" field with
>these repeating codes; I'd like a count of how many records contain code "c"
>and how many have code "d", etc.
>I've tried this , and many many other variations !
>
>SELECT contacts.code Count(contacts.code) AS CountOfItemName
>FROM
>(SELECT DISTINCT code ) As q
>GROUP BY code;
>

Sounds like you don't really want to count *codes*; you want to count *records
for each code*. If that's the case lose the subquery:

SELECT code, Count(*) AS CountOfCodes
FROM yourtablename
GROUP BY code;

As written your query makes no sense, since neither the query nor the subquery
references a table.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

barret bondon wrote:
> Trying to get a count of the number of records for each code; that
> is: 90000 records with a code field; some code "a" records have 1000
> records and some code "b" records have 20000 records; I'd like to
> know ! In other words; ther are perhaps 20 codes; there is a "code" field
> with these repeating codes; I'd like a count of how many records
> contain code "c" and how many have code "d", etc.
> I've tried this , and many many other variations !
>
> SELECT contacts.code Count(contacts.code) AS CountOfItemName
> FROM
> (SELECT DISTINCT code ) As q
> GROUP BY code;
You're overcomplicating it. This should do what you want:

SELECT code, count(*) As CountPerCode
FROM Contacts
GROUP BY code