Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying unpaid balances (XP)

    When running a query, I have the ability to see unpaid balances by creating the aggregate function UnpaidBalance: Sum([AmtBilled] - ([PaymentAmt] -[AdjustmentAmt])), using an inner join. I want to be able to see invoices having no payments as well.

    The query uses two tables, an Invoice Master table, and a Payments table with a one-to-many relationship between the invoice number of the Invoice Master to the invoice number of the Payments table.

    When making the relationship a left outer join in the query (showing all invoice numbers in the Invoice Master and only matching records in the Payments table), I see all the invoice numbers, but only unpaid balances for the matching records. I understand why this is happening, but I am curious if this can be taken to the next level by showing the whole unpaid balance if no matching record exists in the Payments table via this query.

    It would be awesome if this is possible, so I could use this query within a form needing no additional programming!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Querying unpaid balances (XP)

    What you will need is three queries,
    1. sum the invoices by InvNo
    2. sum the payments by InvNo
    3. join the 2 by InvNo and show the sum of the InvAmt (from 1st query) and PayAmt (from 2nd query).

    An example would be:

    1. 1st query :
    SELECT Invoices.InvNo, Sum(Invoices.InvAmt) AS SumOfInvAmt
    FROM Invoices
    GROUP BY Invoices.InvNo;

    2. 2nd query :
    SELECT Payments.InvNo, Sum(Payments.PayAmt) AS SumOfPayAmt
    FROM Payments
    GROUP BY Payments.InvNo;

    3. join the 1st two queries :
    SELECT [qry Invoices].InvNo, [qry Invoices].SumOfInvAmt as InvAmt, IIf(IsNull([SumOfPayAmt]),0,[SumOfPayAmt]) AS PayAmt
    FROM [qry Invoices] LEFT JOIN [qry Payments] ON [qry Invoices].InvNo = [qry Payments].InvNo;

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying unpaid balances (XP)

    I was unaware of the IIf statement. What a tool!! The reference books I have talk about them very briefly, only stating that the statement isn't recommended in VBA usage, but very effective when used within Forms and Queries. What is the difference between the If and IIf?? Is it that If is used in VBA coding, and IIf is used within forms and queries??

    Anyway, Thank you Pat!! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

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

    Re: Querying unpaid balances (XP)

    If ... Then and IIf are both part of VBA, so they can both be used in VBA code.

    If ... Then can *only* be used in code; it can't be used in expressions in a query or in the control source of a control on a form or report.

    IIf is a function; it *can* be used in VBA code, but since code using it is harder to read and debug than code using If ... Then, it is usually better to use If ... Then when you're writing code. Like many other VBA functions, IIf can be used in expressions, and there it is very useful. Other VBA functions that are useful mostly in expressions are Choose and Switch; both can be used in situations where you would use a Select Case instruction in code - as with If ... Then, the Select Case instruction can't be used in expressions.

  5. #5
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying unpaid balances (XP)

    Thanks for the explanation. Your explanation, in addition to the reference material I was able to find, do paint a clearer picture for me. I can see myself using this function much more in the future. Thanks again!!

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Querying unpaid balances (XP)

    Be warned, though, that IIF can slow down the execution of a query because the entire expression is always evaluated, not just the portion that is true. Very complex nested IIF statements should probably be considered an indication that the query needs rebuilding.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying unpaid balances (XP)

    Thanks for the warning. I'll keep that in mind. I had read some reference material saying that in VBA use IF..Then..Else instead of IIf because of performance purposes. I'm hoping that being not very complex myself may save me from getting into very complex IIf functions.(ha ha)

Posting Permissions

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