Calculating between two tables

[perrydaniel]

Hi I have a database put together which has two tables, one is for product and the other is for transactions.

The product table is related to the transaction table by account number.

Table 1 will provide an item which we will say is €50.00
Table 2 will show payments i.e 2 transactions for €20.00 and €20.00.

How would I write a query which shows the remaining amount to be paid. i.e €10.00.

Sorry but fairly new to calculations

Any help would be great

 

[PHV]

A starting point (SQL code):
SELECT A.account, A.amount - Sum(Nz(B.payment,0)) As TotalDue
FROM [Table 1] AS A LEFT JOIN [Table 2] AS B ON A.account = B.account
GROUP BY A.account, A.amount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[perrydaniel]

Hi PHV

Thanks, I have typed my SQL (see below), and have used T1 and T2 as my Alias for my tables, but I am getting 'Syntax Error in Join Operation'

Any suggestions

SELECT T1.AccountNo, T1.LoanAmount - Sum(Nz(T2.DrawdownAmount,0)) As Balance
FROM [T1] AS A LEFT JOIN [T2] AS T2 on T1.AccountNo = T2.AccountNo
Group By T1.AccountNo, T1.LoanAmount;

 

[PHV]

SELECT T1.AccountNo, T1.LoanAmount - Sum(Nz(T2.DrawdownAmount,0)) As Balance
FROM [real table1 name] AS T1 LEFT JOIN [real table2 name] AS T2 ON T1.AccountNo = T2.AccountNo
GROUP BY T1.AccountNo, T1.LoanAmount

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886