Search code examples
sqlms-accessms-access-2016

MS Access, getting total values from two tables

I am trying to get the total values from tables "sales" and "payment" where the customer_id in both tables match the customer selected from a combo box in a form.

Table 1: Sales

customer_id item    item_value
1           Fan     $200
3           AC      $500
1           Iron    $50

Table 2: Payment

customer_id amount
1           $150
2           $300
1           $50
4           $100

I am not sure how to write a query to get the following result:

Query Result

customer_id total_purchase_amount   total_paid_amount
1           250                     $200

Your help is appreciated! Thanks in advance

Solution

  • You can use correlated subquery : 

    select s.customer_id, sum(s.item_value) as total_purchase_amount,
       (select sum(p.amount) 
        from Payment p 
        where p.customer_id = s.customer_id
       ) as total_paid_amount
from Sales s
where s.customer_id = ? -- you can pass here customer_id
group by s.customer_id;