count mutiple user roles as one

[R_wilson1]

Hello,

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

i.e.
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_id = support_id isnull then
Count read only as 1

Eles
Count as unknown

End if
End if
End if

Form
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 = 3

I hope this is clear enough, if not I can email a sample of the database (Access 97)

Thanks for any suggestions in the right directions.

Rick

 

[GumbyD]

Rick - I think you will need to post your database. When you say user profiles are you talking about the object level security settings in this Access database or just some data about user profiles that you have stored in an Access database?

 

[R_wilson1]

Sorry, I was on the road for a few days.

What I have here is Users that can have multiple Profiles. I need to count the greater profile for that User and ignore duplicates and lesser profiles.

Need to group the Users, Group the Profile with in the Users, find the greater Profile and ignore the lesser profile, and Count the greater profiles.

I can get all of these things except to ignore the lesser profile, it wants to count the lesser profiles as well.

 

[GumbyD]

Rick -

Can you post your database so I can see what you are looking at. If you are getting user information from the Access security area are the three divisions you are talking about Groups you have created (Support, Full, and Read Only) or is this a database that tracks a users access to some other system?

 

[R_wilson1]

Sorry, the Holidays are very busy.

this tracks a users access to some other system, not the securety within access. I did find the code that i was looking for.

SELECT Profile1.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_pro = Profile1.ID
GROUP BY Profile1.ID, qryuserscount.Profile_ID;

Thnaks anyways