Query two tables with many-to-many relationship

[maxim]

I have two tables Orders and Payments with many-to-many relationship. Two different orders can be paid with one check and one order can be paid with several different checks. To link Orders table with Payments table I use third PaymentsOrders Table which has following layout:

PaymentsOrdersID – primary key
PaymentID – foreign key
OrderID – foreign key

I need help creating the query to show unpaid orders. I need to see difference between order amount and payment amount. I created following query which works fine if I have one order with multiple payments:

SELECT Orders.OrderID, Orders.OrderAmount, Sum(Payments.PaymentAmount) AS SumOfPayments, [OrderAmount]-[SumOfPayments] AS UnpaidAmount
FROM Payments INNER JOIN (Orders INNER JOIN PaymentsOrders ON Orders.OrderID = PaymentsOrders.OrderID) ON Payments.PaymentID = PaymentsOrders.PaymentID
GROUP BY Orders.OrderID, Orders.OrderAmount;

I run into problems when I have two different orders paid with one check. Query I created takes amount of the order and subtracts amount of the payment which is meant to cover two different orders and report will show this order as overpaid.

 

[georgedwilkinson]

You need to store the amount applied in your junction table. You don't have reliable data and cannot write meaningful queries without that.

 

[maxim]

You need to store the amount applied in your junction table. You don't have reliable data and cannot write meaningful queries without that.

99% of all payments are one order - one check. If I follow your advise data entry workload will increase because I would have to record same amount twice. Is there any way to make it automatic if order is paid in full with one check?

 

[georgedwilkinson]

Yes. Write a lot of code to handle it.

But that doesn't change what I said...the 1% is what's killing you. I submit to you that it also costs your company money.

 

[maxim]

Yes. Write a lot of code to handle it.

But that doesn't change what I said...the 1% is what's killing you. I submit to you that it also costs your company money.

I guess adding applied amount is the only easy solution. Thanks for your help.

 

[maxim]

There should be only a single data entry point. Either a payment is applied entirely to a single order or partially to multiple orders. In any case, an order needs to be provided. The difference is that instead of storing the payment amount in the payment table, you would store it in the junction table. You would use a subform that shows all unpaid orders for the customer and include a footer so you can sum payments and order amounts.

Thank you. I tied you approach. Now I see that there is no need to record check amount in payment table since it can be calculated as a sum of applied payments from junction table.
But now I have problems creating the forms. I want to be able to enter checks in the subform or in the form linked to Orders form. Subform I created does not allow me to enter check data because it cannot create new check record in payments table and in junction table simultaneously. I can create separate form to enter payments but then I have to go to Orders form and select payment from combo box which is not efficient.