SQL Query

Posted on 2005-04-08
Last Modified: 2006-11-18

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


Question by:ezzadin
    LVL 65

    Expert Comment

    by:Jim Horn
    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?

    Author Comment


    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


    Author Comment

    yes.. and there is a unique field in those two tables called SystemID
    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;

    LVL 8

    Accepted Solution


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


    Author Comment

    thanks nagki

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now