Calculating a value from different tables

[oasis123]

I have this configuration:
Table1Debit: ID(auto), Name(text), Amount(number)
Table2Credit: ID(auto), Name(text), Amount(number)

Query1: SELECT Amount FROM Table1Debit WHERE Name = "John"
Query2: SELECT Amount FROM Table1Credit WHERE Name = "John"

How can I calculate a balance substracting all the debits from the credits of that person?
Please, don't suggest a change in my configuration. Obviously this is a simplification. I use the queries as subforms in my main Form.

 

[arnelgp]

Advice put in one table with addnal field and put db for debit cr for credit.

Now in your question create new qry.

Select top 1 (select sum(amount) from table1debit where [name]="john")-(select sum(amount) from table1credit where [name]="john") as bal from table1debit

 

[plog]

No to your setup and no to your request to avoid givind good advice. Credits and debits belong in the same table. With a structure that does that this is trivial:

SELECT [Name], SUM(Amount) AS Balance FROM YourTable GROUP BY [Name]

Fix your tables now, or this will just be one of many symptoms you have to work around.

 

[Mark_]

Query1: SELECT Amount FROM Table1Debit WHERE Name = "John"
Query2: SELECT Amount FROM Table1Credit WHERE Name = "John"

Change both to total (TOTALS button on your ribbon) their respective values. This will give you one line for each. You can then use DLookup against your queries to return the values.

I am guessing that what you are really asking is "How do I total sales then subtract payments"?

 

[oasis123]

Thank you guys for your help. Of course I expected comments about my setup. As I said what I posted here is simply a schematic model. When I propose a problem I try always to use the minimal elements. Sure, I already know that I can use debit and credit in the same record ;-) But my DB is a complex setup of tables with their own semantic. For example, the table 'Transactions' has info about accounts transfers: From, To, Method, Desc, Date, Amount. On the other hand, the 'Payments' table has: Account, Item, Date, Amount, Period, Valid Until.
Thank you arnelgp for your suggestion. As my DB is more complex, and my knowledge of SQL is limited, I finally decided to do lookups in VBA and do the math there. Here's my code (an now it's no simplified).

Public Function GetBalance()
Dim strSQL1, strSQL2, strSQL3 As String
Dim MoneyIn, MonenyOut, Paid As Currency
Dim rs As DAO.Recordset
Dim acc As String

If Payment.Value Then
acc = Forms!Accounts_Frm2!Text109

strSQL1 = "SELECT Sum(Amount) FROM Transactions WHERE (((Transactions.To)=(SELECT ID FROM Accounts WHERE Name = '" & acc & "')));"
strSQL2 = "SELECT Sum(Amount) FROM Transactions WHERE (((Transactions.From)=(SELECT ID FROM Accounts WHERE Name = '" & acc & "')));"
strSQL3 = "SELECT Sum(Amount) FROM Payments WHERE (((Payments.Account)=(SELECT ID FROM Accounts WHERE Name = '" & acc & "')));"

Set rs = CurrentDb.OpenRecordset(strSQL1)
MoneyIn = Nz(rs.Fields(0), 0)
Set rs = CurrentDb.OpenRecordset(strSQL2)
MoneyOut = Nz(rs.Fields(0), 0)
Set rs = CurrentDb.OpenRecordset(strSQL3)
Paid = Nz(rs.Fields(0), 0)
Label187.Caption = "Balance = " & MoneyIn & " - " & MoneyOut & " - " & Paid & " = " & MoneyIn - MoneyOut - Paid
End If
End Function

 

[Pat Hartman]

There is no reason to use subselects.

The solution to your problem is to get all the data into a single recordset. We all think that the data should already be in the same table but you don't. OK, do it your way but you still need to get the data into a single recordset to work with it efficiently. You can do that using a Union query. Create the three queries you have - without the subselects - and normalize the sign by multiplying the credits by -1. Then you can sum everything.