Order UnShipped Orders - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Order UnShipped Orders

Mohei Eldin Fouad

3 years ago

Can you help me with formatting access report according to my needs to show the CustomerName and OrderNumber and other details related to un-shipped order (Under supply) and at the same time show products for each order on the same row not in 2 or more rows according to the products customer orders? Regularly access make report like:
OrderNumber ExpectedShippingDate Product1 product2 product3
1        04/27/20231000
1        04/27/2023050
2        04/28/20230011
2        04/28/20231200
304/30/202305       0
3               04/30/20230020
Even when I remove duplication it shows same result without repeating the OrderNumber and ExpectedShippingDate
Is there a way to force access report to show such report as follows?:
OrderNumber   ExpectedShippingDate Product1 product2 product3
1        04/27/20231050
2        04/28/202312011
304/30/202305       20
Many thanks in advance

	Mohei Eldin Fouad OP @Reply 3 years ago

	Mohei Eldin Fouad OP @Reply 3 years ago
Data sources: OrderT and OrderDetailsT PorductID is foreign ID in order details table, and I tried to make a quary using Product1: IIF([ProductID]=1,[QuantityRequired],0) Product2: IIF([ProductID]=2,[QuantityRequired],0) Product3: IIF([ProductID]=3,[QuantityRequired],0) Gives me the same result as above shown picture any help?

	Kevin Robertson @Reply 3 years ago
Aggregate Query

	Mohei Eldin Fouad OP @Reply 3 years ago
Sorry to tell you aggregate query doesn't work too, take a look on the following pictures

	Mohei Eldin Fouad OP @Reply 3 years ago

	Mohei Eldin Fouad OP @Reply 3 years ago

	Mohei Eldin Fouad OP @Reply 3 years ago
even when I put vesselname (or OrderNumber) total to where doesn't give me what I asked for

	Mohei Eldin Fouad OP @Reply 3 years ago
once I put the quantity required on any format as it is or as shown on above it or in the quantityrequired itself form the table forces the query to show vesselname in another new row for the several products required

	Kevin Robertson @Reply 3 years ago
I'm assuming you want a total for each order. Change Group By to Sum for each of the Products. The Null values may also be causing problems. I would return a zero (0) instead.

	Mohei Eldin Fouad OP @Reply 3 years ago
It's worked and I got the result I want from following formula: Product1: Sum(IIf([productID]=2,[quantityrequired],Null)) Group by expression I did it without sum and grouped it by some, but Access switched it after I saved the query and reopened it, find it as above mentioned. Thank you very much for this inspiration. Or very thank you very much if you already meant what I did

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View