• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

MSAccess SQL to manipulate results unbound listboxes

I have an unbound Access form that has some unbound listboxes. The purpose of the form is to show a user and their assigned roles and the status of the role. I will give some data to explain what it is that I am trying to achieve.

User 1 has roles zcommon, role1 and role 2
User 2 has roles zcommon, role 1 and role 3

A role may be excluded from all users or specifically from nominated user/s.
Roles excluded from all users are updated into a table called LegacyRoleSignoff with fields role (PK) and mapping_signoff (number). The mapping_signoff field has a value of 1 (incomplete), 2 (complete), 4(Excluded for all users).
Roles excluded from specific users are held in a table called LegacyRoleUserWriteOff with fields role (PK), user (PK)  and mapping_signoff (number). The mapping_signoff field has a value of 3 (Removed for this user).

So for the example data, my table data is

LegacyRoleSignoff
zcommon,4     .......meaning the role zcommon is excluded from all users
role2, 2          ...... meaning that the mapping for role2 is completed

LegacyRoleUserWriteOff
role2, user1, 3      ....meaning that role2 for user1 only has been removed

If a role is assigned to a users, I only want it to be displayed in the listbox once with the most important status always being listed (rating 4,3,2,1 as highest to lowest). No explanation is required when the status is 1 or null

So, with the user selected in listbox1 (called lstLegacyUsersAll), the roles display in listbox2 (called lstLegacyRole)

user1    .........................        zcommon .......... Excluded for all users
             .........................        role1        .......    (left blank)
             .........................       role2         ...........  Removed for this user (even though there is also a completed status assigned)

user 2 .........................        zcommon .......... Excluded for all users
             .........................        role1        .......    (left blank)
             .........................        role3        .......    (left blank)


I am just caught up on the SQL in the second listbox. So far I have


SELECT LegacyRoles.role AS [Legacy Role], LegacyRoles.role_name AS Description, lut_mapping_status.mapping_completed AS [Mapping completed], LegacyRoles.valid_to AS [Role expires on]
FROM (LegacyRoles INNER JOIN LegacyUsersRolesAll ON LegacyRoles.role = LegacyUsersRolesAll.Role) LEFT JOIN (LegacyRoleAllWriteOff LEFT JOIN lut_mapping_status ON LegacyRoleAllWriteOff.mapping_signoff = lut_mapping_status.mapping_flag) ON LegacyUsersRolesAll.Role = LegacyRoleAllWriteOff.role
WHERE (((LegacyUsersRolesAll.User)=[lstLegacyUsersAll]));

In the middle of this there is reference to LegacyRoleAllWriteOff - this is a join between the two tables that hold the status of the role.

As it is, this SQL shows the status of a role excluded for one user for all users with that same role assignment. Eg above, any other user that has role 2 would show its status as removed irrspective of whether it was or not.

I am not sure how difficult this is to fix so if it goes on, I will definitely raise the points.

Ta
0
jainesteer
Asked:
jainesteer
  • 3
1 Solution
 
OnALearningCurveCommented:
Hi jainesteer,

Does the table lut_mapping_status store the Role?

If so take out the table LegacyRoleAllWriteOff of your join statement and join the LegacyUsersRolesAll to  lut_mapping_status via the role field directly.

I think this is causing your strange restults.

Hope this helps,

Mark.
0
 
jainesteerAuthor Commented:
No that table only has the status and description
0
 
OnALearningCurveCommented:
Try this:

SELECT LegacyRoles.role AS [Legacy Role], LegacyRoles.role_name AS Description, lut_mapping_status.mapping_completed AS [Mapping completed], LegacyRoles.valid_to AS [Role expires on]
FROM (LegacyRoles INNER JOIN LegacyUsersRolesAll ON LegacyRoles.role = LegacyUsersRolesAll.role) LEFT JOIN (LegacyRoleAllWriteOff LEFT JOIN lut_mapping_status ON LegacyRoleAllWriteOff.mapping_signoff = lut_mapping_status.mapping_flag) ON (LegacyUsersRolesAll.user = LegacyRoleAllWriteOff.user) AND (LegacyUsersRolesAll.role = LegacyRoleAllWriteOff.role)
WHERE (((LegacyUsersRolesAll.user)=[lstLegacyUsersAll]));


Hope this helps,

Maek.
0
 
OnALearningCurveCommented:
Glad I could help.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now