Performing pivot like calculations without the use of the pivot table and parsing out list

sample.xlsx
I am attempting to query a SQL table for items which shoes a new line for each inventory and the qty in it. I am attempting to collect the individual items and display them using one query. I am also attempting to take the list in sheet 2 and without creating a pivot table, display the list (in a new sheet if necessary) as a sum of qty's by part.
Part Inv Qty
1120003-015 RE 5
1120003-015 SK 2
2123999-100 RE 5

to
Part RE SK MB
1120003-015 5 2 0
2123999-100 5 0 0

and how can I take a dynamic list and without using a pivot have it show by part number totals by part number ( I can get the pivot to show what I want but because of the pivot's restrictions, performing calculations with the sums gets difficult.

Any suggestions would be appreciated and if my explanation is too vague, please let me know.
Thanks.

This can be accomplished with a cross tab query in MS Access.

Here is the SQL statement for your example.

Please Login or Register  to view this content.

You can then export directly to Excel to do calculations as necessary

Alan

Originally Posted by ahunter488

I can get the pivot to show what I want but because of the pivot's restrictions, performing calculations with the sums gets difficult.

I agree with Alan that changing the SQL is probably easier but can you explain your comment above?

What about changing the pivot into hard data as shown in this video. Is that an option

http://www.datapigtechnologies.com/f...es/pivot8.html