Query to find records that exist and do not exist

[BRZ-Ryan]

I have a permissions table that stores UserID and PermissionID. This table has records that links a UserID's to PermissionID's where a user can have multiple permissions.



Column1=UserID, Column2=PermissionID
1 34
1 75
1 98
2 75
2 21
3 75
4 75

I am trying to write a query that will find which permission id's are common to all users and which are 'ala cart'. This seems like it should be simple but I cannot figure it out.

In the above example data, I'd like to retrieve 75 as being a common permission, and 34,98,21 being ala cart.

[ranman256]

select permissions, count(permissions) as countOfPerm from table

the higher counts would be common

[BRZ-Ryan]

That will not quite work.

If I got the below results I am not getting a definitive answer:
34 38
98 25
21 23
75 21

Not looking for counts here, I want the actual permissions to be listed that are common to all, and those not common to all. These can be two separate queries.

[BRZ-Ryan]

Disregard. I'm going to use VBA/DAO to get a count of agents, then get a count of each permission and if the agent count = permission count then it is common to all. All others are ala cart. Thank you

[knarfreppep]

SELECT tblPermissions.strDescription AS Permission, Count(tblUserPermissions.bytUser) AS [Number of Users with this Permission], IIf(Not Count([bytUser])=(SELECT Count(bytID) FROM tblUsers),"Yes") AS [A la carte]
FROM tblUserPermissions LEFT JOIN tblPermissions ON tblUserPermissions.bytPermission = tblPermissions.bytID
GROUP BY tblPermissions.strDescription
ORDER BY tblPermissions.strDescription;


. tblUsers has fields bytID and strName and three rows: 1,U1; 2,U2; and 3,U3
. tblPermissions has fields bytID and strdescription and three rows: 1,P1; 2,P2; and 3,P3
. tblUserPermissions has fields bytUser and bytPermission and seven rows: 1,1; 1,2; 1,3; 2,1; 2,2; 3,1; and 3,3