Re: How to prevent duplicates?
- From: "Al Camp" <anon@xxxxxxxx>
- Date: Sun, 30 Oct 2005 10:04:40 -0500
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
"jwr" <joyrose@xxxxxxxxxxxxx> wrote in message
news:GdP8f.29872$Pp1.9545@xxxxxxxxxxxxxxxxxxxxxxxxx
> 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?
>
> "Al Camp" <anon@xxxxxxxx> wrote in message
> news:ubQLpXJ3FHA.3732@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>>
>> "jwr" <joyrose@xxxxxxxxxxxxx> wrote in message
>> news:E6y8f.28780$Pp1.25888@xxxxxxxxxxxxxxxxxxxxxxxxx
>> > 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
>> > "Al Camp" <anon@xxxxxxxx> wrote in message
>> > news:%23arW9c82FHA.2564@xxxxxxxxxxxxxxxxxxxxxxx
>> >> 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.
>> >> --
>> >> hth
>> >> Al Camp
>> >> Candia Computer Consulting - Candia NH
>> >> http://home.comcast.net/~cccsolutions
>> >>
>> >> "jwr" <joyrose@xxxxxxxxxxxxx> wrote in message
>> >> news:LPo8f.16741$NJ.12535@xxxxxxxxxxxxxxxxxxxxxxxxx
>> >> >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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>
.
- References:
- How to prevent duplicates?
- From: jwr
- Re: How to prevent duplicates?
- From: jwr
- Re: How to prevent duplicates?
- From: Al Camp
- Re: How to prevent duplicates?
- From: jwr
- How to prevent duplicates?
- Prev by Date: Re: Format page trouble
- Next by Date: Re: Why do my Access reports print micro-size on various printers?
- Previous by thread: Re: How to prevent duplicates?
- Next by thread: Data from multiple tables on one report
- Index(es):
Relevant Pages
|
