How to prevent duplicates?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jwr - 28 Oct 2005 13:30 GMT

I have a report based upon payments received by customers. If, for
instance, the invoice that the customer is paying has several line items on
it and they make several payments, my report lists the line items over and
over again as being paid.

Example: Invoice = $500
line Items Purchased = 2
Payments $100
$400

Report shows they paid $1000 (2 payments x 2 line items)

How do I prevent this? My report is based upon a query. I am using XP Pro
and Access 2003.

thanks
JR

Reply to this Message

Al Camp - 28 Oct 2005 14:55 GMT

Your problem is with the query. If there were 4 lineItems you would your
problem quadruple the payemnts? Probably so I gather.
That indicates that the relationships between your query tables is
incorrect, causing the number of line items to create the same number of
payments.
Although there's no way to be sure, I think you may need to do a "totals"
query to collapse the LineItems to one line. A Count would give you the
number of Line Items, but present them as only one record... against your
payments.

Signature

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

>I have a report based upon payments received by customers. If, for
> instance, the invoice that the customer is paying has several line items
[quoted text clipped - 15 lines]
> thanks
> JR

Reply to this Message

jwr - 29 Oct 2005 00:05 GMT

You are correct. The query produces multiple payments.
I have not done a total query and I am not sure what you mean by "A Count".
Could you please give some guidance?

Thank you,
JR

> Your problem is with the query. If there were 4 lineItems you would your
> problem quadruple the payemnts? Probably so I gather.
[quoted text clipped - 24 lines]
> > thanks
> > JR

Reply to this Message

Al Camp - 29 Oct 2005 15:34 GMT

jwr,
I can't say for sure without seeing your data. Please answer these
questions in detail...

Is [Invoice] the total amount due for the Invoice?
Is ItemsPurchased just a count of the items on the invoice?
Do different items go into making up that count? (2 oranges + 3 limes =
5 ItemsPurchased)

If [Invoice] equals the total amount due then you shouldn't be
multiplying payments by the ItemsPurchased. In the InvoiceNo footer you
should just have...
= [Invoice] - Sum(Payments)

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

> You are correct. The query produces multiple payments.
> I have not done a total query and I am not sure what you mean by "A
[quoted text clipped - 33 lines]
>> > thanks
>> > JR

Reply to this Message

jwr - 29 Oct 2005 19:33 GMT

Following is SQL behind query.

SELECT Payments.PaymentDate, Contracts.ContractNum, SIN.SIN,
Products.ProductCode, Products.ProductName, [Order Details].Quantity,
Sum([Order Details].UnitPrice) AS SumOfUnitPrice, Customers.ControlNum,
Payments.PaymentAmount, Payments.EssettDate, Payments.EssettNumber
FROM ((Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN (((Contracts INNER JOIN (SIN INNER JOIN
Products ON SIN.SINID = Products.SINID) ON Contracts.ContractID =
Products.ContractID) INNER JOIN [Order Details] ON Products.ProductID =
[Order Details].ProductID) INNER JOIN ContractSIN_XRef ON (SIN.SINID =
ContractSIN_XRef.SINID) AND (Contracts.ContractID =
ContractSIN_XRef.ContractID)) ON Orders.OrderID = [Order Details].OrderID)
LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID
GROUP BY Payments.PaymentDate, Contracts.ContractNum, SIN.SIN,
Products.ProductCode, Products.ProductName, [Order Details].Quantity,
Customers.ControlNum, Payments.PaymentAmount, Payments.EssettDate,
Payments.EssettNumber
HAVING (((Payments.PaymentDate)>=[forms]![Report Date Range]![Beginning
Order Date] And (Payments.PaymentDate)<=[forms]![Report Date Range]![Ending
Order Date]))
ORDER BY Contracts.ContractNum DESC;

My terminology of invoice was meaning the invoice number.

Items purchased is just a count; i.e. Clin #1 = orange
Clin #2 = apple
Clin #3 = lime
3 line items purchased

I do not have a total dollar amount for all purchases on one invoice. Just
individual line items with a total $ per line item. No total of totals per
$ line item.

Does this help?

> jwr,
> I can't say for sure without seeing your data. Please answer these
[quoted text clipped - 52 lines]
> >> > thanks
> >> > JR

Reply to this Message

Al Camp - 30 Oct 2005 16:04 GMT

jwr,
Let's back up just a bit...
Orders to Payments should be a one to many relationship... simple as
that. Get that working... and later you can attach the Customer table
information and the Items information to the report/subreport queries.

On your main report each Invoice record should be derived from a Totals
query that indicates the InvoiceNo, CustomerID, InvoiceTotal, Number of
items. (the ONE) Don't try to combine payments with the main form query!
In the Detail section of your report you should have a subreport based on
Payments to show all payments against that InvoiceNo. That subreport should
be linked to the main report by InvoiceNo.

InvoiceNo Header
InvNo CustID InvAmtTotal NoOfItems
123 14 675.00 5
-----------------------------------
Subreport
InvNo PayDate PayAmt
123 1/1/05 100.00
123 2/1/05 300.00
123 3/1/05 100.00
-------------------------------------
Footer
= InvAmt - Sum(PayAmt)
-
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

> Following is SQL behind query.
>
[quoted text clipped - 100 lines]
>> >> > thanks
>> >> > JR

Reply to this Message