Solved

# SQL Query

Posted on 2005-04-08
334 Views
hello,

I have tow tables:

CustCredit and CustDebit. i wanna write a query that gives me the balance ... for example 1000 credit - 1000 debit so balance = 0

here is what i need:

In CustCredit I got 'Amount' and in CustDebit i got 'logontime' so basically SumofAmount - SumoflogonTime

can somebody help me with this plz

Thanks

0

LVL 65

Expert Comment

Assuming you have some kind of Account Number that serves as a relatable key between these two tables...

SELECT CustDebit.logontime - CustCredit.Amount AS YourBalance
FROM CustDebit
INNER JOIN CustCredit ON CustDebit.AccountNumber = CustCredit.AccountNumber

btw how does 'logontime' equate to a currency amount?
0

Author Comment

well,

this is not gonna work.. I did that already and it didn't work

my logontime field is NUMBER and keep that in mind that tbldebit has more than 1000 of feilds so i have to do Sum on LogonTime and then do Sum of Debit and then DumofCredit - SumofDebit

thanks
0

Author Comment

yes.. and there is a unique field in those two tables called SystemID
0

LVL 2

Assisted Solution

Unless I'm missing something, I think you'll have to create subqueries to get the sums, then link them:

Subquery #1, title "Total_Amount":
SELECT CustCredit.SystemID, Sum(CustCredit.Amount) as SumOfAmount From CustCredit;

Subquery #2, title "Total_Debit":
SELECT CustDebit.SystemID, Sum(logontime) as SumOflogontime From CustCredit;

Then a final query linking the two:
SELECT Total_Amount.SystemID, Total_Amount.SumOfAmount - Total_Debit.SumOflogontime AS Balance
FROM Total_Amount INNER JOIN Total_Credit
ON Total_Amount.SystemID = Total_Debit.SystemID;

0

LVL 8

Accepted Solution

try this:
select ((select sum(amount) from CustCredit) - (select sum(logontime) from CustDebit)) as Balance from CustCredit

Nagki
0

Author Comment

thanks nagki
0

## Featured Post

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.