matching on multiple records in two tables to find a match

[JillW]

Hi, hope someone can help. I have two tables: Table 1 contains information
at the user level and has 4 fields, user name, application, Transaction, and
Access level.
Table 2 contains information at a group level, and has 4 fields, group name,
application, transaction, and access level.

I am trying to resolve which users have the transactions and access levels
that match what a particular group has.
For example Table 1 has the following entries:
User name App Trans Access
jill A oper 10
jill A etab 10
jill A jetp 10

Table 2 has the following entries
group App Trans Access
Red A oper 99
Red A etab 10
Red A jeti 11
Green A oper 10
Green A etab 10
Green A jetp 10
Blue A oper 10
BLue A agnt 49

The logic would resolve that Jill is a Green group, because every entry in
Table 1 matching on App Tran and Access match every entry for the Green group
on app, tran and access.

I would greatly appreciate any guidance on this.

Thanks Jill

 

[Jeff L]

In a query, join your two tables together on App, Trans, and Access.
Output the Username and Group.

It might be a better design to have a table that has username and what
group they belong to. That way you can get the users settings based on
what group they are in. Just a suggestion.

 

[JillW]

Thanks Jeff, we are trying to get to the point where we have a user and what
group there are in. It's an exisitng system and we are in the process of
superimposing groups on top of individual access. I've tried a number of
ways to match up the app, trans and access but what the result I get give me
multiple groups because it is evaluating each row in the first table with
single row in the group table. Could you take a crack at suppling the actual
code as everything I have tried isn't getting me a single group.

 

[Jeff L]

Here's the SQL for the query:

Select UserName, Group

From Table1 Inner Join Table2 ON (Table1.App = Table2.App) And

(Table1.Trans = Table2.Trans) And (Table1.Access = Table2.Access);

Hope that helps!

 

[Conan Kelly]

JillW,

Based on what you are describing, just GROUPING your query should give you the results you are looking for. If you are in Query
Design view, click the Totals button on the toolbar. Group By should be the default entry in the Total: line below. If not, change
it to Group By for both the Username and Group fields.

OR

Add...

GROUP BY [name of Username field], [name of Group field]

....right after the FROM/WHERE statements and before any ORDER BY statements.

Hope this helps,

Conan Kelly

 

[JillW]

Hello, well after a long weekend I'm back trying to resolve this query.
Thanks for all you help.
So here is the query:

SELECT Table1.user, Table2.GROUP, Table1.comp, Table1.access, Table2.COMP,
Table2.ACCESS
FROM Table1 INNER JOIN Table2 ON (Table1.tran = Table2.TRAN) AND (Table1.app
= Table2.APP) AND (Table1.comp = Table2.COMP) AND (Table1.access =
Table2.ACCESS);

and here is the result

user GROUP Table1.COMP Table1.ACCESS Table2.COMP Table2.ACCESS
JILL GREEN OPER 10 OPER 10
JILL BLUE OPER 10 OPER 10
JILL RED ETAB 10 ETAB 10
JILL GREEN ETAB 10 ETAB 10
JILL GREEN JETP 10 JETP 10


Any suggestions?

Thanks

JillW,

Based on what you are describing, just GROUPING your query should give you the results you are looking for. If you are in Query
Design view, click the Totals button on the toolbar. Group By should be the default entry in the Total: line below. If not, change
it to Group By for both the Username and Group fields.

OR

Add...

GROUP BY [name of Username field], [name of Group field]

....right after the FROM/WHERE statements and before any ORDER BY statements.

Hope this helps,

Conan Kelly

Thanks Jeff, we are trying to get to the point where we have a user and what
group there are in. It's an exisitng system and we are in the process of
superimposing groups on top of individual access. I've tried a number of
ways to match up the app, trans and access but what the result I get give me
multiple groups because it is evaluating each row in the first table with
single row in the group table. Could you take a crack at suppling the actual
code as everything I have tried isn't getting me a single group.

 

[Jeff L]

Make a query and paste this into the SQL view:
TRANSFORM First([Table2].[Trans]) AS Expr1
SELECT Query1.UserName, Query1.Group
FROM Query1
GROUP BY Query1.UserName, Query1.Group
PIVOT Query1.Table1.Trans;

This will create a crosstab query. Substitute Query1 with your query
name. Now create another query and use the cross tab query. Output
Username and Group and in the fields for the Trans, put Is Not Null in
the criteria. I have tested this and when I ran it, it told me that
Jill was in Group Green.

 

[JillW]

Thanks Jeff... that works great.... Have a great weekend.