Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Group similar records in a query for a total

    Hope I explained this right in my subject......Access 2007
    I've built a database of customers and invoices. I need to put together a query for a report on open invoices to create aging statements. So I've joined Customer and Invoices in Qry1. Next is to take out paid invoices in Qry2 that gives back all invoices that are not matched to the paid table.

    Where I run into trouble is on partial payments that have multiple payments and balances due

    So there is a Qry3 for invoices that are on the paid table. I built an expression BAL subtracting payment from invoice amount. If a single partial payment is on them, and there is a balance due - my BAL returns a number and is set to criteria >0.

    The problem I need to get around is I have some invoices that have multiple payments on them. My Qry3 BAL will only deduct a payment at a time from the invoice amount, so I don't have a true balance and if a invoice is paid in full after multiple payments I'm not getting the 0 BAL to eliminate it from Qry3.

    I played around for some time bringing all the payments into the report to try grouping payments there, but was unable to eliminate paid in fulls, or calculate a balance from total payments. I would like to work this in the query.

    Is there a way to use Max or last in the query? Or a where cause on the invoice number? expression Sum([PAYMENT]) Where [INVNO] = subqry.[INVNO]?
    I can't get my head around it? Not sure there's a way.

    So the 2 things I would like in Qry3 = group together all [PAYMENT] by [INVNO], get a total of those payments, subtract that total from [INVAMT] for BAL, then keep it if BAL >0

    possible?

    (thank you!!!)

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Possible, yes. Anything is possible. Accomplishing this in one query is possible, but more complicated and harder to debug.

    I am not entirely clear on your situation, but I suggest walking towards it one step at a time using multiple queries.

    Use a query to group by invoice and sum payments. This will provide a total paid for each invoice.

    Use a second query to compare the first query against the original invoices. This way you know the sum was done correctly because it happens in the other query first. In this one, put your balance expression, which will be original invoice minus any payments.

    Hope this helps.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

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