Hi all

Here's my situation:

I'm building an invoice database and am designing a form that allows
users to apply payments against invoices.

Because any one invoice may contain multiple items, I had to (?) insert
a subform on the invoice form to record each item's price and quantity
purchased; then subtotaled on the subform footer (I have had hints
after the fact that the subtotal would have better been placed on the
main invoice form; is this true, and why?); to give the invoice amount.

These are the tables I'm using,  with (what I think are) the relevant
fields only:

TBLINVOICE
invoiceID (PK)
invoicedate

TBLITEMS
itemID (PK)
cost
quantity
invoicedate (would better be named 'itemdate?')
invoiceID (FK)
paymentID (FK)

TBLPAYMENTS
paymentID (PK)
payment
date
invoiceID (FK)
itemID (FK)

* TBLINVOICES inner join TBLITEMS and TBLPAYMENTS on invoiceID
* TBLITEMS inner join TBLPAYMENTS on itemID

On the payments form (frmPayments) I have inserted a subform
(sfmInvAmt) that calculates the invoice total from the cost and
quantity fields on the "item" subform (sfmItems) on the main invoice
form (fmInvoice).

I tried to do the same thing to calculate the balance remaining using
this query (qryRemaining):

SELECT qryPayments.payment, qryPayments.invoiceID AS
qryPayments_invoiceID, qryInvAmt2.cost, qryInvAmt2.quantity,
qryInvAmt2.invoiceID AS qryInvAmt2_invoiceID
FROM qryPayments INNER JOIN qryInvAmt2 ON qryPayments.invoiceID =
qryInvAmt2.invoiceID;

Of course, it's returning a record for every instance of an item being
posted; which gives me duplicate values for the payments.

I had a vague idea for resolution that involved creating yet another
subform and getting the value from the "InvAmt" subform. I was able to
do that, but I had to set the rowsource of this new subform to the
"InvAmt" subform, which meant I couldn't include any fields from
"qryRemaining" to calculate the balance.

I racked my brain for a few days, but I now concede; I am officially
stumped.

Help.

prodeji