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
     
    
11. Save the form and switch it to Form View
12. Close the form

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 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 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.

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 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:
    
   

   Control Name Caption Back Color Control Sourcce Label   Deposit Performed By #727272   Label   Employee #:     Text Box txtEmployeeNumber       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         Label   Deposit Date:     Text Box txtDepositDate       Label   Deposit Time:     Text Box txtDepositTime       Label   Location Code:     Text Box txtLocationCode     =IIf(IsNull([txtLocationCode]),"",DLookUp("Location","Locations","LocationCode = '" & [txtLocationCode] & "'")) Text Box txtLocation       Label   Performed For Back Color: #727272   Label   Account #:     Text Box txtAccountNumber       Text Box txtCustomerName       Label   Currency Type:     Combo Box cbxCurrencyTypes       Label   Previous Balance:     Text Box txtPreviousBalance       Label   Deposit Amount:     Text Box txtDepositAmount       Label   New Balance:     Text Box txtNewBalance       Label   Notes:     Text Box txtNotes       Button cmdSubmit Submit     Button cmdReset Reset     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):
     
    

    Control Name Caption Label   Withdrawal Performed By Line     Label   Withdrawal Date: Text Box txtWithdrawalDate   Label   Withdrawal Time: 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):
     
    

    Control Name Caption Label   Charge Performed By Line     Label   Charge Date: Text Box txtChargeDate   Label   Charge Time: Text Box txtChargeTime   Label   Charge Performed Against Label   Amount Charged: 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:
     
    

    Control Name Control Source Text Box TransactionDate TransactionDate Text Box TransactionTime TransactionTime Text Box TransactionType TransactionType Text Box DepositAmount DepositAmount Text Box WithdrawalAmount WithdrawalAmount Text Box ChargeAmount ChargeAmount Text Box ChargeReason ChargeReason 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 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 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:
    
    

    Control Name Caption Control Source Text Box AccountNumber Account #: AccountNumber Text Box DateCreated Date Created: DateCreated Text Box CustomerName Customer Name: =DLookUp("LastName","Customers","AccountNumber='" & [AccountNumber] & "'") & ", " & DLookUp("FirstName","Customers","AccountNumber='" & [AccountNumber] & "'") & " " & DLookUp("MiddleName","Customers","AccountNumber='" & [AccountNumber] & "'") Text Box AccountType Account Type: AccountType Subform       Text Box AccountStatus Account Status:   Label   Account's Transactions Summary   Text Box txtDeposits Deposits: =Nz(CDbl([sfAccountsTransactions].[Form]![txtDeposits])) Text Box txtWithdrawals Withdrawals: =Nz(CDbl([sfAccountsTransactions].[Form]![txtWithdrawals])) Text Box txtCharges Charges: =Nz(CDbl([sfAccountsTransactions].[Form]![txtCharges])) Text Box txtBalance Balance: =Nz(DLast("Balance","Transactions","AccountNumber='" & [AccountNumber] & "'")) Subform

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
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:
     
    

    Control Name Caption Control Source Text Box txtAccountNumber Account #:   Text Box txtCustomerName     Text Box txtAccountType Account Type:   Text Box txtDateCreated Date Created:   Label       Text Box txtStartDate Transactions From:   Label       Text Box txtEndDate To:   Button cmdShowTransactions Show Transactions   Subform       Label   Account's Transactions Summary   Text Box txtDeposits Deposits: =Nz(CDbl([sfTransactionsReview].[Form]![txtDeposits])) Text Box txtWithdrawals Withdrawals: =Nz(CDbl([sfTransactionsReview].[Form]![txtWithdrawals])) Text Box txtCharges Charges: =Nz(CDbl([sfTransactionsReview].[Form]![txtCharges])) Text Box txtBalance Balance: =Nz(CDbl([sfTransactionsReview].[Form]![txtBalance])) 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 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] & "'"))
    
    
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
     
    
78. Create the customers accounts
79. Close the form

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;

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;