Two Report Issues: Please Help!

[Samantha]

Info:
I have used an access template which I made changes to in
order to create a database that will serve as an invoice
register. I have made a report based on a query (Invoices
and Payments) that is made up of two tables: Invoices and
Payments. This report should display all invoices that
have unpaid balances and any payments received against
them.

Issue 1: I'm unable to get the outstanding totals to work
correctly on the report. The formula is: =[InvoiceAmount]-
sum([PamentAmount]). This only works for invoices that
have payments. It does not carry a balance if there hasn't
been a payment. I have already tried to open the query and
under the PaymentAmount column entered Nz
(Payments.PaymentAmount,0) but all that did was eliminate
the records with null values from the query.

Issue 2: I need to print the a report based on the invoice
date for only invoices that have an outstanding balance.
The template already had a similar report created which is
based on an existing form that prompts a range of dates.
My problem is figuring out what to put in the Record
Source field of the report to get it to open based on the
query and range of dates as well as excluding invoices
that have a zero balance.

Thanks so much in advance! Samantha

 

[SA]

Samantha:

Issue 1 has to do with the join type you have between the tables in the
query. You probably have a join between the table invoices and payments
based on the invoice id or customer id or something similar. It is likely a
one to one join, i.e. there must be a value in both tables for the query to
produce results. To have the query show records where there is an invoice
and NO payment, you have to change the joint type. Double click on the join
line in the query window. Click the option that says something to the
effect of "Include all records in table Invoices and only those records in
table Payments where the values are equal."

Issue 2: To have the query produce results where the total payments are
less than or equal to the invoice, in your AmountDue field where you have
InvoiceAmount-Sum(NZ(PaymentAmount,0)) add a condition in the conditions
row, to be >0.

HTH