Find Unmatched Records by User and Program

[dfrazell]

I need to find all the programs that a user is not authorized to. Table A is the list of available programs and Table B is the list of programs a user is authorized for by user.

Table A - Program list
PrgName
----------
OrderEntry
Inventory
Manufacturing
Scheduling
Shipping
Purchasing
Receiving
etc.

Table B - User authorization list
User PrgName
-------- -------------------
BSMITH OrderEntry
BSMITH Shipping
BSMITH Receiving
JDOE Purchasing
JDOE Shipping
etc.

Desired Results
BSMITH Inventory
BSMITH Manufacturing
BSMITH Purchasing
BSMITH Scheduling

JDOE OrderEntry
JDOE Inventory
JDOE Manufacturing
JDOE Scheduling
JDOE Purchasing
JDOE Receiving

The unmatch query wizard doesn't give me the desire results. How can I find, by user, the programs they are not authorized to?

Thanks!

 

[PHV]

Something like this ?
SELECT B.User, A.PrgName
FROM [User authorization list] AS B, [Program list] AS A
WHERE A.PrgName NOT IN (SELECT PrgName FROM [User authorization list] WHERE [User]=B.User)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dfrazell]

For the most part it worked but it is duplicating results. Any better solutions or should a Group By fix it?

Thanks for your help!

 

[PHV]

Perhaps the DISTINCT predicate ?
SELECT DISTINCT B.User, A.PrgName
FROM [User authorization list] AS B, [Program list] AS A
WHERE A.PrgName NOT IN (SELECT PrgName FROM [User authorization list] WHERE [User]=B.User)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dfrazell]

That did it! Thanks for your help!