count mutiple user roles as one

R

R_wilson1

Hello,

I have a count issue that I have not seen being asked before. I a
thinking that an IF statement should work but I am unable to figure ou
a way on how to do this. I have a group of users that can have multipl
types of user profiles. I need to count only one of the profiles as on
but ignore the other lesser profiles or if it is a duplicate.

I.E.[/B]
IF PROFILE_ID = SUPPORT THEN
COUNT SUPPORT AS 1

IF PROFILE_ID = FULL IS TRUE AND PROFILE_ID = SUPPORT_ID ISNULL THEN
COUNT FULL AS 1

IF PROFILE_ID = READ_ONLY AND PROFILE_ID = FULL IS NULL AND PROFILE_I
= SUPPORT_ID ISNULL THEN
COUNT READ ONLY AS 1

ELES
COUNT AS UNKNOWN

END IF
END IF
END IF

-FORM-
USERNAME PROFIL
User1 Support
User1 Full
User2 Read only
User2 Full
User3 Read only
User3 Read only
User4 Full
User4 Full

REPORT OUTPU
Support = 1
Full = 2
Read only = 3

I hope this is clear enough.

Thanks for any suggestions in the right directions.

Ric
 
D

Douglas J. Steele

Without knowing exactly how your tables are organized, it's difficult to
give you a definitive answer, but it would sound as though all you need do
is write a query with the DISTINCT keyword in it.
 
D

Douglas J. Steele

Not a chance! There's no way I'm going to download a database from someone I
don't know (and I suspect you'll find that very few others of the regulars
will either)

If you want help, post text describing the layout of the relevant tables.
 
R

R_wilson1

Sorry,

Thought it wold be simipler if you seen how the database was set up.
do see your point though.

It is just a single table (usercount) with the fields Username
User_ID, Profilename, Profile_ID, Department, Dept_I
 
D

Douglas J. Steele

If you want to know how many unique User_IDs there are, try creating the
following query:

SELECT Count(*) AS UniqueUsers
FROM (SELECT DISTINCT User_ID FROM usercount)
 
R

R_wilson1

Hi Doug,

Thnaks for the count for the users but I am looking for the profil
count. Each user can have mutiple profile (Support, Full, read only).
need to know who has the greater profile and count those profiles.

i.e.
USERNAME - PROFILE
User1 - Support
User1 - Full
User2 - Read only
User2 - Full
User3 - Read only
User3 - Read only
User4 - Full
User4 - Full

REPORT OUTPUT
Support = 1
Full = 2
Read only =
 
D

Douglas J. Steele

You need some way of being able to determine the "maximum" level, and that's
going to be difficult with only one table unless Profile_ID happens to list
them in order.

Assuming it does (i.e.: Profile_ID 1 is greater than Profile_ID 2 and so
on), try:

SELECT Profile, Count(*)
FROM
(SELECT User_ID, Min(Profile_ID) AS Profile
FROM usercount
GROUP BY User_ID)
GROUP BY Profile

That should give you Profile_ID: hopefully you've got a Profile table you
can join that with to get the Profilename.
 
R

R_wilson1

Hi Doug,

I did find something similar to your code but yours worked almos
perfectly. The grouping was in alphabetical order which gave me a fals
count.

There are 47 named of profiles that are grouped into 4 types. I need t
have the profile types in the report.

I took your suggestion and did create a 2nd table (profile1) with an I
number, Profile type and Profile name then joined the Profile and Use
tables into a query (qryuserscount). I had to adjust some of the fiel
and table names but all in all it is working the way you have create
it. This is what I ended up with:

SELECT ID, Count(*) AS [Profile Count], qryuserscount.Profile_ID
FROM qryuserscount INNER JOIN [SELECT Users, Min(ID_pro) AS ID
FROM qryuserscount GROUP BY Users]. AS Profile1 ON qryuserscount.ID_pr
= Profile1.ID
GROUP BY ID, qryuserscount.Profile_ID;

Thanks for your hel
 
Top