dumb question

dazmiller · September 19th, 2005, 10:10 PM

ok a dumb one here but i need some help.

I have records as such in access db

Name ID StatusCode

Name is a list of names
ID is an ID funnily nuff
StatusCode is a string that can be any of 50 different values.

So i have records like this

Name ID StatusCode
Dan 1 05
Mary 2 25
Dan 1 25
Dan 1 25

I need a query to do the following:

For each name how many 05's are there, how many 25's etc.
so the result should be something like:

Dan 05 Result : 1
Dan 25 Result : 2
Mary 05 Result : 1

Basically i need the count of rows for each person that match the statuscode.

Any ideas?

Jeff Mason · September 20th, 2005, 05:20 AM

So, you need, for each set consisting of a grouping of StatusCode by Name, the count of the number of elements in each set?

How about:

Code:

    SELECT Name, ID, COUNT(*) as Result
    FROM yourtable
    GROUP BY Name, StatusCode

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

gemsuis · September 29th, 2005, 09:10 AM

Mr. Jeff Mason is sujjested you good but you should know that the "Result" will the your count of rows and "yourtable" will be the name of your table