Crosstab query multi-value string output
Posted on 2013-12-24
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!