MS Access Query Question

[_Durandal_]

Hey Everyone,

I'm trying to figure out the best way to compare two combo box selections in Microsoft Access. Basically (as you can see from the screenshot below), I'm trying to allow a user to select the current security role, and then the newly desired role. I want there to be a compare against the two and populate the appropriate boxes below.

I'm thinking this requires a LEFT join, but it has been way too long and I'm struggling quite a bit. Any advice or tips? Thanks!

Two example roles (both stored in the same table by the way) would be as follows (see below). Obviously, the 3rd Party Admin Clerical has different roles than the 3rd Party Admin Clerical (Genpact). I would like it to compare them and spit out the difference:

 

[PTNL]

Just to be clear: You want to select the current role, and select the desired role. The "Roles to Be Removed" field would show the sub-roles from "current role", and the "Roles to Be Added" would show sub-roles from "future role".

Assuming I've rephrased this appropriately, it shouldn't be a problem. We'd have to see a sample of how the parent roles and sub-roles are related. What it appears you've provide are possible combinations; is that really how the data is organized in the DB, or is it organized in another fashion?


On a related note... Why force someone to select their current role? It's either going to be known or unknown, right? If so, just turn it into a non-editable field; the system should know what security role (if any) is assigned to the user.

 

[_Durandal_]

Thanks for the response. I guess to be more specific, I want it to compare the old role against the new role and figure out what's different in the 'new role' only. So, in the above screenshot, 'Peoplesoft User', 'KC_THIRDPTY_REP', and KC_CUSTSERV_DEFAULT_CM' are common. Those would not show up anywhere since nothing needs to be changed.

What would show up in the 'Add' are:
KC_COLL_PGM_VOID_BRK
KC_COLL_MAIN
KC_COLL_TRANSFER
KC_COLL_SPECIALIST
KC_FIN

What would show up in the 'Remove' is:
KC_CSR_NO_UPDT

The main reason for this is due to security roles for applications. People often get promoted, or switch departments, so that is the reason for selecting their current role. The group that assigns the security can then use this to quickly pick out the different roles without doing a comparison in a spreadsheet.

Some of our security roles have up to 30 sub-roles, so it can get fairly time consuming if you're trying to update multiple people. It probably seems silly, but that's how our forms are built (add x roles, remove x roles).

I can try to post a sample of the database online at some point for you to look at. I'll just make it generic, but you'll still get the idea. Thanks again for at least looking at this!

 

[Pwyl_The_Destroyer]

You could try something like this:

select role from test1 where name = 'A' and role not in 
(select role from test1 where name = 'B')

select role from test1 where name = 'B' and role not in 
(select role from test1 where name = 'A')

 

[_Durandal_]

Just wanted to say thanks - I was able to get it working based on the suggestion above and a lot more research .