Summing column values from 2 tables and getting their difference

Question

I have 2 tables, ord_tbl and pay_tbl with these data:

ord_tbl

invoice | emp_id | prod_id | amount
123     | 101    | 1       | 1000
123     | 101    | 2       | 500
123     | 101    | 3       | 500
124     | 101    | 2       | 300
125     | 102    | 3       | 200

pay_tbl

invoice | new_invoice | amount
123     | 321         | 300
123     | 322         | 200
124     | 323         | 300
125     | 324         | 100

I would like the selection statement to give me this result

invoice | emp_id | orig_amt | balance | status
123     | 101    | 2000     | 1500    | unsettled

The invoice that has 0 balance will not be included anymore. This is what I tried so far...

;WITH CTE as
(SELECT ot.invoice, MAX(ot.emp_id) as emp_id, SUM(ot.amount) as origAmt FROM ord_tbl ot GROUP BY ot.invoice),
CTE2 as
(SELECT pt.invoice, SUM(pt.amountt) as payAmt FROM pay_tbl GROUP BY pt.invoice)
SELECT CTE.invoice, CTE.emp_id, CTE.origAmt, CTE.origAmt-CTE2.payAmt as bal, 'UNSETTLED' as status
FROM
CTE LEFT JOIN CTE2 ON CTE.invoice=CTE2.invoice
WHERE CTE.emp_id='101' AND CTE.origAmt-CTE2.payAmt>0 OR CTE2.patAmt IS NULL

This has been taught to me here and it works in sql server. What I need now is to have this run in ms access. I tried this code but ms access gives me an error saying "Invalid SQL statement; expected 'DELETE','INSERT', 'SELECT', or 'UPDATE'." Can you help? Thanks.

There are several problems to your code - first of all: SQL has a SELECT,INSERT or DELETE as first statement - at least in Access-SQL. Second: Either your code is more complicated than it needs to be or you are not telling everything in the Question. I created a simple SQL in my answer, with the problem that you dont say what emp_id is (is there only one per invoiceid?) and why in your example Invoice 125 gives no result — , Oct 28 '15 at 10:08

score 1 · Answer 1 · answered Oct 28 '15 at 10:08

MS ACCESS sql is poor and ACCESS doesn't know WITH instruction. I created tables (all fields int type). I rewrote query and this query works:

SELECT CTE.invoiceCTE, 
       CTE.emp_idCTE, 
       CTE.origAmtCTE, 
       CTE.origAmtCTE-CTE2.payAmtCTE2 as bal, 
        'UNSETTLED' as status
FROM 
 (SELECT invoice as invoiceCTE, 
  MAX(emp_id) as emp_idCTE, 
  SUM(amount) as origAmtCTE 
  FROM ord_tbl 
  GROUP BY invoice) as CTE
LEFT JOIN 
( SELECT invoice as invoiceCTE2, 
  SUM(amount) as payAmtCTE2
   FROM pay_tbl 
  GROUP BY invoice) as CTE2
ON CTE.invoiceCTE=CTE2.invoiceCTE2
WHERE CTE.emp_idCTE=101
    AND (CTE.origAmtCTE-CTE2.payAmtCTE2>0 OR CTE2.payAmtCTE2 IS NULL)

Forgive me but SO is not a ranting forum. The Jet/ACE Engine (that MS Access ships with by default) is not the only RDMS that does not maintain CTE using `with()` Popular open-source [MySQL](http://stackoverflow.com/questions/324935/mysql-with-clause) does not support CTE and until last year SQLlite did not either. Usually, a CTE query can be substituted wth a derived table, subquery, related view/stored query. — Parfait, Oct 28 '15 at 14:47

score 0 · Answer 2 · 2015-10-28T10:22:47.263

I don't know about emp_id. If it is some kind of customer id you'd have only one per invoice_id and you'd need this SQL:

SELECT 
  ord_tbl.invoice, 
  First(ord_tbl.emp_id) AS ErsterWertvonemp_id, 
  Sum(ord_tbl.amount) AS origAmt, 
  Sum([ord_tbl].[amount])-Sum([pay_tbl].[amount]) AS bal, 
  "unsettled" AS status
FROM 
  ord_tbl LEFT JOIN pay_tbl 
  ON ord_tbl.invoice = pay_tbl.invoice
GROUP BY ord_tbl.invoice
HAVING (((Sum([ord_tbl].[amount])-Sum([pay_tbl].[amount]))>0));

If you want to select only the ones with emp_id=101 you'd need this:

SELECT 
   ord_tbl.invoice, 
   ord_tbl.emp_id, 
   Sum(ord_tbl.amount) AS origAmt, 
   Sum([ord_tbl].[amount])-Sum([pay_tbl].[amount]) AS bal, 
   "unsettled" AS status
FROM 
   ord_tbl LEFT JOIN pay_tbl 
   ON ord_tbl.invoice = pay_tbl.invoice
GROUP BY 
   ord_tbl.invoice, 
   ord_tbl.emp_id
HAVING (
   ((ord_tbl.emp_id)=101) 
   AND 
   ((Sum([ord_tbl].[amount])-Sum([pay_tbl].[amount]))>0)
);

Summing column values from 2 tables and getting their difference

2 Answers2