Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Red face Unanswered: count mutiple user roles as one

    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 i can send you a sample of the database (Access 97)

    Thanks for any suggestions in the right directions.

    Rick

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Rick

    Welcome to the forum .

    Is this data contained in a table? If so you can use SQL.

    I don't understand your desired output however - based on your pseudocode I would have thought you wanted:

    Username Access
    User1 Full
    User2 Full
    User3 Read Only
    User4 Full

    ???
    Perhaps the db and more explanation about your desired output would be handy....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2005
    Posts
    4
    Hi Pootle,

    Thanks, glad to be here. I normally can read what others post and find out how to make it work for my needs.

    I guess I didn’t give the right/enough information. Yes it is in a table and I can use a SQL statement in access 97.

    What I am looking for is a count of 4 different profile types from the list of users. A user can have more then one profile and I need to count the greatest of these profiles as a 1 to that users and it will ignore the lesser profiles.

    i.e.

    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 that helps

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how does the table look?

    easiest would be to have a single "rights" number for each user, something like:
    3 = support (includes full, read-only)
    2 = full (includes read-only)
    1 = read-only
    each user has only one "rights" value - so the answer you are looking for falls straight out of the table.

    since you are struggling, you obviously have a different table structure!

    i had a go assuming that somewhere in your tables you have a many:many table "crap" including fields userID & userType

    i think this works:
    Code:
    SELECT bumf.myType, count(bumf.userID) as myCount 
    FROM [SELECT DISTINCT userID, 1 as myType FROM crap WHERE userType=1
    UNION
    SELECT DISTINCT userID, 2 as myType FROM crap WHERE userType = 2 and userID not in (SELECT DISTINCT userID FROM crap WHERE userType=1)
    UNION
    SELECT DISTINCT userID, 3 as myType FROM crap WHERE userType = 3  and userID not in (SELECT DISTINCT userID FROM crap WHERE userType=1 or userType=2)
    UNION
    SELECT DISTINCT userID, 0 as myType FROM crap WHERE userID not in (SELECT DISTINCT userID FROM crap WHERE userType=1 Or userType=2 or userType=3)
    ]. as bumf
    GROUP BY bumf.myType;
    ...but it looks very ugly - there must be a better way.

    and it doesn't report the "99" case for a user who is in "1" and "99"

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How do you know which profile is "lesser"?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by R_wilson1
    Report output
    Support = 1
    Full = 2
    Read only = 3
    Still don't get your required output.

    Anyway - how about something like:

    Code:
    SELECT Username, 
    REPLACE(MAX(SWITCH([Profile] = 'full', 'r' & [Profile], 1=1, [Profile], User)), 'rfull', 'full') AS MaxLevel
    FROM MyTable
    GROUP BY UserName
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2005
    Posts
    4

    Question

    Pootles code returns a "wrong number of arguments ..." on the Replace line.

    Me and a friend of mine came up with a code but it only works in access 2000 or better. I have 2000 on my home comp but my work only has access 97.

    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;

    Can anyone figure out how to change this into 97 code.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by R_wilson1
    Pootles code returns a "wrong number of arguments ..." on the Replace line.
    Don't get that - the args are "String to be searched", "String portion to search for", "String to replace the portion". Anywhoo - yours and Izy's code have been interferred with by Access (won't work in any version of Access).
    Quote Originally Posted by R_wilson1
    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
    should be:
    Code:
    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
    What is the SQL for qryuserscount?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Dec 2005
    Posts
    4
    You code looks like my code, only you replaced [ ] with ( ). Access 97 doesn’t like the 2nd Select in the 1st FROM field.

    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 profile.

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

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    interference etc: baffled!

    hi Pootle,

    i just copy/pasted my ugly SQL proposal from here into 2K3 - it ran as advertised.

    interference - mmm. i'm not sure why i had to use...

    [
    blahUNIONblahUNIONblah
    ]. AS bumf

    ...but 2K3 wouldn't fly until i did.

    ugly, but it works!

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You still didn't answer my question about how you're determining precedence.

    Is there a reason not to use a bitmask for your rights?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •