Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Calgary, AB

    Unanswered: Eliminate row with positive and negative value

    I have an access query which gives the following results.

    PurchaseOrderNum DeliveryDate SupplierNum ItemNum WarehouseNum PurchaseQty DeliveredQty
    614173 9/13/2006 861820 01268 902 12
    614173 9/13/2006 861820 01268 902 -12

    614148 9/12/2006 990050 05602 903 1
    614146 9/13/2006 864800 02544 903 58

    I need to eliminate any results where all field values are equal and the PurchaseQty and DeliveredQty are a positive and negative of the same number. This is probably pretty simple but I'm not sure how to do it. ie i need to eliminate the records in bold above.

    SELECT [tblOrderedTemp Without Matching tblReceiptTemp02].PurchaseOrderNum, [tblOrderedTemp Without Matching tblReceiptTemp02].DeliveryDate, [tblOrderedTemp Without Matching tblReceiptTemp02].SupplierNum, [tblOrderedTemp Without Matching tblReceiptTemp02].ItemNum, [tblOrderedTemp Without Matching tblReceiptTemp02].WarehouseNum, [tblOrderedTemp Without Matching tblReceiptTemp02].PurchaseQty, [tblOrderedTemp Without Matching tblReceiptTemp02].DeliveredQty
    FROM [tblOrderedTemp Without Matching tblReceiptTemp02]
    WHERE ((([tblOrderedTemp Without Matching tblReceiptTemp02].DeliveryDate)<=Date()-3) AND (([tblOrderedTemp Without Matching tblReceiptTemp02].DeliveredQty) Is Null))
    ORDER BY [tblOrderedTemp Without Matching tblReceiptTemp02].PurchaseOrderNum DESC , [tblOrderedTemp Without Matching tblReceiptTemp02].DeliveryDate DESC;
    Last edited by mi9; 09-16-06 at 20:18.

  2. #2
    Join Date
    Jul 2005

    Maybe this will help?

    SELECT tblOrdered.purchaseOrderNum, tblOrdered.deliveryDate, tblOrdered.supplierNum, tblOrdered.WarehouseNum, Sum([purchaseQty]+[deliveredQty]) AS balQty
    FROM tblOrdered
    GROUP BY tblOrdered.purchaseOrderNum, tblOrdered.deliveryDate, tblOrdered.supplierNum, tblOrdered.WarehouseNum
    HAVING (((Sum([purchaseQty]+[deliveredQty]))<>0));
    Why does your tblOrdered have both purchaseQty and deliveredQty? Your queries might be simpler if these two attributes were in different tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts