I’m trying to get for each part number, the highest unit price on any open PO’s with a pur-stk trantype. I have tried several subqueries with MAX (PODetail.unitcost) but still am getting duplicates. We are reviewing all Part material costs on BOMs including part costs, supplier price list base unit price and want to see what we are being charged for materials on open PO’s.
Anyone else have a way to do this?
I tried several ways including with and without PO Line. My other issue is when I have 2 or more open PO’s with the same max unit cost, but in that case I guess I can choose max(partdetail.sysrevid) Its getting the max cost that’s the main issue
The way that the MAX feature works, it will give you the max for each group by… since you have PONum and POLine in your group, it will show each PO.
Try changing this out so that this truly only summarizes the PartNum by not displaying the PO Num and Line fields. Then turn this into a sub-query, and create a TOP query that shows the open line items, and does a lookup to the sub-query that is linked by the part number.
I’ll give that a try thanks Tim
