Series-Based Functions

 

Introduction

A series or collection-based function is one that considers a particular column and performs an operations on all of its cells. For example, if you have a particular column in which users enter a string, you may want to count the number of strings that have been entered in the cells under that column. In the same way, suppose you have a column under whose cells users most enter numbers. Using a series-based function, you can get the total of the values entered in the cells of that column.

The general syntax of series-based functions is:

FunctionName(Series)

The FunctionName is one of those we will see shortly. Each of these functions takes one argument, which is usually the name of the column whose cells you want to consider the operation.

The Series-Based Functions

Sum: To perform the addition on various values of a column, you can use the Sum() function. This function is highly valuable as it helps to perform the sum of values in various transactions.

Count: The Count() function is used to count the number of values entered in the cells of a column.

Average: The Avg() function calculates the sum of values of a series and divides it by the count to get an average.

Minimum: Once a series of values have been entered in cells of a column, to get the lowest value in those cells, you can call the Min() function.

Maximum: As opposed to the Min() function, the Max() function gets the highest value of a series.

Practical Learning: Using Series-Based Functions
  1. Open the College Park Auto Shop2 database and click Forms in the Database window
  2. Double-click the sbfParts subform to open it
  3. After viewing the subform, switch it to Design View
  4. On the ToolBox, click the Text Box control and click somewhere in the Form Footer section
  5. Delete its label and change the following properties for the new text box
    Name: txtPartsTotal
    Control Source: =Sum(Nz([UnitPrice]) * Nz([Quantity]))
    Format: Currenty
    Decimal Places: 2
    Top: 0
  6. To make the Part combo box provide values to the appropriate text boxes, click the text box under Part Name. In the Properties window, click Data and set its Control Source to
    =[PartID].[Column](2) and press Enter
  7. To provide the same functionality for the UnitPrice text box, we will write a line of code.
    On the Form, right-click the left combo box under Part and click Build Event...
  8. In the Choose Builder dialog box, click Code Builder and click OK
  9. In the Object combo box, make sure PartID is selected. In the Procedure combo box, select AfterUpdate
  10. Implement the AfterUpdate event as follows (it is the exact same thing as above except that, this time, the user can change the unit price of an item):
    Private Sub PartID_AfterUpdate(Cancel As Integer)
    Me![UnitPrice] = Me![PartID].Column(3)
End Sub
  11. Close the code window or Microsoft Visual Basic
  12. Back in Microsoft Access, click the text box in the Form Footer section to select it
  13. Using the Format tab of the Properties window, set its Visible property to No
  14. Reduce the height of the text box and reduce the height of the Form Footer section as follows:
     
  15. Save and close the subform
  16. Open the WorkOrders form in Design View
  17. On the Toolbox, click the Text Box and click in the lower-right empty area of the Detail section
  18. Change the Caption of its label to Total Parts
  19. Click its text box and change the following properties
    Name: txtTotalParts
    Control Source: =[Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal]
    Format: Currency
    Decimal Places: 2
  20. Once again, on the Toolbox, click the Text Box and click in the same area where the previous text box was added
  21. Change the Caption of its label to Total Labor
  22. Click its text box and change the following properties
    Name: txtTotalLabor
    Control Source: =Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6])
    Format: Currency
    Decimal Places: 2
  23. Add another Text Box and change the Caption of its label to Tax Amount
  24. Click its text box and change the following properties
    Name: txtTaxAmount
    Control Source: =CLng(([Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal]+
    Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6]))*[TaxRate]*100)/100
    Format: Currency
    Decimal Places: 2
  25. Add one more Text Box to the same section and change the Caption of its label to Order Total
  26. Click its text box and change the following properties
    Name: txtOrderTotal
    Control Source: =([Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal])+
    Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6])+
    CLng(([Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal]+
    Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6]))*[TaxRate]*100)/100
    Format: Currency
    Decimal Places: 2
  27. Save and close the form
  28. To see the result, perform a few orders as follows:
 

 

 

 

 

 

 

 

 

 
Previous Copyright © 2002-2007 FunctionX, Inc. Next