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 said:
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 said:
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.
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
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
