Prev: Control Select Query Output Field Type
Next: can I generate report showing which tables are used in which queri
From: duketter on 13 Jan 2010 12:47
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.
From: Bob Barrows on 13 Jan 2010 13:06
duketter wrote:
> 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? :-)


--
HTH,
Bob Barrows


From: John Spencer on 13 Jan 2010 13:12
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

duketter wrote:
> 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.
 | 
Pages: 1
Prev: Control Select Query Output Field Type
Next: can I generate report showing which tables are used in which queri