Combined Vlookup and And functions in an Access Query

[Mary2015]

Hello everyone,
I have 2 tables :
Table 1 : listing the users and the transactions they have access to :

User	Transaction
user1	A
user1	B
user1	E
user1	C
user2	D
user2	E
user2	A

Table 2 : Listing the transactions that a single user cannot have access to at the same time.

Transaction 1	Transaction 2
A	B
D	E
A	C

I need a query that will return only users who have conflicts as defined in Table 2, The result will be as follows:

USER	Transaction 1	Transaction 2
user1	A	B
user2	D	E
user1	A	C

In Excel I would use the vlookup function with and AND condition, the data I have is loo large to be calculted in Excel so I am trying to use MS Access.
Any ideas ?
Thanks a lot for your help.

[June7]

Would you like to provide db so don't have to build one and can do some testing? Follow instructions at bottom of my post.

[Mary2015]

Thanks a lot June7, Here is the attachement.

[June7]

Since USER and Rolle pairs should be unique, need to delete duplicate records from [Roles by User].

One quick way to eliminate the duplicates is to write unique pairs to a new table.
SELECT DISTINCT [Roles by User].USER, [Roles by User].Rolle INTO Roles2 FROM [Roles by User];

Delete old table, rename new table. Set USER and Rolle fields as compound index to prevent duplicate pairs.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Having a hard time figuring out what is what. Need examples with real table and field names and real data samples. Which is "Table 1" and which is "Table 2"?

[Mary2015]

Hello,


Table 1 will be the result of Query 1 named “users with Access to sensitive transactions”, here is an example:

User	Rolle	T-CODE
L01	Z:FI_ALL_BUKRS_1200	AB02
L01	Z:FI_ALL_BUKRS_1200	AJRW
L01	xxxxx	xxxxx
L01	xxxxx	xxxxx

Table 2 will be the table named “Conflicts A” :

Right A (transaction code)	Right B (transaction code)	Function ID A	Functional ID 2	Risk ID
AB02	AJRW	FA01	FA02	Z099

The requested query output needs to be :

Right A (transaction code)	Right B (transaction code)	Function ID A	Functional ID 2	Risk ID	User	Rolle
AB02	AJRW	FA01	FA02	Z099	L01	Z:FI_ALL_BUKRS_1200

Thanks a lot for your help, I really appreciate the effort you are making.

[June7]

Problem is that the T-CODE values are separate records in Table1 but in one record in Table2. How do we know which Table2 column to search for the Table1 T-CODE values? How should Access know that ABO2 and AJRW from Table1 should be searched for as a pair in Table2?

Also, there is issue of duplicate records in Table1, as noted in previous post. Set the query for Unique Values to eliminate dups.

I tried to do a query using DLookup expressions but performance was really slow and it wasn't producing desired results anyway. Not seeing a query solution. Think even VBA would be difficult.

[Mary2015]

You are right, I think the Dlookup done twice will solve the issue, It like RightA should be looked up in T-code and RightB also looked up in T-code,
could you please provide me a small example on How the Dlookup function is correctly aggregated and I will try to solve the performance issue, maybe splitting the user list into 2 parts.
many thanks.

[June7]

Sorry, not seeing how DLookup can accomplish this. I made one more attempt with queries.

Query1
SELECT [Query 1 :Users with Access to sensitive transactions].User, [Query 1 :Users with Access to sensitive transactions].[T-CODE], [Query 1 :Users with Access to sensitive transactions].Rolle
FROM [Query 1 :Users with Access to sensitive transactions]
WHERE ((([Query 1 :Users with Access to sensitive transactions].[T-CODE]) In (SELECT [Right A (transaction code)] FROM [Conflicts A])));

Query2
SELECT [Query 1 :Users with Access to sensitive transactions].User, [Query 1 :Users with Access to sensitive transactions].[T-CODE], [Query 1 :Users with Access to sensitive transactions].Rolle
FROM [Query 1 :Users with Access to sensitive transactions]
WHERE ((([Query 1 :Users with Access to sensitive transactions].[T-CODE]) In (SELECT [Right B (transaction code)] FROM [Conflicts A])));

Query3
SELECT Query1.User, Query1.[T-CODE], Query2.[T-CODE], Query1.Rolle
FROM Query2 INNER JOIN Query1 ON (Query2.Rolle = Query1.Rolle) AND (Query2.User = Query1.User);

Query4
SELECT [Conflicts A].[Right A (transaction code)], [Conflicts A].[Right B (transaction code)], [Conflicts A].[Function ID A], [Conflicts A].[Functional ID 2], [Conflicts A].[Risk ID], Query3.User, Query3.Rolle
FROM [Conflicts A] INNER JOIN Query3 ON ([Conflicts A].[Right B (transaction code)] = Query3.Query2.[T-CODE]) AND ([Conflicts A].[Right A (transaction code)] = Query3.Query1.[T-CODE]);

If that doesn't work, the issue is beyond my abilities with SQL and maybe VBA is only option.

[Mary2015]

Many thanks, I will test those queries and let you know. It is definitely a challenge.