I have two tables - Invoices and Receipts. The Receips table has an InvoiceID field linking it to the invoice.
In the receipts table I have two fields - Applied to Invoice amount, and Total Receipt amount. For a single receipt, Total Receipt Amount=SUM(Applied to invoice) amount
There is a many to many relation here - meaning a single invoice can be related to multiple receipts and a single receipt may be related to multiple invoices.
I want to show a list of receipts in a datatable - receipt number and Total Receipt amount
If I were to do it in SQL, I would do SUM(TotalReceiptAmount) .... GROUP BY ReceiptID
In QV, If I do SUM(TotalReceiptAmount) I get the wrong figure since there are several lines for each receipt in my logical table. If I do SUM (DISTINCT TotalReceiptAmount) its better, but still not correct since if I have two receipts with same TotalReceiptAmount (for instance, 100), it will sum them as 100 instead of 100.
How can I specify QV to consider a single record from each ReceiptID?
I am attaching the reduced file - please take a look if you can. The drill down tab, the receipts table has only 1 receipt (as it should) but the total is wrong. The receipts table contains multiple records for the same receipt id.
