R

#### Rich

but I can't see it.

General Goal:

I'm trying to query for invoices that have not been completely paid, that

is, where one or more payments have been made, but there's still a balance

due.

++++++++++++++++++++++++++++++++++++

Data Structure:

Two tables: tblInvoices, tblPayments, linked on PK/FK InvoiceID.

Many-to-one relationship allowing multiple payments per invoice.

tblInvoices tblPayments

----------- -----------

InvoiceID [PK] PaymentID [PK]

InvoiceDate InvoiceID [FK]

InvoiceNumber PaymentDate

InvoiceAmount PaymentAmount

LateFee

DiscountPercentage

DiscountAmount

Each Invoice has a base amount (InvoiceAmount, perhaps poorly named),

modified by adding a LateFee (if any) and subracting either a

DiscountPercentage or DiscountAmount (may be zero). The true Invoice total,

as modified by the late fee and discount, is not stored in the table but is

calculated each time.

++++++++++++++++++++++++++++++++++++

The Query:

Having difficulty understanding why this query doubles an invoice amount if

it has two payments against it:

SELECT i.InvoiceID,

InvoiceAmount,

SUM ((InvoiceAmount*(1-DiscountPercentage))+LateFee-DiscountAmount) AS

InvoiceTotal,

SUM (PaymentAmount) AS TotalPayments

FROM tblInvoices AS i INNER JOIN tblPayments AS p ON i.Invoiceid=p.InvoiceID

GROUP BY i.InvoiceID, InvoiceAmount;

The query returns the following results:

"InvoiceID" "InvoiceAmount" "InvoiceTotal" "TotalPayments"

2 $35.00 $70.00 $43.33

etc.

InvoiceID Number 2 is the problem. It has no additional fees or discounts,

so the total Invoice=InvoiceAmount=$35. But, b/c there are two payments on

the invoice, it calculates "InvoiceTotal" as $70, twice the correct amount.

Invoices with only one payment are calculated correctly.

++++++++++++++++++++++++++++++++++++

My Problem:

Why? It's obviously summing the InvoiceTotal once for each of two payments,

but I don't see how to correct this. Any help anyone can offer would be

much appreciated.

Rich