Re: Help with Unmatch Query

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 wrote:
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!
.

Relevant Pages

  • Re: Referencing Primary Key
    ... The query includes both tables, joined on the test number field from both, and presumably with a criteria to select the Profile required, and it should give you what you want. ... When I said 'Primary Key' I meant that each table needs to have a 'primary key' that it uses as, I guess, a record number. ... Once a year we have to send out a letter that lets them indicate whether or not they want to keep using the profile, as well as what the cost will be if the test isn't covered by insurance. ...
    (microsoft.public.access.macros)
  • Re: Open to suggestions
    ... My intention is to have a script that will query the AD profile attribute ... Perhaps you can pass credentials to an ADO connection ... ' Construct the ADO query, ...
    (microsoft.public.scripting.vbscript)
  • Problem setting media type for new profile
    ... I'm trying to create a custom profile with one video stream of Windows Media ... Then I query the profile manager for the IWMProfile3 interface. ...
    (microsoft.public.windowsmedia.sdk)
  • RE: Criteria query
    ... you need to create a query that normallizes your Profile table. ... SELECT JobRole, CourseTitle1 as CourseTitle FROM Profile WHERE CourseTitle1 ...
    (microsoft.public.access.queries)
  • Re: Query Timeout
    ... SouRa, rather than adjusting the query timeout setting, why dont you try to ... > Hi Herbert, ... I found a profile High ...
    (microsoft.public.sqlserver.replication)
Loading