Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    8

    Unanswered: Making a query out of two queries with common fields but display 0 for null

    Hi there,

    I am back again with my new structure that I have setup. Basically with the purchase order (PO) project I want to keep track of how much outstanding is there on each PO and how much payment the client has made on each PO.

    I have three tables setup.
    1. POInfo has PO Number and Customer fields.
    2. PODatabase has Quantity, Description, Unit Price, and PO Number fields. Each record indicates the amount of the item for what price and for which PO.
    3. POTracking has PO Number, Payment Date and Amount Paid fields. Each record indicates the amount paid for a PO at which given date.

    Now, the PO Number is linked between the three and the lookup tables are all looking up the PO Number from POInfo table.

    I have two queries which sums up two things.
    1. POTotalAmount: Sums up each PO's total amount. It is a running sum query which outputs two columns (PO Number and Total Amount).
    2. POPaidAmount: Sums up each PO's payment. It is also a running sum query which outputs two columns (PO Number and Paid Amount). I need this because the customer can split the payments into multiple segments, so one PO can have 5 payments while another may have 20.

    Now, in order to enter payments for PO, the PO Number must exist in the database. However, not all POs have received payments. I tried creating a third query which outputs a datasheet that would essentially allow me to put it on a report. The third query I want it to function as follows:
    1. Outputs all PO's Total Amount (This can be done with POTotalAmount).
    2. Outputs all sum of all payments for each PO (This can be done with POPaidAmount)
    3. Display 0 if there are no payments for a given PO.

    The problem I am encountering right now is number 3, displaying 0 for non existing payments but the PO does have an outstanding amount. I tried linking the three tables' PO Numbers together and it would only display the ones which has payment information and leave out the PO's which has no payment information.

    Is there something I can do to attack this problem? I tried searching on internet for hours and I was not able to get it to work.

    Thank you for taking your time to read my long message.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Generally speaking, you want to change the join between the table and the payments query. Probably a LEFT join, but if you right click on the join line with the query in design view, you want the option that says something like "Display all records from POInfo..."
    Paul

  3. #3
    Join Date
    Sep 2009
    Posts
    8
    Thanks for the reply. It worked great! Its displaying all the PO's even if the field is Null. However, I want the query to display 0 for the Paid Amount column if there is no data. Right now it is just left blank.

    Any input? Thanks again!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try this in place of the fieldname:

    Nz(FieldName, 0)
    Paul

  5. #5
    Join Date
    Sep 2009
    Posts
    8
    That worked perfect! Thanks pbaldy!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo!
    Paul

Posting Permissions

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