Microsoft Access Database Development With VBA

Domain Aggregate Functions

 

Fundamentals of Aggregate Functions

 

Introduction

Microsoft Access is a relational database application with a high level of support for the SQL. Still, besides the operations available in SQL, Microsoft Access has its own implementation of the language. It does this by adding various types of functions. One category of these functions is used to supplement the operations of data analysis. That is, instead of using a WHERE condition, you can use a special function that tremendously simplifies your work. The functions in this case are applied to a series (also called a set) of records and the produce a particular result. Because these functions act on a series of records, they are called domain aggregate functions.

Practical LearningPractical Learning: Introducing Domain Aggregate Functions

  1. Open the KoloBank1 database from Lesson 17
  2. In the Navigation Pane, right-click NewTimeSheet and click Design View

Using a Domain Aggregate Function

A domain aggregate function has the following syntax:

DFunctionName(Expression, Domain, [Criteria])

DFunctionName is the name of the function. The Expression argument can be the name of a column on which the function will act. It can also be a calculation-based expression. The Domain argument can be the name of a table or that of a query that doesn't require an external value.

The Criteria argument is optional. If passed, it can specify the condition used to select a specific record. It acts as a WHERE condition. Therefore, a domain aggregate function resembles the function formula of a SQL statement:

SELECT Expression FROM Domain WHERE Criteria

As mentioned already, Expression is usually the name if a column whose values would be considered. Domain can be the name of a table or query. The Criteria should be formulated as if it was following a WHERE operator.

The Categories of Aggregate Functions

 

Looking Up a Value

Looking up a value in a table or query consists of visiting each one of the values of a certain field and finding out which one of those values matches the target. The aggregate function used to perform this operation is named DLookup. The DLookup() function can be used to find one or more records that respond to a criterion from another table or query.

As mentioned already, you can call this function without passing the third argument which sets the condition. If you omit that argument, all records from the Domain would come in the result. This would be equivalent to a simple SELECT statement that doesn't have a condition.

To apply a condition, pass the third argument. Here is an example:

Private Sub cmdFindByItemNumber_Click()
    ItemName = DLookup("ItemName", "StoreItems", "ItemNumber = " & txtItemNumber)
End Sub

