Count Values in column based on other column

[dharkangel]

Hello,

I was wondering if this was possible. I have a table with two fields (UserName and Role) with the following values:

UserName Role
---- ____
Smith, James ETM
Jones, Rodney EUM
Jones, Rodney EUM
Thompson, Dennis EUM
Johnson, Abe PD
Johnson, Abe PD
Johnson, Abe PD
Wllkins, Suzanne PD
Tam, Sarah PT

I was wondering if it was possible to write a query that would count the values in the Role field once per UserName?

So if I could use this query to count EUM it would give me a value of 2. PD would be 2.

Thank you,

 

[Pampers]

In your query (design mode), click on the Sum-sign (located on the toolbar). In the querygrid, set GroupBy for the UserName, set Count for Role. This wil count for each Username how many Roles it plays.

Pampers
There is only one way to change a diaper - fast

 

[dhookom]

If the role is to be counted only once per UserName then you can first create a totals query "qgrpUserRoles" like:
SELECT UserName, Role
FROM [table with two fields]
GROUP BY UserName, Role;

Then create another query:
SELECT Role, Count(*) as NumOf
FROM qgrpUserRoles
GROUP BY Role;


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Pampers]

Darn Dhookom,
You two query answer gave me the solution for a report query i'm working on!! You can use a total query (f.i. Max) the pull the right records. Then use this query to make another query the retrieve the remaing recordinfo - if you follow what i'm saying ;-)


Pampers
There is only one way to change a diaper - fast

 

[PHV]

Since ac2k or above, no need of two queries:
SELECT Role, Count(*) AS NumOf
FROM (SELECT DISTINCT UserName, Role FROM yourTable) D
GROUP BY Role

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

I was hoping PH would come in with a single query syntax. I should have just waited

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Pampers]

Sorry Dharkangel,
I misunderstood your question. It has to count the number of Roles for each role. Why not?

SELECT tblRoles.Role, Count(tblRoles.Role) AS CountOfRole
FROM tblRoles
GROUP BY tblRoles.Role;

Pampers
There is only one way to change a diaper - fast

 

[dharkangel]

You guys are awesome, especially PH. I've learned more SQL techniques on this board in the last two weeks than in any class I've taken. The only thing is, I'm beginning to get frustrated at how it seems that MS Access has its own little ways of doing things.