locked
Min of group value in a report RRS feed

  • Question

  • I have a report named rptBudget(TEST) which groups on Expr3 which is a numeric day of the month. Text61 in this group gives the account balance after the days transactions. In Text82 in the Report header, I would need to get the Lowest balance of Text61 for Month1(it is now set to get the value of Text61 and it returns the first value of Text61) I hope someone can help me with this because I can't figure it out. Thanks, Bob

    Text 59in the Report header is the BeginningAccount Balance 

    Text41 in the Group Expr3 Header is =1.

    Test 35 is Group Expr3 in the Group footer and is = the day's transactions.

    Text 61 is Group Expr3 in the Group footer and is =IIf([Text41]=1,[Text59]+[Text36]).


     
    • Edited by BobAubry Saturday, October 3, 2020 4:25 PM
    Saturday, October 3, 2020 4:15 PM

Answers

  • If you create a query which returns the balances at end of business each day, e.g.

        SELECT T1.TransactionID, T1.TransactionDate, T1.TransactionAmount,
        SUM(T2.TransactionAmount) AS DailyBalance
        FROM Transactions AS T1 INNER JOIN Transactions AS T2
        ON  (T2.TransactionDate<=T1.TransactionDate)
        GROUP BY T1.TransactionID, T1.TransactionDate, T1.TransactionAmount;

    then you can return the minimum balance for the current month in a report by calling the DMin function in an unbound text box:

    =DMin("DailyBalance","qryDailyBalances","Year(TransactionDate) = " & Year([TransactionDate]) & " And Month(TransactionDate) = " & Month([TransactionDate]))

    If the report's RecordSource query is restricted by a parameter or parameters, then the above query would need to be similarly restricted of course.

    If you wish to show the Balance Brought Forward in the header, where the report is restricted to a data range, then this can be computed in the report's query by means of a subquery.  You'll find an example in Balances.zip in my public databases folder at:

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

    In this little demo file the following query returns the balance brought forward in addition to the balances per transaction, with the former then being shown in a text box in the report header:

    PARAMETERS Forms!frmReportDlg!txtDateFrom DATETIME,
    Forms!frmReportDlg!txtDateTo DATETIME;
    SELECT T1.TransactionID, FirstName & " " & LastName AS Customer, T1.TransactionDate, T1.Credit, T1.Debit,
       NZ((SELECT SUM(Credit-Debit)
               FROM TransactionsCD
               WHERE TransactionDate < Forms!frmReportDlg!txtDateFrom),0) AS OpeningBalance,
    SUM(T2.Credit-T2.Debit) AS Balance
    FROM (TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2
    ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
    AND (T2.TransactionDate<=T1.TransactionDate))
    INNER JOIN Customers ON T1.CustomerID = Customers.CustomerID
    WHERE T1.TransactionDate BETWEEN Forms!frmReportDlg!txtDateFrom
    AND Forms!frmReportDlg!txtDateTo
    GROUP BY FirstName & " " & LastName,T1.TransactionDate, T1.TransactionID,
    T1.Credit, T1.Debit;

    In this example the table includes separate credit and debit columns rather than a single column in which debits are entered as negative values.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, October 3, 2020 5:13 PM
    • Marked as answer by BobAubry Monday, October 5, 2020 6:08 PM
    Saturday, October 3, 2020 5:08 PM

