Access SubQuery Help

[accesshandicap]

Tables involved:
USER_MASTER table with field values of MSAID, RACFID, USER_NAME, USER_CO_CENTER.
AP_SCREEN_ACCESS table with field values of MSA_ID, SCREEN_CODE, USER_ACCESS.

The AP_SCREEN_ACCESS table has multiple screen codes which identify the AP screens the user has access to.
Example:
AP Cancel Access should have the Screen Codes of ICD and PCN with the user_access = Y.
AP Update Access should have the Screen Codes of PCR and PCM with the user_access = Y.
To determine the users name and RACFID, we need to join the user_master table AND the ap_screen_access by MSA_ID and MSAID.

I want to create a query to return DISTINCT RACFID, User_NAME and include 2 columns stating YES if the user has AP_Cancel Access or AP Update Access. The problem is the user can have access to both screen codes which makes it return more than one value.

Is there a simple way to do this? So far, I'm only receiving errors stating too many values returned in subquery.

[Rawhide]

Post your query please.

[accesshandicap]

SELECT DISTINCT User_Master.RACFID, User_Master.User_Name, Subquery1.Screen_Code
FROM User_Master,
(SELECT User_Access, Screen_Code
FROM AP_Screen_Access WHERE User_Access ='Y' AND (Screen_Code In ('ICD', 'PCN'))) Subquery1
WHERE Subquery1.MSA_Id = User_Master.MSAID


I'm not for sure if this is correct for what I'm trying to return...

[Rawhide]

Does this get you what you want? If not please provide a sample of what you want the dataset to look like.

I hope I don't throw you by aliasing the tables. I write all of my queries this way. It's quicker to type and easier to read.

SELECT DISTINCT UM.RACFID, UM.User_Name, SA.Screen_Code, SA.User_Access
FROM User_Master As UM
Inner Join AP_Screen_Access AS SA On SA.MSA_Id = UM.MSAID
WHERE SA.User_Access ='Y'
AND SA.Screen_Code In ('ICD', 'PCN')

[accesshandicap]

No! I'm trying to join the tables using the criteria above and return the below information.

RACFID USER AP_CANCEL AP_ACCESS
333333 Jones YES YES

The problem I'm running into.. Returning a YES reply if the user has both Screen Codes of ICD and PCN with the user_access = Y on the AP_Screen_Access table or if the user has both Screen Codes of PCR and PCM with the user_access = Y on the AP_Screen_Access table. I want to return a value of YES even if only one of them has user_access = Y.

Sorry - I hope I'm explaining this correctly. See my previous notes, it explains the table lay out.