Match Problem

[Jw]

Hi,

I am using MS Access XP with Windows XP Professional. I created two tables A
and B.

Table A
Customer Name
InvoiceNo
InvoiceAmount

Table B
Pay-InvoiceNo
Pay-InvoiceAmount
Pay-Date

I created a query and then I have already made a trust relationship (option
3) between Table A's InvoiceNo and Table B's Pay-InvoiceNo. Furthermore, I
created a Report based on the Query to list out the outstanding invoice and
invoice amount but the Report is incorrect.

I checked with Query result, I found that something is incorrect. For
example, if customer made two payment (1st payment is a deposit with second
payment is final payment) for the same Inovice. It will be automatically
added one more time in the Query to match with payment so that the same
Invoice Number with Invoice Amount will be shown on two times on the Query
and Report.

How can I solve it ??

Any information or hints is very appreciated.

Thanks
Jimmy

 

[Guest]

Sum the field Pay-InvoiceAmount in your query.
This will add the Deposit and Payed amount together thus showing only one
line in the report.