I have designed the below query to extract some data from our Oracle system. The query works fine and extracts the correct information. The problem is with the column that is supposed to return the total value of the Puchase Order (PO_TOTAL_VALUE field). This is supposed to return the sum of the three line items that compose the purchase order. Rather, it returns some unknown totals which have nothing to do with the three line items of the purchase order. I have also posted some screenshots of the query in design view in Access. Could anybody tell me what have I done wrong?
SELECT APPS_PO_HEADERS_ALL.PO_HEADER_ID, APPS_PO_HEADERS_ALL.SEGMENT1, APPS_PO_REQUISITION_LINES_ALL.ATTRIBUTE1, IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="","-",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="APW","AGREEMENT FOR PERFORMANCE OF WORK",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="APWEMER","AGREEMENT FOR PERFORMANCE OF WORK - EMERGENCY",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="CONS","CONSULTANT",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="CONCEMER","CONSULTANT - EMERGENCY",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="DFC","DIRECT FINANCIAL CONTRIBUTION",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="FELLOW","FELLOWSHIP",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="GENEXT","GENERAL EXTERNAL SERVICES",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="IMPRESTCC","IMPREST/CREDIT CARD",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="IS","INTERNAL SERVICES",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="LOA","LETTER OF AGREEMENT",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="TSA","TECHNICAL SERVICE AGREEMENT")))))))))))) AS [DOCUMENT TYPE (SERVICES)], IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="","-",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="UN","Procurement for UN/NGO",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="SP","Standard Procurement",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="RP","Reimbursable Procurement",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="EMER","EMERGENCY"))))) AS [DOCUMENT TYPE (GOODS)], HR_HR_ALL_ORGANIZATION_UNITS.NAME, APPS_PO_HEADERS_ALL.CREATION_DATE, APPS_PO_HEADERS_ALL.AUTHORIZATION_STATUS, APPS_PO_HEADERS_ALL.APPROVED_DATE, APPS_PO_HEADERS_ALL.COMMENTS, APPS_PO_HEADERS_ALL.CLOSED_DATE, APPS_PO_HEADERS_ALL.CLOSED_CODE, AP_AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID, AP_AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE, APPS_PO_VENDORS.VENDOR_NAME, APPS_PO_HEADERS_ALL.VENDOR_ID, AP_AP_INVOICES_ALL.INVOICE_DATE, AP_AP_INVOICES_ALL.INVOICE_NUM, AP_AP_INVOICES_ALL.INVOICE_AMOUNT, AP_AP_INVOICES_ALL.INVOICE_CURRENCY_CODE, AP_AP_INVOICES_ALL.GL_DATE, AP_AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE, AP_AP_INVOICES_ALL.PAYMENT_CROSS_RATE, AP_AP_INVOICES_ALL.DESCRIPTION, AP_AP_INVOICES_ALL.EXCHANGE_RATE, AP_AP_INVOICES_ALL.BASE_AMOUNT, Sum(IIf(Nz([APPS_PO_LINES_ALL]![QUANTITY],0)*Nz([APPS_PO_LINES_ALL]![UNIT_PRICE],0)*Nz([APPS_PO_DISTRIBUTIONS_ALL]![RATE],1)=0,Nz([APPS_PO_LINES_ALL]![AMOUNT],0)*Nz([APPS_PO_DISTRIBUTIONS_ALL]![RATE],1),Nz([APPS_PO_LINES_ALL]![QUANTITY],0)*Nz([APPS_PO_LINES_ALL]![UNIT_PRICE],0)*Nz([APPS_PO_DISTRIBUTIONS_ALL]![RATE],1))) AS PO_TOTAL_VALUE, APPS_PO_LINES_ALL.QUANTITY, APPS_PO_LINES_ALL.UNIT_PRICE, APPS_PO_LINES_ALL.AMOUNT, APPS_PO_DISTRIBUTIONS_ALL.Rate
FROM (((((((((((APPS_PO_HEADERS_ALL LEFT JOIN APPS_PO_DISTRIBUTIONS_ALL ON APPS_PO_HEADERS_ALL.PO_HEADER_ID = APPS_PO_DISTRIBUTIONS_ALL.PO_HEADER_ID) LEFT JOIN APPS_PO_REQ_DISTRIBUTIONS_ALL ON APPS_PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID = APPS_PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID) LEFT JOIN APPS_PO_REQUISITION_LINES_ALL ON APPS_PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID = APPS_PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID) LEFT JOIN APPS_PA_PROJECTS_ALL ON APPS_PO_DISTRIBUTIONS_ALL.PROJECT_ID = APPS_PA_PROJECTS_ALL.PROJECT_ID) LEFT JOIN AP_AP_INVOICE_DISTRIBUTIONS_ALL ON APPS_PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID = AP_AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID) LEFT JOIN AP_AP_INVOICES_ALL ON AP_AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_AP_INVOICES_ALL.INVOICE_ID) LEFT JOIN HR_HR_ALL_ORGANIZATION_UNITS ON APPS_PA_PROJECTS_ALL.CARRYING_OUT_ORGANIZATION_ID = HR_HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID) LEFT JOIN APPS_XXWRP_SERVICE_REG_ALL ON APPS_PO_HEADERS_ALL.PO_HEADER_ID = APPS_XXWRP_SERVICE_REG_ALL.PO_HEADER_ID) LEFT JOIN APPS_PO_VENDORS ON APPS_PO_HEADERS_ALL.VENDOR_ID = APPS_PO_VENDORS.VENDOR_ID) LEFT JOIN APPS_PO_LINES_ALL ON (APPS_PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = APPS_PO_LINES_ALL.PO_HEADER_ID) AND (APPS_PO_DISTRIBUTIONS_ALL.PO_LINE_ID = APPS_PO_LINES_ALL.PO_LINE_ID)) LEFT JOIN APPS_XXWRP_GOODS_LINES_ALL ON APPS_PO_HEADERS_ALL.PO_HEADER_ID = APPS_XXWRP_GOODS_LINES_ALL.PO_HEADER_ID) LEFT JOIN APPS_XXWRP_GOODS_REG_ALL ON APPS_XXWRP_GOODS_LINES_ALL.REGISTRATION_ID = APPS_XXWRP_GOODS_REG_ALL.REGISTRATION_ID
GROUP BY APPS_PO_HEADERS_ALL.PO_HEADER_ID, APPS_PO_HEADERS_ALL.SEGMENT1, APPS_PO_REQUISITION_LINES_ALL.ATTRIBUTE1, IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="","-",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="APW","AGREEMENT FOR PERFORMANCE OF WORK",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="APWEMER","AGREEMENT FOR PERFORMANCE OF WORK - EMERGENCY",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="CONS","CONSULTANT",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="CONCEMER","CONSULTANT - EMERGENCY",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="DFC","DIRECT FINANCIAL CONTRIBUTION",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="FELLOW","FELLOWSHIP",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="GENEXT","GENERAL EXTERNAL SERVICES",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="IMPRESTCC","IMPREST/CREDIT CARD",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="IS","INTERNAL SERVICES",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="LOA","LETTER OF AGREEMENT",IIf([APPS_XXWRP_SERVICE_REG_ALL]![DOCUMENT_TYPE]="TSA","TECHNICAL SERVICE AGREEMENT")))))))))))), IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="","-",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="UN","Procurement for UN/NGO",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="SP","Standard Procurement",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="RP","Reimbursable Procurement",IIf([APPS_XXWRP_GOODS_REG_ALL]![PROCUREMENT_TYPE]="EMER","EMERGENCY"))))), HR_HR_ALL_ORGANIZATION_UNITS.NAME, APPS_PO_HEADERS_ALL.CREATION_DATE, APPS_PO_HEADERS_ALL.AUTHORIZATION_STATUS, APPS_PO_HEADERS_ALL.APPROVED_DATE, APPS_PO_HEADERS_ALL.COMMENTS, APPS_PO_HEADERS_ALL.CLOSED_DATE, APPS_PO_HEADERS_ALL.CLOSED_CODE, AP_AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID, AP_AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE, APPS_PO_VENDORS.VENDOR_NAME, APPS_PO_HEADERS_ALL.VENDOR_ID, AP_AP_INVOICES_ALL.INVOICE_DATE, AP_AP_INVOICES_ALL.INVOICE_NUM, AP_AP_INVOICES_ALL.INVOICE_AMOUNT, AP_AP_INVOICES_ALL.INVOICE_CURRENCY_CODE, AP_AP_INVOICES_ALL.GL_DATE, AP_AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE, AP_AP_INVOICES_ALL.PAYMENT_CROSS_RATE, AP_AP_INVOICES_ALL.DESCRIPTION, AP_AP_INVOICES_ALL.EXCHANGE_RATE, AP_AP_INVOICES_ALL.BASE_AMOUNT, APPS_PO_LINES_ALL.QUANTITY, APPS_PO_LINES_ALL.UNIT_PRICE, APPS_PO_LINES_ALL.AMOUNT, APPS_PO_DISTRIBUTIONS_ALL.Rate
HAVING (((APPS_PO_HEADERS_ALL.SEGMENT1)="201127602") AND ((APPS_PO_HEADERS_ALL.CREATION_DATE)>[AP_AP_INVOICES_ALL]![INVOICE_DATE]));
In the end I resolved he issue by creating an Access Report based on the query, and caluclating the total value of each PO in the report itself.
