Multiple Payment Instances

[luckysarea]

Please help me identify my mistake:

My db has 4 tables:

Orders Table

Order ID

Order Date

Order Details Table

Order Details ID

Quantity

Rate

Payments Table

Payment ID

Order ID

Amount Received

Here is the relationship between them:


I'm using ORDERS and ORDER DETAILS (Two Tables) as a single order may have multiple products.

Now i have made a query to Calculate BALANCE AMOUNT:


In this query you see two records. In both the records ORDER DATE, QUANTITY, RATE, AMOUNT BILLED are same. But AMOUNT RECEIVED are different. This is because i have entered two part-payment records for a particular Order ID in the PAYMENTS Table.

The problem is that the above query would double the AMOUNT BILLED (on summation). What should i do to correct this.

Thankx in advance!

[kennejd]

I think you'll need 2 summary queries...one order query grouped by order id with the total amount ordered. Then you'll need a payment query grouped by order id with the total payment amount. Finally, create another query and add these 2 summary queries as the tables. Join on order id.

[NTC]

one query oriented solution is to change the query to an aggregate query - the epsilon E symbol and group the duplicate fields...... but not sure that's really ideal either.

depends a bit on your goal. In terms of creating a report - the normal subreport relationship of the payments will link to the order and so that order is not going to show twice.....

[luckysarea]

I think you'll need 2 summary queries...one order query grouped by order id with the total amount ordered. Then you'll need a payment query grouped by order id with the total payment amount. Finally, create another query and add these 2 summary queries as the tables. Join on order id.

Hey Thanx!! I got the logic. My query is working fine now.

Thank you NTC for trying to help me.