CODEWITHSUNDEEP
sqlms-access
Rick
Rick

Reputation: 2308

Access: Query specific users not in a profile

I have a table that displays user profile and users assigned.

Example

Profile    User
--------   -----
ProfileA   UserA
ProfileA   UserB
ProfileA   UserC
ProfileB   UserA
ProfileB   UserD
ProfileC   UserB
ProfileD   UserE

I am trying to create a query to verify that a group of specific users are not in the profile and display those profiles.

Example: I want to display the profiles where UserA and UserC are not assigned to.

Output Example:

Profile
--------
ProfileC
ProfileD

I am trying to create this query in MS Access.

Upvotes: 1

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Presumably, you have a table of profiles. If so, use not exists:

select p.*
from profiles as p
where not exists (select 1
                  from user_profiles up
                  where up.profile = p.profile and up.user in ('UserA', 'UserC')
                 );

If you don't have a separate table -- which would seem odd to me -- you can do this with the profiles in your table. But in this case, conditional aggregation is a simple method:

select profile
from user_profiles
group by profile
having sum(iif(user in ('UserA', 'UserC'), 1, 0)) = 0;

Upvotes: 1

Related Questions