Code:
SELECT MARA.[Analyst Code], [Oblg EBAN].Material, [Oblg EBAN].[Cnsm repair ind], [Oblg EBAN].PRON, [Oblg EBAN].[Purchase Order], EKPO.[Purchase Requisition], EKPO.[CLIN/Sub-CLIN], EKKO.[Dlvr/call Ord #], [Oblg EBAN].[Manuf Part No], EKPO.[Document number], EKKO.[Contract Number], IIf([EKPO]![Net Order Price]<[MARA]![AMDF Price],"Less","More") AS [Cost Chk], EKPO.[Net Order Value] AS [Total Contract Amt], Round(([Oblg EBAN]![Oblg Date]-[Oblg EBAN]![Date of order])/30.5,2) AS [Current ALT], MARA.[Administrative Lead Time], Round((Date()-[Oblg EBAN]![Oblg Date])/30.5,2) AS [PLT to Date], MARA.[Production Lead Time], IIf(Round((Date()-[Oblg EBAN]![Oblg Date])/30.5,2)>[MARA]![Production Lead Time],"Late","On Schd") AS [PLT Rq Chk], [Oblg EBAN].[Oblg Date], [Oblg EBAN].[Date of order], [EKPO Order Sum].[SumOfOrder Quantity] AS [Order Qty], [EKPO Order Sum]![SumOfOrder Quantity]-Sum([EKPO]![Total Quantity Shipped]) AS [Due In], LastDLV.[MaxOfMaxOfDelivery Date] AS [Last DLV Dt], LastDLV.LastOfQuantity AS [Last DLV Qty], EKPO.[Total Quantity Shipped], NxtDLV.NxtDLV AS [Next DLV Dt], NxtDLV.[Schd Qty] AS [DLV Qty], [Oblg EBAN].[SHIP TO], EKPO.Plant, ([EKPO Order Sum]![SumOfOrder Quantity]-[EKPO]![Total Quantity Shipped])*[EKPO]![Net Order Price] AS [Value of Due In], LFA1.Vendor, LFA1.[Name 1]
FROM NxtDLV RIGHT JOIN (LFA1 RIGHT JOIN (EKKO RIGHT JOIN ([EKPO Order Sum] RIGHT JOIN (EKPO RIGHT JOIN (LastDLV RIGHT JOIN (MARA RIGHT JOIN [Oblg EBAN] ON MARA.Material = [Oblg EBAN].Material) ON LastDLV.[Document number] = [Oblg EBAN].[Document number]) ON EKPO.PRON = [Oblg EBAN].PRON) ON [EKPO Order Sum].PRON = [Oblg EBAN].PRON) ON EKKO.[Purchasing Document] = [Oblg EBAN].[Purchase Order]) ON LFA1.Vendor = EKKO.Vendor) ON NxtDLV.[Purchasing Document] = [Oblg EBAN].[Purchase Order]
WHERE (((Mid([Oblg EBAN]![PRON],4,1)) Not Like "M"))
GROUP BY MARA.[Analyst Code], [Oblg EBAN].Material, [Oblg EBAN].[Cnsm repair ind], [Oblg EBAN].PRON, [Oblg EBAN].[Purchase Order], EKPO.[Purchase Requisition], EKPO.[CLIN/Sub-CLIN], EKKO.[Dlvr/call Ord #], [Oblg EBAN].[Manuf Part No], EKPO.[Document number], EKKO.[Contract Number], EKPO.[Net Order Value], MARA.[Administrative Lead Time], MARA.[Production Lead Time], [Oblg EBAN].[Oblg Date], [Oblg EBAN].[Date of order], [EKPO Order Sum].[SumOfOrder Quantity], LastDLV.[MaxOfMaxOfDelivery Date], LastDLV.LastOfQuantity, EKPO.[Total Quantity Shipped], NxtDLV.NxtDLV, NxtDLV.[Schd Qty], [Oblg EBAN].[SHIP TO], EKPO.Plant, LFA1.Vendor, LFA1.[Name 1], EKPO.[Order Quantity], EKPO.[Net Order Price], MARA.[AMDF Price]
HAVING ((([Oblg EBAN].[Oblg Date])>#4/1/2009#) AND (([EKPO Order Sum]![SumOfOrder Quantity]-Sum([EKPO]![Total Quantity Shipped]))>0));