scdallas
asked on
Crosstab query multi-value string output
I have an MS Access table that defines security setup. Access to a form is granted by task and then tasks to roles. The same form may be granted access as part of different tasks. Here is some sample data:
ROLE TASK FORM ACCESS LEVEL
AP Admin Vendor Setup AP01 All Access
AP Admin Company Setup AP01 All Access
AP Admin Payables Inquiry AP01 Inquiry Only
AP Clerk Vendor Setup AP01 All Access
AP Clerk Enter Invoice AP01 All Access
I have two separate crosstab queries where there are rows for each form name and columns for each role to display the task name (in one) and access level (in another). But for those instances where there are two or more intersections of form and role, I'd like the crosstab query to display text which concatenates the multiple values of task name.
Can this be done in MS Access? And if so, how?
Thanks so much!
ROLE TASK FORM ACCESS LEVEL
AP Admin Vendor Setup AP01 All Access
AP Admin Company Setup AP01 All Access
AP Admin Payables Inquiry AP01 Inquiry Only
AP Clerk Vendor Setup AP01 All Access
AP Clerk Enter Invoice AP01 All Access
I have two separate crosstab queries where there are rows for each form name and columns for each role to display the task name (in one) and access level (in another). But for those instances where there are two or more intersections of form and role, I'd like the crosstab query to display text which concatenates the multiple values of task name.
Can this be done in MS Access? And if so, how?
Thanks so much!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hamed Nasr
Show sample output of cross tab queries, and required output. Why there are two queries?
ASKER
I have two cross tab queries to show by form and role the first task and access level values. For the above data, the output for these looks like:
FORM APADMIN TASK APCLERK TASK
AP01 Vendor Setup Vendor Setup
AP02
AP03
FORM APADMIN ACCESS APCLERK ACCESS
AP01 All Acc ess All Access
AP02
AP03
And then I have a third query to consolidate those so that there are two columns per Role.
Realize that there are thousands of forms, hundreds of tasks, and about 50 roles. Also, I'm using the basic Access query design rather than writing total code. What I'd like, ultimately, is something that looks like:
FORM APADMIN TASKS APADMIN ACCESS
AP01 Vendor Setup, Company Setup, Payables Inquiry All Access, All Access, Inquiry Only
AP02
AP03
with the same two columns for each role value.
Does this make more sense?
FORM APADMIN TASK APCLERK TASK
AP01 Vendor Setup Vendor Setup
AP02
AP03
FORM APADMIN ACCESS APCLERK ACCESS
AP01 All Acc ess All Access
AP02
AP03
And then I have a third query to consolidate those so that there are two columns per Role.
Realize that there are thousands of forms, hundreds of tasks, and about 50 roles. Also, I'm using the basic Access query design rather than writing total code. What I'd like, ultimately, is something that looks like:
FORM APADMIN TASKS APADMIN ACCESS
AP01 Vendor Setup, Company Setup, Payables Inquiry All Access, All Access, Inquiry Only
AP02
AP03
with the same two columns for each role value.
Does this make more sense?
Have you tried the fnConcat code I provided? It will get you to:
Form Role Tasks
AP01 APADMIN Tasks Vendor Setup, Company Setup, Payables Inquiry
AP01 APADMIN Access All Access, All Access, InquiryOnly
Then you would simply change that query to a crosstab, set the Form to RowHeader, Role to ColumnHeader and Tasks to Value (Expression)
Form Role Tasks
AP01 APADMIN Tasks Vendor Setup, Company Setup, Payables Inquiry
AP01 APADMIN Access All Access, All Access, InquiryOnly
Then you would simply change that query to a crosstab, set the Form to RowHeader, Role to ColumnHeader and Tasks to Value (Expression)
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.