Help with Unmatch Query

[Lori]

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!

 

[John Spencer]

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
'====================================================

 

[Lori]

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!

 

[Lori]

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