Group By data from different tables based on two sums

[falahsalih]

Hi Everybody,


I have 2 tables and one query as below

1- Order Table

OrderID | CustomerID | CusromerName | OrdDate

2- Payment Table

OrderID | Amount | PYMDate

3- Order Deatails Query

OrderID | ProductName | Quantity | Price | Total ([Quantity]*[Price])

I want to group the orders based on the sum of total and sum of payments. I made another query as following :

OrderID (From order table) | Payment (from payments table) | Total (From Order Details Query )
at the Total field I set the above fields to
Group By, Sum and Sum respectively

I got true payments sum, however, the sum total are always duplicated. Any Help please ?
Note that the three objects (two tables and query ) have relation (type : one to many) by the order ID

[June7]

Post the actual SQL statement for analysis.

[falahsalih]

Post the actual SQL statement for analysis.

Actually, I have no good background in Sql. but i post the actual sql by the sql view

SELECT order.[Order ID], Sum(Querytotal.Total) AS SumOfTotal, Sum(payments.Payment) AS SumOfPayment
FROM ([order] INNER JOIN Querytotal ON order.[Order ID] = Querytotal.[Order ID]) INNER JOIN payments ON order.[Order ID] = payments.[Order ID]
GROUP BY order.[Order ID];

[June7]

What you said about wanting to "group the orders based on the sum of total and sum of payments" doesn't really make sense and the query you posted doesn't do that, it groups on the Order ID which is reasonable.

What you need to do is an aggregate query that sums the Order details and an aggregate query that sums the payments. Then join those two aggregate queries with an outer join: "Include all records from OrderDetailsSum and only those records from PaymentSum that match"

[falahsalih]

What you said about wanting to "group the orders based on the sum of total and sum of payments" doesn't really make sense and the query you posted doesn't do that, it groups on the Order ID which is reasonable.

What you need to do is an aggregate query that sums the Order details and an aggregate query that sums the payments. Then join those two aggregate queries with an outer join: "Include all records from OrderDetailsSum and only those records from PaymentSum that match"

Sorry, I have posted wrong Sql.
I have update it

[June7]

The SQL is not changed and my suggestion still applies.

[falahsalih]

The SQL is not changed and my suggestion still applies.

Well, I followed your suggestion and made 2 queries. One for sum the payments, and the other one for sum the totals. Now, can you guide me how to join the 2 queries please. Many thanks

[June7]

Use the query builder. Treat the queries like tables.

[falahsalih]

Use the query builder. Treat the queries like tables.

It very confused to join them, I did what you suggest for me, then i got some conflict date. payment of one of the costumer has transformed to another customer

[June7]

The two queries should each have the OrderID field and link them on that field.

Post sql statements for analysis.

[falahsalih]

The two queries should each have the OrderID field and link them on that field.

Post sql statements for analysis.

Done.....Thank you very much