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.

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

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.