Help with Unmatch Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lori - 17 Apr 2008 13:15 GMT

In Access 2000 I have 3 tables: Profile, Privilege, and
Profile_Privilege_Reference. I created a query below that lists the
privileges for each profile by department. Department is stored in the
Privilege and Profile tables.

I need to create a query that lists the privileges from the privilege table
that each profile does NOT have assigned in the profile_privelege_reference,
by department. I need the opposite of the query below:

SELECT Profile.Department, Profile_Privilege_Reference.Profile_Name,
Profile_Privilege_Reference.Job_Type,
Profile_Privilege_Reference.Privilege_Name
FROM Profile_Privilege_Reference INNER JOIN Profile ON
Profile_Privilege_Reference.Profile_Name = Profile.Profile_Name
ORDER BY Profile.Department, Profile_Privilege_Reference.Profile_Name,
Profile_Privilege_Reference.Privilege_Name;

Thanks for any help!

Reply to this Message

John Spencer - 17 Apr 2008 14:52 GMT

Three query method

Query One: Your current query
SELECT Profile.Department, Profile_Privilege_Reference.Profile_Name,
Profile_Privilege_Reference.Job_Type,
Profile_Privilege_Reference.Privilege_Name
FROM Profile_Privilege_Reference INNER JOIN Profile ON
Profile_Privilege_Reference.Profile_Name = Profile.Profile_Name
ORDER BY Profile.Department, Profile_Privilege_Reference.Profile_Name,
Profile_Privilege_Reference.Privilege_Name;

Query two: Build a list of ALL possible combinations
SELECT P.Department, R.Profile_Name, R.Job_Type, R.Privilege_Name
FROM Profile as P, Profile_Privilege_Reference as R

Query Three:
SELECT Q2.*
FROM QueryTwo as Q2 LEFT JOIN QueryOne as Q1
ON Q2.Department = Q1.Department
AND Q2.Privilege_Name = q1.Privilege_Name
WHERE Q1.Department is Null

If needed that could probably be built all into one query. Also this
query may not be updatable. If you need the ability to update records
post back and I will try to come up with a solution for that

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> In Access 2000 I have 3 tables: Profile, Privilege, and
> Profile_Privilege_Reference. I created a query below that lists the
[quoted text clipped - 14 lines]
>
> Thanks for any help!

Reply to this Message

Lori - 17 Apr 2008 15:31 GMT

I didn't get any results after running query 3 - I must have done something
wrong. Here's the SQL from Query 3:

SELECT qry_All_Profile_Privilege_Combinations.Department,
qry_All_Profile_Privilege_Combinations.Profile_Name,
qry_All_Profile_Privilege_Combinations.Privilege_Name
FROM qry_All_Profile_Privilege_Combinations
LEFT JOIN qry_Privileges_For_All_Profiles ON
(qry_All_Profile_Privilege_Combinations.Privilege_Name =
qry_Privileges_For_All_Profiles.Privilege_Name) AND
(qry_All_Profile_Privilege_Combinations.Department =
qry_Privileges_For_All_Profiles.Department)
WHERE (((qry_All_Profile_Privilege_Combinations.Department) Is Null));

Here's the SQL from Query 2 (my results had many records so I think this one
worked)

SELECT Profile.Department, Profile_Privilege_Reference.Profile_Name,
Profile_Privilege_Reference.Job_Type,
Profile_Privilege_Reference.Privilege_Name
FROM Profile_Privilege_Reference, Profile;

Thanks again for helping me!

> Three query method
>
[quoted text clipped - 47 lines]
> >
> > Thanks for any help!

Reply to this Message

Lori - 17 Apr 2008 19:34 GMT

Nevermind - I got it to work - I had a problem with how I did query 2.
Thanks for your help.

> I didn't get any results after running query 3 - I must have done something
> wrong. Here's the SQL from Query 3:
[quoted text clipped - 71 lines]
> > >
> > > Thanks for any help!

Reply to this Message