Practical LearningPractical Learning: Looking for a Record in a Domain

  1. On the form, double-click the Employee Number text box
  2. In the Properties window, click Event and double-click On Lost Focus
  3. Click its ellipsis button Ellipsis
  4. Implement the event as follows:
    Private Sub txtEmployeeNumber_LostFocus()
    On Error GoTo txtEmployeeNumber_Error
    
        ' Access the Employees table
        ' Locate an employee who uses the number entered by the user
        If Not IsNull(DLookup("EmployeeNumber", "Employees", _
                     "EmployeeNumber = '" & txtEmployeeNumber & "'")) Then
            ' If you find it, retrieve the corresponding name
            ' (last name and first name)
            ' and display the full name
            txtEmployeeName = DLookup("LastName", "Employees", _
                                      "EmployeeNumber = '" & _
                                      txtEmployeeNumber & "'") & _
                                      ", " & _
                              DLookup("FirstName", "Employees", _
                                      "EmployeeNumber = '" & _
                                      txtEmployeeNumber & "'")
        Else
            ' If you didn't find any employee with that number,
            ' reset the current record
            cmdReset_Click
        End If
    
    txtEmployeeNumber_Exit:
        Exit Sub
        
    txtEmployeeNumber_Error:
        If Err.Number = -2147352567 Then
            Resume txtEmployeeNumber_Exit
        Else
            MsgBox "An error occured when retrieving the employee information" & vbCrLf & _
                   "Please call the program vendor and report the error as follows:" & vbCrLf & _
                   "Error #: " & Err.Number & vbCrLf & _
                   "Reason:  " & Err.Description
            Resume Next
        End If
    End Sub
  5. Close Microsoft Visual Basic and return to Microsoft Access
  6. Close the form
  7. When asked whether you want to save it, click Yes
  8. In the Navigation Pane, right-click TimeSheets and click Design View
  9. On the form, click the employee name text box (the text box on the right side of the Employee # text box)
  10. In the Properties window, click Control Source and type:
    =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'"))
    
    
    
  11. Close the form
  12. When asked whether you want to save it, click Yes
  13. Open the WattsALoan1 database from Lesson 17
  14. In the Navigation pane, right-click the LoansAllocations form and click Design View
  15. On the form, double-click the text box on the right side of the EmployeeNumber text box
  16. In the Properties window, click Control Source and type:
    =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'"))
  17. On the form, click the text box on the right side of the Account # text box
  18. In the Properties window, click Control Source and type:
    =IIf(IsNull([AccountNumber]),"",DLookUp("LastName","Customers","AccountNumber = '" & [AccountNumber] & "'") & ", " & DLookUp("FirstName","Customers","AccountNumber = '" & [AccountNumber] & "'"))
  19. Change the design of the form as follows:
     
    Watts A Loan - Loans Allocations - Form Design
  20. Close the form
  21. When asked whether you want to save, click Yes
  22. In the Navigation Pane, double-click the Customers form
  23. Create accounts for a few customers
  24. Close the form
  25. In the Navigation Pane, right-click the Payments form and click Deisgn View
  26. Click the text box on the right side of the EmployeeNumber text box
  27. Click Control Source and type:
    =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'"))
  28. On the form, click the button on the right side of the LoanNumber text box
  29. In the Properties window, click Control Source and type:
    =IIf(IsNull([LoanNumber]),"",DLookUp("Customer","LoansCustomers","LoanNumber = " & [LoanNumber]))

The First/Last of a Series

If you have a series of values that respond to a common criterion, to get the first value of that series, you can call the DFirst domain aggregate function. In the same way, to get the last value in the series, call the DLast function. If there is only one value in the group, both functions return the same value.

Practical LearningPractical Learning: Finding the Last Value of a Series

  1. In the Controls section of the Ribbon, click the Button Button and click the form.
    If the wizard starts, cancel it
  2. Using the Properties window, change the button's characteristics as follows:
    Name: cmdGetCurrentBalance
    Caption: Get Current Balance
    Visible: No
  3. Complete the design of the form as follows:
     
    Watts A Loan - Payments - Form Design
  4. On the form, click the LoanNumber text box
  5. In the Properties window, click Event and double-click On Lost Focus
  6. Click the ellipsis button Ellipsis to launch Microsoft Visual Basic
  7. Change the file as follows:
    Option Compare Database
    Option Explicit
    
    ' This global variable is used to hold the previous balance of the loans payments
    Private CurrentBalance As Double
    
    Private Sub LoanNumber_LostFocus()
        ' Make sure the user had entered a loan number.
        ' Based on the loan number, get the monthly payment the customer is supposed to pay.
        ' Show that value in the Payment Amount text box
        If Not IsNull([LoanNumber]) Then
            PaymentAmount = CDbl(DLookup("MonthlyPayment", "LoansAllocations", "LoanNumber = " & CLng(LoanNumber)))
        End If
        
        ' Check the table of payments.
        ' If the loan number entered here does not have a balance in the Payments table,
        ' that is, if there is no previous balance of the currrent loan number,
        ' this means that this is probably the first payment of the loan.
        If IsNull(DLookup("Balance", "Payments", "LoanNumber = " & CLng(LoanNumber))) Then
            ' If this is the first payment of the loan, get the Future Value
            ' in the Loans Allocations table for this loan number.
            ' Store (or reserve) that value in the global CurrentBalance variable
            CurrentBalance = CDbl(DLookup("FutureValue", "LoansAllocations", "LoanNumber = " & CLng(LoanNumber)))
        Else
            ' If there is at least one balance amount in the Payments table for this loan number,
            ' get that balance and store it in the global CurrentBalance variable
            CurrentBalance = CDbl(DLast("Balance", "Payments", "LoanNumber = " & CLng(LoanNumber)))
        End If
        
        ' Show the (previous) balance in the Balance text box
        Balance = CurrentBalance
    End Sub
  8. In the Object combo box, select cmdGetCurrentBalance
  9. Implement the Click event as follows:
    Private Sub cmdGetCurrentBalance_Click()
        MsgBox "The previous balance was " & CStr(CurrentBalance)
        
        Balance = CurrentBalance - CDbl(PaymentAmount)
        
        MsgBox "The new balance is " & CStr(Balance)
    End Sub
  10. In the Object combo box, select Form
  11. In the Procedure combo box, select Current
  12. Implement the OnCurrent event as follows:
    Private Sub Form_Current()
        ' To make sure the user doesn't accidentally click the button,
        ' show the Get Current Balance button only if this is a new payment
        If IsNull(ReceiptNumber) Then
            cmdGetCurrentBalance.Visible = True
        Else
            cmdGetCurrentBalance.Visible = False
        End If
    End Sub
  13. Return to Microsoft Access
  14. Close the form
  15. When asked whether you want to save it, click Yes
  16. From the resources that accompany these lessons, open the CeilInn1 database
  17. In the Navigation Pane, right-click the Payments table and click Design View
  18. Change the table as follows to add New Columns:
     
    Field Name Data Type Caption Field Size Format Input Mask
    ReceiptNumber          
    EmployeeNumber          
    PaymentDate          
    AccountNumber Text Processed For 20    
    FirstDateOccupied Date/Time First Day Occupied   Long Date 99/99/0000;0;_
    LastDateOccupied Date/Time Last Day Occupied   Long Date 99/99/0000;0;_
    TotalDays Number Total Days Integer    
    AmountCharged          
    TaxRate          
    Notes Memo        
  19. Save and close the table
  20. In the Navigation Pane, right-click the Payments form and click Design View
  21. Change its design as follows (we list only the three new text boxes you should add to the form): 
     
    Ceil Inn - Payments
    Control Name Caption Control Source
    Label Label   Room Occupied From:  
    Text Box Text Box FirstDateOccupied   FirstDateOccupied
    Label Label   To:  
    Text Box Text Box LastDateOccupied   LastDateOccupied
    Label Label   Total Days/Nights:  
    Text Box Text Box TotalDays   TotalDays
  22. Save the form
  23. On the form, click the AccountNumber text box
  24. In the Properties window, click Event and double-click On Lost Focus
  25. Click its ellipsis button and implement the event as follows:
    Private Sub AccountNumber_LostFocus()
    On Error GoTo AccountNumber_LostFocusError
        
        ' If the customer account number was not entered, don't do anything
        If IsNull([AccountNumber]) Then
            Exit Sub
        Else
            ' Get the first date registered with the customer's account number in the Occupancies table
            FirstDateOccupied = DFirst("DateOccupied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
            ' Get the last date the customer used the room
            LastDateOccupied = DLast("DateOccupied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
        End If
    
        Exit Sub
        
    AccountNumber_LostFocusError:
        MsgBox "There was an error resulting from" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description
    End Sub
  26. Return to Microsoft Access
  27. Switch the Payments form to Form View
  28. Select the value in the Customer Acnt # text box, delete it, and re-type it as 100752
  29. Press Tab and notice the date in the From text box
  30. Return to Microsoft Visual Basic

The Minimum/Maximum of Values

The DFirst (DLast) function is used to get the first (last) of a series values of any types, including string. If the values are number-based or date/time type, to find the lowest value in the series, which may not be the first, you can call the DMin function. In the same way, to get the highest numeric value or the latest occurring date, you can call the DMax function. Once again, if there is only one value in the group, both functions return the same value.

The Number of Records

Probably the most fundamental numeric information you may want to know about a series is the number of records it contains. This information can be provided by an aggregate function named DCount. When calling this function, the first argument should always be the name of a column. Here is an example:

=DCount("Title","Videos")

This code returns the total number of videos that have a value in the Title column. The DCount() function doesn't count the records where the Expression value is NULL. As mentioned in the syntax of the domain aggregate functions, you can use a criterion to select specific records. Here is an example:

=DCount("Title","Videos","Rating = 'R'")

In this case, the function will return the number of videos that are rated R from our table.

Practical LearningPractical Learning: Getting the Number of Records

  1. Change the code as follows:
    Private Sub AccountNumber_LostFocus()
    On Error GoTo AccountNumber_LostFocusError
        
        ' If the customer account number was not entered, don't do anything
        If IsNull([AccountNumber]) Then
            Exit Sub
        Else
            ' Get the first date registered with the customer's account number in the Occupancies table
            FirstDateOccupied = DFirst("DateOccupied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
            ' Get the last date the customer used the room
            LastDateOccupied = DLast("DateOccupied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
            ' Based on the customer account, get the number of days or nights the customer rented the room
            TotalDays = DCount("RateApplied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
        End If
    
        Exit Sub
        
    AccountNumber_LostFocusError:
        MsgBox "There was an error resulting from" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description
    End Sub
  2. Return to Microsoft Access
  3. Switch the Payments form to Form View
  4. Select the value in the Customer Acnt # text box, delete it, and re-type it as 100752
  5. Press Tab
     
    Ceil Inn - Payments
  6. Return to Microsoft Visual Basic

The Sum of Values

The DSum() function can calculate the total of (numeric) values of a column in a table (or query). Here is an example that will return the amount we spent buying the videos stored in a table:

=DSum("PriceBought","Videos")
 
 
 

Practical LearningPractical Learning: Getting the Number of Records

  1. Change the code as follows:
    Private Sub AccountNumber_LostFocus()
    On Error GoTo AccountNumber_LostFocusError
        
        ' If the customer account number was not entered, don't do anything
        If IsNull([AccountNumber]) Then
            Exit Sub
        Else
            ' Get the first date registered with the customer's account number in the Occupancies table
            FirstDateOccupied = DFirst("DateOccupied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
            ' Get the last date the customer used the room
            LastDateOccupied = DLast("DateOccupied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
            ' Based on the customer account, get the number of days or nights the customer rented the room
            TotalDays = DCount("RateApplied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")
                
            ' Get the total of the rates applied from the Occupancies table
            If IsNull(DSum("RateApplied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")) Then
                dRateApplied = 0#
            Else
                dRateApplied = CDbl(DSum("RateApplied", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'"))
            End If
            
            If IsNull(DSum("PhoneCharge", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'")) Then
                dPhoneCharge = 0#
            Else
                dPhoneCharge = CDbl(DSum("PhoneCharge", "Occupancies", "AccountNumber = '" & [AccountNumber] & "'"))
            End If
        End If
    
        Exit Sub
        
    AccountNumber_LostFocusError:
        MsgBox "There was an error resulting from" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description
    End Sub
  2. Close Microsoft Visual Basic and return to Microsoft Access
  3. Close the form
  4. When asked whether you want to save, click Yes
  5. In the Navigation Pane, right-click Payments and click Copy
  6. Right-click any section in the Navigation Pane and click Paste
  7. Type the name as NewPayment
  8. Click OK
  9. In the Navigation Pane, right-click NewPayment and click Design New
  10. Using the Properties window, change the following characteristics of the form:
    Modal: Yes
    Border Style: Dialog
    Navigation Buttons: No
    Data Entry: Yes
     
    Ceil Inn - New Room Payment
  11. Save the form and switch it to Form View
  12. Close the form

The Average of Values

The DAvg() function is used to calculate the average of (numeric) values of a column of a table. Here is an example that produces the average price of the videos of the above table:

=DAvg("PriceBought","Videos")

The Standard Deviation

The StDev() function is used to calculate the standard deviation of the numeric values of the records of a table or query. The DStDevP() function is used to calculate the standard deviation of a population sample of the numeric values of the records of a table or query. If there is no value or the same value in the considered group, this function returns NULL. This means that there should be at least two different values in the group.

The Variance

The DVar() function calculates the statistical variance of the (numeric) values of a table or query. The DVarP() function calculates the variance of a population. If there is no value or the same value in the considered group, these functions return NULL.

Practical LearningPractical Learning: Using Domain Aggregate Functions

  1. Re-open the KoloBank1 database
  2. On the Ribbon, click Create and, in the Forms section, click Form Design
  3. Using the Properties window, set the following characteristics:
    Auto Center: Yes
    Navigation Buttons: No
  4. In the Controls section of the Ribbon, click Combo Box Combo Box and click the form
  5. Using the wizard, select the CurrenciesTypes table and the CurrencyType column before clicking Finish
  6. Save the form as NewDeposit
  7. Design the form as follows:
     
    Ceil Inn - Bank Account New Deposit
    Control Name Caption Back Color Control Sourcce
    Label Label   Deposit Performed By #727272  
    Label Label   Employee #:    
    Text Box Text Box txtEmployeeNumber      
    Text Box Text Box txtEmployeeName     =IIf(IsNull([txtEmployeeNumber]),"",IIf(IsNull(DLookUp("MiddleName","Employees","EmployeeNumber = '" & [txtEmployeeNumber] & "'")),DLookUp("LastName","Employees","EmployeeNumber = '" & [txtEmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [txtEmployeeNumber] & "'"),DLookUp("LastName","Employees","EmployeeNumber = '" & [txtEmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [txtEmployeeNumber] & "'") & " " & DLookUp("MiddleName","Employees","EmployeeNumber = '" & [txtEmployeeNumber] & "'")))
    Line Line        
    Label Label   Deposit Date:    
    Text Box Text Box txtDepositDate      
    Label Label   Deposit Time:    
    Text Box Text Box txtDepositTime      
    Label Label   Location Code:    
    Text Box Text Box txtLocationCode     =IIf(IsNull([txtLocationCode]),"",DLookUp("Location","Locations","LocationCode = '" & [txtLocationCode] & "'"))
    Text Box Text Box txtLocation      
    Label Label   Performed For Back Color: #727272  
    Label Label   Account #:    
    Text Box Text Box txtAccountNumber      
    Text Box Text Box txtCustomerName      
    Label Label   Currency Type:    
    Combo Box Combo Box cbxCurrencyTypes      
    Label Label   Previous Balance:    
    Text Box Text Box txtPreviousBalance      
    Label Label   Deposit Amount:    
    Text Box Text Box txtDepositAmount      
    Label Label   New Balance:    
    Text Box Text Box txtNewBalance      
    Label Label   Notes:    
    Text Box Text Box txtNotes      
    Button Button cmdSubmit Submit    
    Button Button cmdReset Reset    
    Button Button cmdClose Close    
  8. Right-click the Reset button and click Build Event...
  9. Double-click Code Builder and implement the event as follows:
    Private Sub cmdReset_Click()
        txtEmployeeNumber = ""
        txtDepositDate = Date
        txtDepositTime = Time
        txtLocationCode = ""
        txtAccountNumber = ""
        cbxCurrencyTypes = ""
        txtPreviousBalance = ""
        txtDepositAmount = "0.00"
        txtNewBalance = "0.00"
        txtNewBalance.BackColor = vbWhite
        txtNotes = ""
    End Sub
  10. Return to Microsoft Access
  11. Save and close the form
  12. In the Navigation Pane, right-click NewDeposit and click Copy
  13. Right-click anywhere in the Navigation Pane and click Paste
  14. Type NewWithdrawal as the name of the new form and click OK
  15. In the Navigation Pane, right-click NewWithdrawal and click Design View
  16. Change the Design of the form as follows (the changes are in bold characters):
     
    Ceil Inn - Bank Account New Withdrawal
    Control Name Caption
    Label Label   Withdrawal Performed By
    Line Line    
    Label Label   Withdrawal Date:
    Text Box Text Box txtWithdrawalDate  
    Label Label   Withdrawal Time:
    Text Box Text Box txtWithdrawalTime  
  17. Right-click the Reset button and click Build Event...
  18. Change the code as follows:
    Private Sub cmdReset_Click()
        txtEmployeeNumber = ""
        txtWithdrawalDate = Date
        txtWithdrawalTime = Time
        txtLocationCode = ""
        txtAccountNumber = ""
        txtCustomerName = ""
        cbxCurrencyTypes = ""
        txtWithdrawalAmount = "0.00"
        txtPreviousBalance = ""
        txtNewBalance = "0.00"
        txtNewBalance.BackColor = vbWhite
        txtNotes = ""
    End Sub
  19. Save and close the form
  20. In the Navigation Pane, right-click NewWithdrawal and click Copy
  21. Right-click anywhere in the Navigation Pane and click Paste
  22. Type NewCharge as the name of the new form and click OK
  23. In the Navigation Pane, right-click NewCharge and click Design View
  24. Change the Design of the form as follows (the changes are in bold characters):
     
    Ceil Inn - Bank Account New Charge
    Control Name Caption
    Label Label   Charge Performed By
    Line Line    
    Label Label   Charge Date:
    Text Box Text Box txtChargeDate  
    Label Label   Charge Time:
    Text Box Text Box txtChargeTime  
    Label Label   Charge Performed Against
    Label Label   Amount Charged:
    Text Box Text Box txtAmountCharged  
  25. Right-click the Reset button and click Build Event...
  26. Change the code as follows:
    Private Sub cmdReset_Click()
        txtEmployeeNumber = ""
        txtChargeDate = Date
        txtChargeTime = Time()
        txtLocationCode = ""
        txtAccountNumber = ""
        txtCustomerName = ""
        cbxCurrencyTypes = ""
        txtAmountCharged = "0.00"
        txtPreviousBalance = "0.00"
        txtNewBalance = "0.00"
        txtNotes = ""
    End Sub
  27. Return to Microsoft Access
  28. Save and close the form
  29. In the Navigation Pane, right-click sbTransactionsReview and click Copy
  30. Right-click any part of the Navigation Pane and click Paste
  31. Type sfAccountsTransactions as the name of the new sub-form
  32. Click OK
  33. In the Navigation Pane, right-click sfAccountsTransactions and click Design View
  34. In the Properties window, set the Record Source to Transactions
  35. Set the Control Source of each text box in the Detail section to the corresponding label above it:
     
    Kolo Bank - Accounts Transactions Sub-Form
    Control Name Control Source
    Text Box Text Box TransactionDate TransactionDate
    Text Box Text Box TransactionTime TransactionTime
    Text Box Text Box TransactionType TransactionType
    Text Box Text Box DepositAmount DepositAmount
    Text Box Text Box WithdrawalAmount WithdrawalAmount
    Text Box Text Box ChargeAmount ChargeAmount
    Text Box Text Box ChargeReason ChargeReason
    Text Box Text Box Balance Balance
  36. Save and close the sub-form
  37. On the Ribbon, click Create and, to start a new form, in the Forms section, click Form Design
  38. Using the Properties window, set the Record Source to Customers
  39. Save the form as AccountsTransactions
  40. In the Controls section of the Ribbon, click Subform/Subreport Subform - Subreport and click the form
  41. In the first page of the wizard, click Use An Existing Form and click sfAccountsTransactions
  42. Click Next
  43. In the second page of the wizard, make sure Show Transactions For Each Record in Customers Using AccountNumber and click Next
  44. Click Finish
  45. In the Controls section of the Ribbon, click Subform/Subreport Subform - Subreport and click the form
  46. In the first page of the wizard, click Use An Existing Form and click sbfAccountHistory
  47. Click Next
  48. In the second page of the wizard, make sure Show AccountsHistories For Each Record in Customers Using AccountNumber and click Next
  49. Click Finish
  50. Complete the design of the form as follows:

    Kolo Bank: Accounts Transactions
    Control Name Caption Control Source
    Text Box Text Box AccountNumber Account #: AccountNumber
    Text Box Text Box DateCreated Date Created: DateCreated
    Text Box Text Box CustomerName Customer Name: =DLookUp("LastName","Customers","AccountNumber='" & [AccountNumber] & "'") & ", " & DLookUp("FirstName","Customers","AccountNumber='" & [AccountNumber] & "'") & " " & DLookUp("MiddleName","Customers","AccountNumber='" & [AccountNumber] & "'")
    Text Box Text Box AccountType Account Type: AccountType
    Subform Subform - Subreport      
    Text Box Text Box AccountStatus Account Status:  
    Label Label   Account's Transactions Summary  
    Text Box Text Box txtDeposits Deposits: =Nz(CDbl([sfAccountsTransactions].[Form]![txtDeposits]))
    Text Box Text Box txtWithdrawals Withdrawals: =Nz(CDbl([sfAccountsTransactions].[Form]![txtWithdrawals]))
    Text Box Text Box txtCharges Charges: =Nz(CDbl([sfAccountsTransactions].[Form]![txtCharges]))
    Text Box Text Box txtBalance Balance: =Nz(DLast("Balance","Transactions","AccountNumber='" & [AccountNumber] & "'"))
    Subform Subform - Subreport      
  51. Save and close the form
  52. On the Ribboon, click Create
  53. To start a new form, in the Forms section, click Form Design
  54. In the Controls section of the Ribbon, click Subform/subreport Subform - Subreport
  55. Click the Detail section of the form
  56. In the first page of the wizard, click the Use An Existing Form radio button
  57. In the list box, click sfTransactionsReview
  58. Click Next
  59. Click Finish
  60. Save the form as TransactionsReview
  61. Design the form as follows:
     
    Ceil Inn - Payments
    Control Name Caption Control Source
    Text Box Text Box txtAccountNumber Account #:  
    Text Box Text Box txtCustomerName    
    Text Box Text Box txtAccountType Account Type:  
    Text Box Text Box txtDateCreated Date Created:  
    Label Label      
    Text Box Text Box txtStartDate Transactions From:  
    Label Label      
    Text Box Text Box txtEndDate To:  
    Button Button cmdShowTransactions Show Transactions  
    Subform Subform - Subreport      
    Label Label   Account's Transactions Summary  
    Text Box Text Box txtDeposits Deposits: =Nz(CDbl([sfTransactionsReview].[Form]![txtDeposits]))
    Text Box Text Box txtWithdrawals Withdrawals: =Nz(CDbl([sfTransactionsReview].[Form]![txtWithdrawals]))
    Text Box Text Box txtCharges Charges: =Nz(CDbl([sfTransactionsReview].[Form]![txtCharges]))
    Text Box Text Box txtBalance Balance: =Nz(CDbl([sfTransactionsReview].[Form]![txtBalance]))
    Button Button cmdClose Close  
  62. Save and close the form
  63. In the Navigation Pane, right-click the Customers form and click Design View
  64. Add a Text Box Text Box to the right side of the Employee # text box and delete its label
  65. Using the Properties window, change the following characteristics:
    Name: txtEmployeeName
    Control Source: =IIf(IsNull([EmployeeNumber]),"",DLookUp("LastName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'") & ", " & DLookUp("FirstName","Employees","EmployeeNumber = '" & [EmployeeNumber] & "'"))

    Kolo Bank: Customers
  66. Save and close the form
  67. In the Navigation Pane, right-click the Customers form and click Copy
  68. Right-click any section of the Navigation Pane and click Paste
  69. Type NewCustomer and click OK
  70. In the Navigation Pane, right-click NewCustomer and click Design View
  71. Right-click the Transfer Account to Previous Customers button and click Build Event
  72. Delete all code sections in the Code Editor (you will have left only the "Option Compare Database" and the "Option Explicit" lines)
  73. Close Microsoft Visual Basic and return to Microsoft Access
  74. Click the Transfer Account to Previous Customers button and press Delete
  75. Using the Properties window, change the following characteristics:
    Navigation Buttons: No
    Dividing Lines: Yes
    Data Entry: Yes
  76. Save and close the form
  77. In the Navigation Pane, double-click the NewCustomer
     
    Kolo Bank: New Customer
  78. Create the customers accounts
  79. Close the form

Summary Queries and Aggregate Functions

 

Introduction

Besides the various functions built-in the Visual Basic language, the SQL provides an additional collection of functions that you can use to perform various valuable operations in what are referred to as summary queries. A summary query is used to perform a common operation on the values held by a query.

If you have re-occurring pieces of information in various columns of a table, you can create a query that shows them in group.

To support summary queries, you can create a GROUP BY statement. To do this, at the end of the SQL statement, precede the name of the column with the GROUP BY expression. Here is an example:

SELECT Videos.CopyrightYear
FROM Videos
GROUP BY Videos.CopyrightYear;

Grouping Values

To actually perform the necessary operation(s), a query uses a function referred to as aggregate. If you are working visually, after selecting the bas column, use the other columns to set a criterion or the criteria. To programmatically set a condition in a summary query, you precede the condition with the HAVING operator. Here is an example:

SELECT  Videos.CopyrightYear, Count(Videos.CopyrightYear) AS CountOfCopyrightYear
FROM    Videos
GROUP   BY Videos.CopyrightYear
HAVING (Videos.CopyrightYear) Is Not Null;
 
 
   
 

Previous Copyright © 2005-2013, FunctionX, Inc. Next