Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Running Balance by CustomerID

    I have a table named Transactions it has the Following Fields

    TransID Date CustID Type Reference DebitAmt Credit Balance

    1 1//12/06 25 INV 1000 1000.00 0.00 1000.00
    1 1//15/06 25 INV 1080 2000.00 0.00 3000.00
    1 1//19/06 25 INV 1088 500.00 0.00 3500.00
    1 1//21/06 25 PMT 666 0.00 1000.00 2500.00

    I would like to create a running balance such as above, but also need it to be grouped by CustomerID

    The following sort of works,but shows the running balance for all accounts vs for each customer. Any help is appreciated.

    SELECT Transactions.CustomerID, Transactions.TransactionID, Transactions.TransactionID AS TransactionIDc, Transactions.Date, Transactions.TransactionType, Transactions.Reference, Sum(Transactions.DebitAmount) AS SumOfDebitAmount, Sum(Transactions.CreditAmount) AS SumOfCreditAmount, FormatCurrency(DSum("DebitAmount","Transactions","[TransactionID] <=" & [TransactionIDc])) AS RunTotD, FormatCurrency(DSum("CreditAmount","Transactions", "[TransactionID] <=" & [TransactionIDc])) AS RunTotC, FormatCurrency([RunTotD]-[RunTotC]) AS Balance
    FROM Transactions
    GROUP BY Transactions.CustomerID, Transactions.TransactionID, Transactions.TransactionID, Transactions.Date, Transactions.TransactionType, Transactions.Reference;

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    You need to run a second query based on the first query. The first query should be amended

    SELECT Transactions.CustomerID, Transactions.TransactionID, Transactions.TransactionID AS TransactionIDc, Transactions.fldDate, Transactions.TransactionType, Transactions.Reference, Sum(Transactions.DebitAmount) AS SumOfDebitAmount, Sum(Transactions.CreditAmount) AS SumOfCreditAmount
    FROM Transactions
    GROUP BY Transactions.CustomerID, Transactions.TransactionID, Transactions.TransactionID, Transactions.fldDate, Transactions.TransactionType, Transactions.Reference
    HAVING (((Transactions.CustomerID)=[Enter Cust ID]));

    And the second query will be something like this.

    SELECT FormatCurrency(DSum("DebitAmount","Transactions"," [TransactionID] <=" & [TransactionIDc])) AS RunTotD, FormatCurrency(DSum("CreditAmount","Transactions", "[TransactionID] <=" & [TransactionIDc])) AS RunTotC, FormatCurrency([RunTotD]-[RunTotC]) AS Balance
    FROM qryTransactions;

    I would also change the field Date to something else, fldDate as a suggestion. Date is a reserved word and you may get some strange answers.

  3. #3
    Join Date
    Aug 2004
    Posts
    173

    Pretty Good- But Not 100% Working

    Thanks for the reply - for some reason, the 2ndQuery works well if customer ID = 1, but for other CustomerIDs, I am getting the sum of Debits/Credits for Al lin the database??Any Ideas ? Thanks! AB







    Quote Originally Posted by DJN
    You need to run a second query based on the first query. The first query should be amended

    SELECT Transactions.CustomerID, Transactions.TransactionID, Transactions.TransactionID AS TransactionIDc, Transactions.fldDate, Transactions.TransactionType, Transactions.Reference, Sum(Transactions.DebitAmount) AS SumOfDebitAmount, Sum(Transactions.CreditAmount) AS SumOfCreditAmount
    FROM Transactions
    GROUP BY Transactions.CustomerID, Transactions.TransactionID, Transactions.TransactionID, Transactions.fldDate, Transactions.TransactionType, Transactions.Reference
    HAVING (((Transactions.CustomerID)=[Enter Cust ID]));

    And the second query will be something like this.

    SELECT FormatCurrency(DSum("DebitAmount","Transactions"," [TransactionID] <=" & [TransactionIDc])) AS RunTotD, FormatCurrency(DSum("CreditAmount","Transactions", "[TransactionID] <=" & [TransactionIDc])) AS RunTotC, FormatCurrency([RunTotD]-[RunTotC]) AS Balance
    FROM qryTransactions;

    I would also change the field Date to something else, fldDate as a suggestion. Date is a reserved word and you may get some strange answers.

  4. #4
    Join Date
    Aug 2004
    Posts
    173

    Image of Results

    Attached is a Screenshot of the Results - Perfect for CustommerID = 1 but not when CustomerID= 2

    Thanks -AB
    Attached Thumbnails Attached Thumbnails results.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •