Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula needed (2000)

    Hi!!

    I have a query with the following SQL:

    SELECT tblBilling.AccountID, tblBilling.DateWorked, tblBilling.InvoiceTotal, tblBilling.InvoiceNo, tblBilling.DateBilled, tblPayment.DatePaid, tblPaymentAllocations.InvoiceNoReference, tblPaymentAllocations.AmountAllocated, [invoicetotal]-[amountallocated] AS Balance
    FROM tblPayment RIGHT JOIN (tblDemographics LEFT JOIN (tblBilling LEFT JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblDemographics.AccountID = tblBilling.AccountID) ON tblPayment.PayID = tblPaymentAllocations.PayID
    ORDER BY tblPayment.DatePaid;

    This works fine to show the balance left after X amount has been allocated.

    I need to be able to see the overall balance left for the invoice number
    after each allocation.

    For example, if invoice # 201 = $300.00 and there are PaymentAllocations
    each month in the total of $50.00 each..............I need the query to show
    the overall balance in succession (if that makes sense). I haven't
    got a clue as to how to write this one.

    Thanks,
    Leesha

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

    Re: Formula needed (2000)

    Does tblPaymentAllocations contain a field or fields with info about the schedule of payments, for example dates, or start date and interval?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula needed (2000)

    Hi Hans,

    No, tblAllocations contains info re the amount allocated and references the invoice number. tblPayment is the table that contains data regarding date paid.

    Leesha

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

    Re: Formula needed (2000)

    Does the following do what you want?

    SELECT tblBilling.AccountID, tblBilling.InvoiceNo, tblPaymentAllocations.PayAllocID, tblBilling.InvoiceTotal, tblPaymentAllocations.AmountAllocated, [InvoiceTotal]-[AmountAllocated]-Val(nz(DSum("AmountAllocated","tblPaymentAllocatio ns","InvoiceNoReference=" & [tblPaymentAllocations].[InvoiceNoReference] & " AND PayAllocID <" & [tblPaymentAllocations].[PayAllocID]),0)) AS Balance
    FROM tblPayment INNER JOIN (tblBilling INNER JOIN tblPaymentAllocations ON tblBilling.InvoiceNo = tblPaymentAllocations.InvoiceNoReference) ON tblPayment.PayID = tblPaymentAllocations.PayID
    ORDER BY tblBilling.InvoiceNo, tblPaymentAllocations.PayAllocID;

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula needed (2000)

    Hi Stranger!

    I'll give it a shot.

    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula needed (2000)

    All I can say is .................OMG! I'm in awe. It worked beautifully!

    Thanks,
    Leesha

Posting Permissions

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