How to pass multiple values to parameter in Access?

Question

Background / Overall goal

• I have an excel spreadsheet, with a list of items that certain roles need to test out.
  • Roles have members
  • Roles can have member roles, which have their own users.
• I need to grab the total number of users who could test a given feature to grab names and count numbers for some metrics.
• I put the users, roles, and member roles into an Access DB for some quick querying
• Now, just trying to get the proper information out. I don't mind entering a list of roles and having it spit things back that way.
• I'd like to avoid adding all the test items for each role to the DB (there are a lot of them and this is supposed to be a reasonably quick task).

DB Structure

                       +---------------+
    +------------+     |RolesUsers     |
    |Roles       |     |---------------|    +---------------+
    |+-----------|     |ID             |    |Users          |
 +-+|ID          |+---+|RoleID         |    |---------------|
 |  |RoleName    |     |UserID         |+--+|ID             |
 |  +------------+     +---------------+    |UserName       |
 |                                          +---------------+
 |
 |  +-----------------+
 |  |RolesMemberRoles |
 |  |-----------------|
 |  |ID               |
 +-+|RoleID           |
 +-+|MemberRoleID     |
    +-----------------+

Goal

• Pass a list of roles to a query (either through a listed parameter or string list)
• Return a count of unique users who belong to any of those roles or any of their member roles
• Quick and dirty -- doesn't have to be fancy

Current Access Query

• Returns users belonging to a role or its member roles for a given role entered via parameter

PARAMETERS p_RoleName Text ( 255 );
SELECT Roles.RoleName, Users.UserName
FROM 

(RolesUsers INNER JOIN Users ON RolesUsers.UserID = Users.ID) 
INNER JOIN Roles ON RolesUsers.RoleID = Roles.ID

Where RolesUsers.RoleID In (Select ID from Roles where Roles.RoleName = p_RoleName)

UNION SELECT Roles.RoleName, Users.UserName
FROM 

(RolesUsers INNER JOIN Users ON RolesUsers.UserID = Users.ID) 
INNER JOIN Roles ON RolesUsers.RoleID = Roles.ID

Where RolesUsers.RoleID IN (
SELECT RolesSubroles.SubRoleID FROM RolesSubroles INNER JOIN Roles ON RolesSubRoles.RoleID = Roles.ID 
where Roles.RoleName = p_RoleName);

Ideas So Far

• Found an interesting post on how to pass a string of text into a where clause using Instr(). Not sure how I could necessarily tweak that to my situation though.

Answer 1

Suggestion: In your Access project, query the spreadsheet data using Access SQL e.g. create JOINs between Access tables and Excel data, possibly using lined tables.

Answer 2

To achieve Goal 1, you can use Remou's suggestion above to type a list of RoleNames into the WHERE clause with the following query. However, it might make more sense just to remove the 'WHERE' clause, run the query, and then paste into Excel for (Goal 3) "quick and dirty" filtering.

SELECT DISTINCT Roles_1.RoleName,
            Users.UserName
FROM   Users
   INNER JOIN ((Roles
                INNER JOIN (RolesMemberRoles
                            INNER JOIN Roles AS Roles_1
                              ON RolesMemberRoles.MemberRoleID = Roles_1.ID)
                  ON Roles.ID = RolesMemberRoles.RoleID)
               INNER JOIN RoleUsers
                 ON Roles.ID = RoleUsers.RoleID)
     ON Users.ID = RoleUsers.UserID
WHERE  Roles_1.RoleName In ( "Prez", "Veep", "Staffer" )
ORDER  BY Roles_1.RoleName,
      Users.UserName;  

To return the count of users capable of fulfilling the various roles (Goal 2) you can run the following.

SELECT InheritedRoles.RoleName,
   Count(*) AS NumOfTestersAvailable
FROM   (SELECT DISTINCT Roles_1.RoleName,
                    Users.UserName
    FROM   Users
           INNER JOIN ((Roles
                        INNER JOIN (RolesMemberRoles
                                    INNER JOIN Roles AS Roles_1
                                      ON RolesMemberRoles.MemberRoleID =
                                   Roles_1.ID)
                          ON Roles.ID = RolesMemberRoles.RoleID)
                       INNER JOIN RoleUsers
                         ON Roles.ID = RoleUsers.RoleID)
             ON Users.ID = RoleUsers.UserID) AS InheritedRoles
GROUP  BY InheritedRoles.RoleName
ORDER BY InheritedRoles.RoleName;  

The above SQL assumes that each Role contains itself as a Member in the table RoleMemberRoles. You should use DISTINCT because the data model doesn't prevent a single user from having multiple roles and thus appearing multiple times in each result set.