Case of the curious query ; count the codes

[barret bondon]

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;

 

[John W. Vinson]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Barrows]

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