none
Show part of a report based on a period and add the balance from previous transactions RRS feed

  • Question

  • I want to show the report of transactions between a period of time but I need the balance before the start date. For example, I want the turnover of a customer account for the previous month but he has had a lot of transactions before last month. I want that the report shows the transaction of previous month and the transferred balance from previous transactions. How can I do that ?

    Karim Vaziri Regards,



    • Edited by kvaziri Saturday, September 17, 2016 8:59 PM
    Saturday, September 17, 2016 8:58 PM

Answers

  • Hi Karim,

    Maybe you could use TempVars to calculate and store the total for all previous transactions and then use the TempVars in your query for the report.

    Hope it helps...

    • Proposed as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    • Marked as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    Saturday, September 17, 2016 10:04 PM
  • One way would be to include a row in the query's result table for each customer by means of a UNION ALL operation in the report's query.  The following is an example which adapts one of the queries from the Balances demo in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    By amending one of the queries as follows:

    PARAMETERS [Enter start date:] DATETIME,
    [Enter end date:] DATETIME;
    SELECT T1.[CustomerID], T1.TransactionDate, T1.Credit, T1.Debit,
    SUM(T2.Credit-T2.Debit) AS Balance
    FROM TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2
    ON (T2.[CustomerID]=T1.[CustomerID])
    AND (T2.TransactionDate<=T1.TransactionDate)
    AND (T2.TransactionID<=T1.TransactionID
    OR T2.TransactionDate<>T1.TransactionDate)
    WHERE T1.TransactionDate BETWEEN [Enter start date:]
    AND [Enter end date:]
    GROUP BY T1.[CustomerID], T1.TransactionDate,
    T1.TransactionID, T1.Credit, T1.Debit
    UNION ALL
    SELECT CustomerID,NULL, NULL, NULL, SUM(Credit-Debit)
    FROM TransactionsCD
    WHERE TransactionDate < [Enter start date:]
    GROUP BY CustomerID;

    an additional row per customer will be returned, showing their balance prior to the start date, with Null transaction date, credit and debit columns.  By ordering the report on CustomerID then TransactionDate the additional row per customer will be returned before their transaction data as the Null will sort before the first date.


    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    • Marked as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    Sunday, September 18, 2016 4:46 PM

All replies

  • Hi Karim,

    Maybe you could use TempVars to calculate and store the total for all previous transactions and then use the TempVars in your query for the report.

    Hope it helps...

    • Proposed as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    • Marked as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    Saturday, September 17, 2016 10:04 PM
  • One way would be to include a row in the query's result table for each customer by means of a UNION ALL operation in the report's query.  The following is an example which adapts one of the queries from the Balances demo in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    By amending one of the queries as follows:

    PARAMETERS [Enter start date:] DATETIME,
    [Enter end date:] DATETIME;
    SELECT T1.[CustomerID], T1.TransactionDate, T1.Credit, T1.Debit,
    SUM(T2.Credit-T2.Debit) AS Balance
    FROM TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2
    ON (T2.[CustomerID]=T1.[CustomerID])
    AND (T2.TransactionDate<=T1.TransactionDate)
    AND (T2.TransactionID<=T1.TransactionID
    OR T2.TransactionDate<>T1.TransactionDate)
    WHERE T1.TransactionDate BETWEEN [Enter start date:]
    AND [Enter end date:]
    GROUP BY T1.[CustomerID], T1.TransactionDate,
    T1.TransactionID, T1.Credit, T1.Debit
    UNION ALL
    SELECT CustomerID,NULL, NULL, NULL, SUM(Credit-Debit)
    FROM TransactionsCD
    WHERE TransactionDate < [Enter start date:]
    GROUP BY CustomerID;

    an additional row per customer will be returned, showing their balance prior to the start date, with Null transaction date, credit and debit columns.  By ordering the report on CustomerID then TransactionDate the additional row per customer will be returned before their transaction data as the Null will sort before the first date.


    Ken Sheridan, Stafford, England

    • Proposed as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    • Marked as answer by David_JunFeng Monday, September 26, 2016 2:19 PM
    Sunday, September 18, 2016 4:46 PM
  • Dear DBguy,

    My report is based on an UNION query as follow:

    SELECT [Date], Amount,Description,
    "دریافت" AS TransactionType FROM tblCashReceipt
    UNION SELECT [Date], Amount,Description,
    "پرداخت" AS TransactionType FROM tblPayCash;

    How can I insert the row in my query by TempVar?


    Karim Vaziri Regards,

    Tuesday, September 27, 2016 4:11 PM
  • Hi Karim, Once you have stored the values you need in TempVars, you can add them to your query. For example, SELECT Null AS [Date], TempVars!PreviousTransactions AS Amount, "Previous Balance" AS Description FROM tblCashReceipt UNION SELECT [Date], etc. UNION SELECT... Hope it helps...
    Wednesday, September 28, 2016 8:34 AM