Help formatting a report

HOTWATT used Ask the Experts™
I have a query (attached) that shows a part number and how many of that part # we shipped. I narrowed the query down to the last two years. I created a report (attached) and it is fine. But now I want to create a less detailed report that just shows each part #, # of shipments 2015, Total quantity shipped 2015 , # of shipments 2016,  Total quantity shipped 2016
 I am having trouble figuring out how to go about getting my report to look like this. Any help would be much appreciated!

Basically want it to look like this. I am using the first part # from the image of the report I attached.
Part #              # of shipments 2015   Total quantity 2015        # of shipments 2016         Total quantity  2016
AC0201010000            9                                     405                                    7                                     581
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
create a query against your query like this

IM_Key As PartNumber,
Sum(IIF(Year([TX_Date])=2015,1,0)) As [# of Shipments 2015],
Sum(IIF(Year([TX_Date])=2015,[TX_Qty],0)) As [Total Quantity 2015],
Sum(IIF(Year([TX_Date])=2016,1,0)) As [# of Shipments 2016],
Sum(IIF(Year([TX_Date])=2016,[TX_Qty],0)) As [Total Quantity 2016]
From Query2
Group By IM_Key
Order By IM_Key, Year([TX_Date])

if that does not give you what you need
export the record source of the report to excel and upload the excel file.
Hamed NasrRetired IT Professional

Upload a database recreating the issue.


Thanks for all the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial