Filter Balance & Show Individual Line Items

  • Thread starter Thread starter GC
  • Start date Start date
G

GC

Hello All,

I hope someone can help me.

I have a report that I use to print out an invoice for customers and which
shows individual line items within the invoice (one-customer-to-many items).
Up until now, I have been using the "Sum" function to total the individual
line item charges/credits within the report and to print the "Balance Due".

Now I would like to print the invoices in bulk, and would like to print out
invoices only to customers that have a balance > 0. Can I create a query to
use in the report that will give me ALL the individual charges/credits but
only for customers whose charge/credit total has a balance > 0

I have tried modifying the query to show totals, summing the charge field,
and putting >0 in the criteria section so that I get only customers with a
balance. But this prevents all charges/credits from showing up when I print
the report because it only shows charges/credits that are >0.

Thank you,

GC
 
To achieve this, you will need to get the overall balance value into the
query that is the RecordSource for your report.

You could do this by generating another query that groups by your InvoiceID,
and sums the field that gives the balance. Include this query as a table in
your original one.

Alternatively, you could use a subquery in the WHERE clause of your original
query. The actual details will depend on the structure of the tables where
you store your invoice and payment amounts.
 
Back
Top