Databases
StackOverflow2013
Postsdbo
POHow to pass multiple values to parameter in Access?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to pass multiple values to parameter in Access?
    primarykey
    data
    text
    <h1>Background / Overall goal</h1> <ul> <li>I have an excel spreadsheet, with a list of items that certain roles need to test out. <ul> <li>Roles have members</li> <li>Roles can have member roles, which have their own users.</li> </ul></li> <li>I need to grab the total number of users who could test a given feature to grab names and count numbers for some metrics.</li> <li>I put the users, roles, and member roles into an Access DB for some quick querying</li> <li>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.</li> <li>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).</li> </ul> <h1>DB Structure</h1> <pre><code> +---------------+ +------------+ |RolesUsers | |Roles | |---------------| +---------------+ |+-----------| |ID | |Users | +-+|ID |+---+|RoleID | |---------------| | |RoleName | |UserID |+--+|ID | | +------------+ +---------------+ |UserName | | +---------------+ | | +-----------------+ | |RolesMemberRoles | | |-----------------| | |ID | +-+|RoleID | +-+|MemberRoleID | +-----------------+ </code></pre> <h1>Goal</h1> <ul> <li>Pass a list of roles to a query (either through a listed parameter or string list)</li> <li>Return a count of unique users who belong to any of those roles or any of their member roles</li> <li>Quick and dirty -- doesn't have to be fancy</li> </ul> <h1>Current Access Query</h1> <ul> <li>Returns users belonging to a role or its member roles for a given role entered via parameter</li> </ul> <blockquote> <pre><code>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); </code></pre> </blockquote> <h1>Ideas So Far</h1> <ul> <li>Found <a href="http://datapigtechnologies.com/blog/index.php/passing-multiple-values-to-one-parameter/" rel="nofollow">an interesting post</a> 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.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSeanKilleen
    1. USSeanKilleen
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to pass multiple values to parameter in Access?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to pass multiple values to parameter in Access?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to pass multiple values to parameter in Access?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHa, +1 for taking the time to draw out that table structure... very nice.
      singulars
      1. POHow to pass multiple values to parameter in Access?
      1. USJeremy Wiggins
    2. COThanks, Jeremy -- tables are courtesy of http://www.asciiflow.com/ -- I've found it to be very helpful for situations like these.
      singulars
      1. POHow to pass multiple values to parameter in Access?
      1. USSeanKilleen
    3. COYou know you can say `WHERE Field IN ("abc","def")` do you not?
      singulars
      1. POHow to pass multiple values to parameter in Access?
      1. USFionnuala
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload