Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Unanswered: Aggregate totals in queries

    I am writing a Customer Accounts access 2003 db. One of the reports needs to be 'Customers with outstanding accounts' - customers in the red.

    Because each record is the record of a transaction, each customer may have many records. I have been using calculated fields on reports to give me totals of [TransactionTotal] - [PaymentTotal] = [OutstandingAmount]; but now I need to make a report for 'Customers with Outstanding Accounts', which would require (I believe):

    1. an iif statement in a query to decide whether the account is outstanding or not; and
    2. an aggregate total of [OutstandingAmount] for each customer to base that decision upon.

    Any suggestions would be very welcome. Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on your table design

    you can find out who has outstanding balances by summing the credits and debits

    if the balance is zero then they owe nothing, how you treat credits, ie you owe them is up to you)

    you shouldn't need to use an IIF for this.

    so you could run your first query to identify who has an outstanding balance, use that (as a subselect linked by customer number) as part of the feed to main query used in the statement report which identifies the transactions or inovices and so on as part of the statment report. traditonally statements are sent out periodically and you would need to screen items on the statement which predate the statement

    a customer wouldn't want to see a list of every transaction just the ones in the current statement period (along with any outstanding invoices)

    most accounting system allocate a specific payment to a specific invoice, they may make a single payment which straddles many invoices, they may make a partial payment on an invoice. sometimes how you habdle that is upto your accounts procedures
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    Now I have discovered how to use the 'Sum' function in queries life is looking a whole lot nicer. I am ashamed I didn't know it before.

    Thanks for the reply, sorry to waste your time.

Posting Permissions

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