Grouping Data

[HansBades]

I have a query that produces the line items of a Work Order as shown in the first table below. How can I modify the query to organize the same data in one record as shown with the format of the second table below? Thanks.

Work Order Number	Department	Item	Approval Code
1	Facilities	Repair Window	1
1	Facilities	Replace Carpet	2
1	Facilities	Paint Door	1
2	IT	Replace Cabinets	3

Work Order Number	Department	Item	Approval Code
1	Facilities	Repair Window, Replace Carpet, Paint Door	1, 2
2	IT	Replace Cabinets	3

[ranman256]

You cannot. Queries don’t work that way.

[Welshgasman]

Use Allen Browne's Concatenate perhaps? http://allenbrowne.com/func-concat.html

Or theDBGuy's function http://www.accessmvp.com/thedbguy/co...itle=simplecsv

[HansBades]

The Concatenate functions worked amazingly well. I was surprised how easy it was but I have a problem. My WorkOrder Table is linked to my WorkOrderSKus table in a one to many relationship with WorkOrderID in WorkOrder linking to WorkOrderIDFK in the WorkOrderSkus table. The WorkOrderSkus table has a field ApprovalCodeTypeFK that specifies the approval code for each part requisitioned for a particular Work Order. The ApprovalCodeType table links (ApprovalCodeTypeID being the PK) in a one to many relationship to the WOrkORderSkus table to the ApprovalCodeTypeFK field. So when I run the function it lists my WorkOrder Name, it groups the approval code numbers instead of the actual names of the approval codes. I would like it to group the descriptions of the approval code. Any ideas?

Table:WorkOrders
WorkOrderID	WorkOrderName	WorkOrderDate
1	Replace Cafeteria Windows	Jan 2, 2022
2	Replace Warehouse Door	Jan 4, 2022

Table:WorkOrderSkus
WorkOrderSKUID	WorkOrderIDFK	SKUs	ApprovalCodeIDFK
1	1	Window5jl	1
2	1	WindowScreen5jl	4
3	2	DoorFrame	2
4	2	CustomDoorhandle	3

Table:ApprovalCodeType
ApprovalCodeID	ApprovalCodeDescription
1	Cheapest of available parts
2	Only part available for the project
3	Not cheapest but needed to maintain cosmetic appearance
4	Special order request approved by management

WorkOrderID	ApprovalCodeTypeID
1	1,4
2	2,3

I would like it output the following:

WorkOrderID	ApprovalCodeTypeID
1	Cheapest of available parts, Special order request approved by management
2	Only part available for the project, Not cheapest but needed to maintain cosmetic appearance

[HansBades]

I figured out a way to do it. Likely not the most efficient but it works.

[June7]

Build a query that joins tables and use that as source for concatenation.

[alansidman]

An alternative is to export your query result to Power Pivot and then write two measures in DAX to present the results you wish. Here is a link to a tutorial on how to do this.

https://sfmagazine.com/post-entry/ju...a-pivot-table/