Joining tables

[IgorM]

Hi

I have two tables - one for debtors and one for creditors, both have the
same columns (name, number, NIP, value). I want to create a table (query)
that will combine rows from both tables but only those rows that have the
same NIP. Furthermore I want the rows to be grouped by the name with sums on
values.

I tried this query:
SELECT Debtors.name, Debtors.NIP, SUM(Debtors.values) as [Total debts]
FROM Debtors GROUP BY Debtors.name, Debtors.NIP
UNION ALL
SELECT Creditors.name, Creditors.NIP, SUM(Creditors.values)
FROM Creditors GROUP BY Creditors.name, Creditors.NIP
HAVING Creditors.NIP = Debtors.NIP;

but it won't run. The having clause is incorrect, probably.

Any ideas?
Regards
IgorM

 

[Rick Brandt]

I have two tables - one for debtors and one for creditors, both have the
same columns (name, number, NIP, value). I want to create a table (query)
that will combine rows from both tables but only those rows that have the
same NIP. Furthermore I want the rows to be grouped by the name with sums
on values.

I tried this query:
SELECT Debtors.name, Debtors.NIP, SUM(Debtors.values) as [Total debts]
FROM Debtors GROUP BY Debtors.name, Debtors.NIP
UNION ALL
SELECT Creditors.name, Creditors.NIP, SUM(Creditors.values)
FROM Creditors GROUP BY Creditors.name, Creditors.NIP
HAVING Creditors.NIP = Debtors.NIP;

but it won't run. The having clause is incorrect, probably.

SELECT [name], NIP, SUM(values) as [Total debts]
FROM
(SELECT Debtors.name, Debtors.NIP, Debtors.values
FROM Debtors
INNER JOIN Creditors On Debtors.NIP = Creditors.NIP
UNION ALL
SELECT Creditors.name, Creditors.NIP, Creditors.values
FROM Creditors
INNER JOIN Debtors On Creditors.NIP = Debtors.NIP) AS Qry1
GROUP BY [name], NIP