Concatenating Subform Details

[eckert1961]

Hello,

I have a receipt report where I am trying to combine the details of a student's order onto 1 receipt. Currently if an order from a student contains 2 different items I will get 2 receipts containing the details for each item. All of the information contained on both receipts is correct but rather than giving the student 1 receipt they receive 2.

What I would like to do is to take the information from the 2 OrderDetailsID, from the same OrderID and combine them into one "Description" on 1 receipt. The information that I would like to include are "Qty", "Size" and "ProductName". The description, on the receipt, would look something like the following.

Qty 2, Size 5, Uniform; Qty 3, Size 6, Belt

Please let me know if you require any additional details or clarification. Thanks in advance.

Regards,
Chris

 

[eckert1961]

Question

Hi Pat,

Thanks for taking the time to reply. Currently I do have it set up as a select query so each item is on a seperate line which means that I end up printing 2 receipts for the same member. I was hoping to consolidate everything on 1 line so that I only have to print one receipt per member. The following is the SQL statement of the current query.

SELECT DISTINCT Members.FirstName, Members.LastName, Orders.OrderPaid, Members.ParentsNames, Orders.OrderDate, Products.ProductName, Orders.CheckNumber, [Order Details].Size, Sum([Order Details Query].[Line Total]) AS [SumOfLine Total]
FROM (Members INNER JOIN Orders ON Members.MemberID = Orders.MemberID) INNER JOIN (Products INNER JOIN ([Order Details] INNER JOIN [Order Details Query] ON [Order Details].OrderDetailID = [Order Details Query].OrderDetailID) ON (Products.ProductID = [Order Details].ProductID) AND (Products.ProductID = [Order Details Query].ProductID)) ON Orders.OrderID = [Order Details].OrderID
GROUP BY Members.FirstName, Members.LastName, Orders.OrderPaid, Members.ParentsNames, Orders.OrderDate, Products.ProductName, Orders.CheckNumber, [Order Details].Size, Orders.ReceiptPrinted, Orders.OrderID, [Order Details].OrderDetailID
HAVING (((Orders.OrderPaid)=Yes) AND ((Sum([Order Details Query].[Line Total])) Is Not Null) AND ((Orders.ReceiptPrinted)=No) AND ((Orders.OrderID) Is Not Null))
ORDER BY Members.LastName;

What would I need to change to make this a union query?

Thanks,
Chris

 

[eckert1961]

Additional Details

To further clarify what I'm trying to accomplish I've attached a screen shot of what my posted query results look like.

As you see I have 2 lines for the same member. What I want to be able to do is combine the information from both lines into one. The finished query would look like the following.

Nyal Babiuk Terry/Kay 21-Sep-05 Rising Sun-Bo, Rising Sun-Nunchaku 163 $27.38, $18.18 6 Foot, Chord

Is this possible with a Union Query, VBA or any other method?

Thanks,
Chris

PS: If you have problems viewing the attachment let me know and I will gladly email you the screen shot.

 

[eckert1961]

Thanks Pat,

I appreciate your feedback. I tend to forget about sorting and grouping. Thanks for the reminder.

Chris