SQL Query Double Counts on Multi-Table Query

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

This may be a simple problem of many-to-one returns a record for each match,
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
 
Why GROUP BY InvoiceAmount?

SELECT i.InvoiceID,
Sum (InvoiceAmount) AS Invoice_Amount,
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;
 
Thanks for the idea, Jerry. It yields the same wrong result, however. I
GROUP BY InvoiceAmount b/c in my original query, InvoiceAmount was not part
of an aggregate function. Changing to "SUM(InvoiceAmount) AS
Invoice_Amount" as you suggested returns the incorrect, doubled amount.

Any other ideas?
Rich




Jerry Whittle said:
Why GROUP BY InvoiceAmount?

SELECT i.InvoiceID,
Sum (InvoiceAmount) AS Invoice_Amount,
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;
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Rich said:
This may be a simple problem of many-to-one returns a record for each
match,
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
Click to expand...
Click to expand...
 
think about 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,
i.InvoiceAmount;

first Access will "do the joins"
so, as you said, there will be 2 records
*from the join* when there are 2 pmts

then, Access will create the groups
--> within the InvoiceID=2 group,
there are going to be 2 records
and each will have the InvoiceAmount

then, the select/aggregations
--> will sum both InvoiceAmount in that group

what to do....

group by all the fields in i,
then just use Expression instead of Sum
in the Totals row for your calc

SELECT
i.InvoiceID,
InvoiceAmount,
i.DiscountPercentage,
i.LateFee,
i.DiscountAmount,
(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,
i.InvoiceAmount,
i.DiscountPercentage,
i.LateFee,
i.DiscountAmount;

you see what is going on?

by including all fields in your calc in SELECT clause,
the calc could reference them in an *expression*....
of course, they then had to be in the GROUP BY







This may be a simple problem of many-to-one returns a record for each
match, 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
Click to expand...
 
Ha, thanks Gary! I didn't realize it was possible to create a calculated
field in the SELECT statement without an aggregate function. Your
explanation of the grouping logic was helpful.
Rich


Gary Walter said:
think about 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,
i.InvoiceAmount;

first Access will "do the joins"
so, as you said, there will be 2 records
*from the join* when there are 2 pmts

then, Access will create the groups
--> within the InvoiceID=2 group,
there are going to be 2 records
and each will have the InvoiceAmount

then, the select/aggregations
--> will sum both InvoiceAmount in that group

what to do....

group by all the fields in i,
then just use Expression instead of Sum
in the Totals row for your calc

SELECT
i.InvoiceID,
InvoiceAmount,
i.DiscountPercentage,
i.LateFee,
i.DiscountAmount,
(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,
i.InvoiceAmount,
i.DiscountPercentage,
i.LateFee,
i.DiscountAmount;

you see what is going on?

by including all fields in your calc in SELECT clause,
the calc could reference them in an *expression*....
of course, they then had to be in the GROUP BY







This may be a simple problem of many-to-one returns a record for each
match, 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
Click to expand...
Click to expand...
 
Back
Top