Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Returning Too Many Records? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following query that shoud return incomplete transactions only with:

    Missing Payment Records

    Zero Payments

    It seems to work with the exception of 2nd R/C 636 transaction, this is a balance transaction and it should not be returned.

    R/C 636 has a charge record with $30.00 charge and a payment record with $30.00 payment

    Why is 2nd R/C 636 transaction in the query?

    Hope this is enough information to fix the problem

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Returning Too Many Records? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Are we supposed to unravel the meaning of your attachment?
    Please attach a zipped database.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Returning Too Many Records? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following query that shoud return incomplete transactions with:

    Missing Payment Records

    Zero Payments

    It seems to work with the exception of 2nd R/C 636 transaction, this is a balance transaction and it should not be returned.

    R/C 636 has a charge record with $30.00 charge and a payment record with $30.00 payment

    Why is 2nd R/C 636 transaction in the query?

    Hope this is enough information to fix the problem

    Thanks, John

    Try running qry_RT_tbl_RevTran_MasterDetail_All_02, all dates

    Legend:

    A balance transaction has a one or more charge records and one or more payment records

    The charge record has the charge amount

    The payment record has the cash or check payment amount

    They are linked by RTM ID

    OK - Zero Payment - translates to a balance transaction with a charge and payment record and a legimate zero charge and zero payment, this is OK

    No Payment - Zero Payment - translates to missing payment record

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Returning Too Many Records? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The problem is that null+something = null, not something. You must replace the expressions like

    Sum(A + [img]/forums/images/smilies/cool.gif[/img]

    with either

    Sum(A) + Sum([img]/forums/images/smilies/cool.gif[/img]

    or with

    Sum(Nz(A,0) + Nz(B,0))

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Returning Too Many Records? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Great detective work

    Thanks, John

Posting Permissions

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