All replies

  • If you create a query which returns the balances at end of business each day, e.g.

        SELECT T1.TransactionID, T1.TransactionDate, T1.TransactionAmount,
        SUM(T2.TransactionAmount) AS DailyBalance
        FROM Transactions AS T1 INNER JOIN Transactions AS T2
        ON  (T2.TransactionDate<=T1.TransactionDate)
        GROUP BY T1.TransactionID, T1.TransactionDate, T1.TransactionAmount;

    then you can return the minimum balance for the current month in a report by calling the DMin function in an unbound text box:

    =DMin("DailyBalance","qryDailyBalances","Year(TransactionDate) = " & Year([TransactionDate]) & " And Month(TransactionDate) = " & Month([TransactionDate]))

    If the report's RecordSource query is restricted by a parameter or parameters, then the above query would need to be similarly restricted of course.

    If you wish to show the Balance Brought Forward in the header, where the report is restricted to a data range, then this can be computed in the report's query by means of a subquery.  You'll find an example in Balances.zip in my public databases folder at:

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

    In this little demo file the following query returns the balance brought forward in addition to the balances per transaction, with the former then being shown in a text box in the report header:

    PARAMETERS Forms!frmReportDlg!txtDateFrom DATETIME,
    Forms!frmReportDlg!txtDateTo DATETIME;
    SELECT T1.TransactionID, FirstName & " " & LastName AS Customer, T1.TransactionDate, T1.Credit, T1.Debit,
       NZ((SELECT SUM(Credit-Debit)
               FROM TransactionsCD
               WHERE TransactionDate < Forms!frmReportDlg!txtDateFrom),0) AS OpeningBalance,
    SUM(T2.Credit-T2.Debit) AS Balance
    FROM (TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2
    ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
    AND (T2.TransactionDate<=T1.TransactionDate))
    INNER JOIN Customers ON T1.CustomerID = Customers.CustomerID
    WHERE T1.TransactionDate BETWEEN Forms!frmReportDlg!txtDateFrom
    AND Forms!frmReportDlg!txtDateTo
    GROUP BY FirstName & " " & LastName,T1.TransactionDate, T1.TransactionID,
    T1.Credit, T1.Debit;

    In this example the table includes separate credit and debit columns rather than a single column in which debits are entered as negative values.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Saturday, October 3, 2020 5:13 PM
    • Marked as answer by BobAubry Monday, October 5, 2020 6:08 PM
    Saturday, October 3, 2020 5:08 PM
  • Ken, thanks for your solution.  I have a problem, in that the query on which my report is based gives the error, "Query is too Complex" if I add any more columns. I have two queries each can handle 6 months but no more. I have an unbound report into which I add the calculation queries.  The account balance calculations are all in the calculation reports. Can you think of any way to get the lowest balance in a month using just the reports.  Bob
    Saturday, October 3, 2020 8:47 PM
  • Can you think of any way to get the lowest balance in a month using just the reports.
    Possibly this:

    1.  Ensure that there are two iterations through the report by referencing the Pages property, e.g. in the page footer as in the report in my Balances demo to which I referred you.

    2.  Declare a variable  of Currency data type, and a variable of Integer data type in the report's module's declarations area.

    3.  In the detail section's Format event procedure assign the Month(TransactionDate) value to the integer variable IF the value of the variable is zero.

    4.  Next in the detail section's Format event procedure assign the current Balance control's value to the currency variable IF (it is lower than the currency variable's current value OR the currency variable's current value is zero) AND the value of the integer variable equals the Month(TransactionDate) value.

    5.  In the report header's Format event procedure assign the value of the currency variable to an unbound text box.

    If I've got the logic right the currency variable should be assigned the lowest balance in the first month of the report at the first iteration through the report, and that value should then be assigned to the unbound text box in the report header at the second iteration through the report.  I've assumed the report won't cover more than twelve months of transactions.


    Ken Sheridan, Stafford, England

    Sunday, October 4, 2020 12:32 AM
  • I tested the methodology I described in my last reply in a copy of the report in my Balances demo.  The code for the report's module, with any code irrelevant to this issue removed, is as follows:

    Option Compare Database
    Option Explicit

        Dim curMinBalance As Currency
        Dim intFirstMonth As Integer


    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

        If intFirstMonth = 0 Then
            intFirstMonth = Month(Me.TransactionDate)
        End If
        
        If (curMinBalance = 0 Or Me.Balance < curMinBalance) And _
            intFirstMonth = Month(Me.TransactionDate) Then
                curMinBalance = Me.Balance
        End If
        
    End Sub


    Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

        Me.txtMinBalance = curMinBalance
        
    End Sub


    It duly inserted the minimum balance from the first month correctly into the txtMinBalance control in the report header.  With the data in the demo this happens to be the first row's balance, but if I adjust the data so that the minimum balance in the first month is in the second row, it correctly inserts that value into the text box in the report header.

    If I make further adjustments to the data so that the minimum balance overall is in one of the later months, then it still correctly inserts the minimum balance from the first month.

    PS:  One thing I forgot to mention is that using these event procedures only works if opening the report in print preview, or if the report is sent to a printer.  If the report is opened in report view, then the Format event procedures do not execute, so the code will do nothing.



    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Sunday, October 4, 2020 12:58 PM Postscript added.
    Sunday, October 4, 2020 12:44 PM
  • Ken, The SQL code:

    SELECT T1.Expr3, T1.Expr4, Sum(T2.Expr4) AS Month1

    FROM [EnvelopeBudgetTransactions Query4] AS T1 INNER JOIN [EnvelopeBudgetTransactions Query4] AS T2

    ON T1.Expr3 <= T2.Expr3

    GROUP BY T1.Expr3, T1.Expr4

    ORDER BY T1.Expr3;

    It generates the following output:

    Going to Design View give the Error:

    Access can't represent Join Expression in Design View T1.Expr3 <= T2.Expr3 

    Deleting < in ON clause eliminates the error. I hate having to contact you since you have already put so much time into this but I can't figure out what I am doing wrong. Bob

    Monday, October 5, 2020 3:03 PM
  • As you have found out, Access cannot display non-equi joins in design view. That is normal behaviour.

    However the query can be saved in SQL view and will run successfully. 

    Monday, October 5, 2020 3:10 PM
  • One comment I'd add is that, when designing forms, queries etc in Access, rather than accepting the meaningless names like Text61, Expr1 etc which Access gives to objects, immediately change the name of the object to something meaningful which describes whatever it represents, e.g. in a query you might concatenate a person's first and last names together like this:

       FullName:[FirstName] & " " & [LastName]

    In SQL this is done as:
        
        FirstName & " " & LastName AS FullName

    By naming the computed column FullName it is then obvious what is represented wherever that column might be referenced.

    With controls in a form you'd change the Name property of the control in its properties sheet.  One thing to note is that this should be done before creating any event procedures for the control.  If you change the name after doing so the link between the control and the code will be broken.

    Ken Sheridan, Stafford, England

    Monday, October 5, 2020 4:00 PM
  • Thanks Ken, I got it to work.  I appreciate your time and effort, Bob
    Monday, October 5, 2020 6:09 PM