Query based on 2 parameter in 2 diff tables

[duketter]

Access 2007

I am trying to create a query that will look and pull data from 2 different
tables based on a username the user inputs and an object name the user inputs
when the query is initially ran.
Table 1 shows a user name and the role the user is in
Table 2 shows all the roles and which roles are assigned to specific objects

For example: Table 1 - Bob - GL role Table 2 - GL role - object P11

I need to tie these together so if I enter "Bob" and "P11" then the query
will show me if Bob has access to P11. If not, it should be blank. I can
get separate query's to show me which roles have access to which objects and
which users are in which roles but cannot tie them together.

 

[Bob Barrows]

Access 2007

I am trying to create a query that will look and pull data from 2
different tables based on a username the user inputs and an object
name the user inputs when the query is initially ran.
Table 1 shows a user name and the role the user is in
Table 2 shows all the roles and which roles are assigned to specific
objects

For example: Table 1 - Bob - GL role Table 2 - GL role - object P11

I need to tie these together so if I enter "Bob" and "P11" then the
query will show me if Bob has access to P11. If not, it should be
blank. I can get separate query's to show me which roles have access
to which objects and which users are in which roles but cannot tie
them together.

select ur.name,ro.object
from UserRoles as ur join RolesObjects as ro
on ur.Role = ro.Role
where ur.name=[Enter user name]
and ro.object=[Enter object]

Seems pretty straightforward to me. What am I missing?

 

[John Spencer]

The SQL statement would look something like:

SELECT [Table1].[FirstName]
,[Table2].[Object]
FROM [Table1] INNER JOIN [Table2]
ON [Table1].[GL Role] = [Table2].[GL Role]
WHERE [Table1].[FirstName] = "Bob"
AND [Table2].[Object] = "P11"

In query design view
== Add your two tables
== Add the fields you want to see
== Drag from GL ROLE to GL ROLE to set up a join between the tables
== Enter "Bob" in the criteria under the name field
== Enter "P11" in the criteria under the Object field

If you wish to use parameters and be prompted for the values then
== Enter [Enter Username] in the criteria under the name field
== Enter [Enter Object Code] in the criteria under the Object field

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County