CODEWITHSUNDEEP
sqlms-access
AGavin
AGavin

Reputation: 53

Joining Two Queries That Use Aggregates in MS-Access

The question requires us to join multiple tables and show the balance each account has based off of orders and payments already made.

Due to MS-Access specific syntax, you're unable to join two queries in a from clause using aliases. I've tried using the UNION feature but that also doesn't seem to be working. Below you can see the two queries that I've been trying to join together.

Query 1

SELECT c.clientname, SUM(p.payment) AS Total_Paid

FROM clients c INNER JOIN payments p ON c.clientnumber = p.clientnumber

GROUP BY c.clientname

Result

[Client Name] [Total_Paid]
Client A           1000    
Client B           1500    
Client C           2000

Query 2

SELECT c.clientname, SUM(i.orderamount * i.itemprice) AS Total_Owed

FROM (clients c INNER JOIN orders o ON c.clientnumber = o.clientnumber)

INNER JOIN orderinfo i ON i.ordernum = o.ordernum

GROUP BY c.clientname

Result 

[Client Name] [Total_Owed]
Client A           1000    
Client B           2500    
Client C           3000

What I'd like to have my result be is a table that is the result of subtracting Total_Paid from Total Owed, while only using a single query to do so.

I've tried running this query as a starting point

(SELECT c1.clientname, SUM(p.payment) AS Total_Paid

FROM clients c1 INNER JOIN payments p ON c1.clientnumber = p.clientnumber

GROUP BY c1.clientname)

UNION

(SELECT c.clientname, SUM(i.orderamount * i.itemprice) AS Total_Owed

FROM (clients c INNER JOIN orders o ON c.clientnumber = o.clientnumber)

INNER JOIN orderinfo i ON i.ordernum = o.ordernum

GROUP BY c.clientname)

But the results I get are something like this

[Client Name] [Total_Paid]
Client A           1000
Client A           1000
Client B           1500
Client B           2500
Client C           2000
Client C           3000

Instead of 

[Client Name] [Total_Owed][Total_Paid]
Client A          1000       1000 
Client B          2500       1500 
Client C          3000       2000

Finished result should look something like

[Client Name] [Balance]
Client A          0 
Client B          1000 
Client C          2000

Please let me know if any clarification is needed!

Upvotes: 0

Views: 96

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would write this as:

SELECT c.clientname, p.Total_Paid, o.Total_Owed
FROM (clients c LEFT JOIN
      (SELECT p.clientnumber, SUM(p.payment) AS Total_Paid
       FROM payments p
       GROUP BY p.clientnumber
      ) as p 
      ON c.clientnumber = p.clientnumber
     ) LEFT JOIN
     (SELECT o.clientnumber, SUM(oi.orderamount * oi.itemprice) AS Total_Owed
      FROM orders o INNER JOIN
           orderinfo oi
           ON oi.ordernum = o.ordernum
      GROUP BY o.clientnumber
     ) o
     ON c.clientnumber = o.clientnumber;

Note: This returns all clients, even those with no payments or no orders. That appears to be the intend of your question.

Upvotes: 0

Roman Czerwinski
Roman Czerwinski

Reputation: 559

You have tables for clients, payments, orders, and orderinfo. It looks like the relationships are clients JOINs to payments and orders on the client_number and that orders JOINs to orderinfo in the order_number. So you will need to aggregate the payments and orders together as you have above, but instead of using a UNION, you'll need to JOIN those two aggregated amounts as derived tables. This will allow you to subtract the amounts as opposed to stacking them vertically in the results. JOINs can add columns from related tables whereas UNIONs can stack similar structured results from different queries. Since the desired comparison is side by side you'd need to JOIN to operate on the calculated metrics as if they were a part of the same row but in different columns. I used a LEFT JOIN and started with orders because I can imagine there exists a possibility for someone to order without having yet paid, and you would like to see that result still with their full balance due. An INNER JOIN would only yield results where a client has both an order and a payment. Access does not support COALESCE() or CASE expressions, so IIF() was used to get rid of the the cases without payments (which would be null and therefore cannot be used in subtraction).

SELECT
    tot_owed.clientname
    , tot_owed.Total_Owed 
    - IIF(tot_paid.Total_Paid IS NULL, 0, tot_paid.Total_Paid) AS Balance
FROM (
    SELECT 
        c.clientname
        , c.clientnumber
        , SUM(i.orderamount * i.itemprice) AS Total_Owed
    FROM 
        clients c 
    INNER JOIN 
        orders o ON c.clientnumber = o.clientnumber
    INNER JOIN 
        orderinfo i ON i.ordernum = o.ordernum
    GROUP BY 
        c.clientname
        , c.clientnumber
) tot_owed
LEFT JOIN (
    SELECT
        c.clientname
        , c.clientnumber
        , SUM(p.payment) AS Total_Paid
    FROM 
        clients c 
    INNER JOIN 
        payments p ON c.clientnumber = p.clientnumber
    GROUP BY 
        c.clientname
        , c.clientnumber
) tot_paid ON tot_owed.clientnumber = tot_paid.clientnumber

Upvotes: 1

Related Questions