Filtering by a Pattern

Fundamentals of Patterns

Introduction

Most or all of the criteria we have specified so far with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact value in records but you want to specify some type of approximation. To let you do this, the SQL provides the LIKE operator.

If you are visually creating the condition, in the Criteria text box that corresponds to the column on which the condition must apply, type the LIKE expression. Its formula is:

expression LIKE pattern

The expression is usually the name of a column. The pattern can be a value to be found in the expression column.

In most cases, the expression is preceded by the WHERE operator. The operation performed by LIKE deals with string-based columns. The LIKE operator is used to perform a comparison to find out if the value of a field (the expression) exactly or approximately matches a pattern. If the operation is valid, it produces a Boolean value as True or False. You can then use the result as you see fit.

Practical Learning: Introducing Filtering by Pattern

1. On the Ribbon, click File and click Open
2. In the list of files, click WattsALoan from Lesson 40
3. In the Navigation Pane, right-click the TimeSheet form and click Design View
4. In the Controls section of the Ribbon, click the Text Box and click an unoccupied area of the Detail section of the form
5. Delete the accompanying label
6. Click the newly added text box and, in the All tab of the Property Sheet, change its Name to txtEmployeeName
7. Position the new text box on the right side of the Employee # text box

   

8. Close the New Time Sheet form
9. When asked whether you want to save, click Yes
10. In the Navigation Pane, right-click the LoanAllocation form and click Design View
11. In the Controls section of the Ribbon, click the Text Box and click an unoccupied area of the Detail section of the form
12. Delete the accompanying label
13. Click the newly added text box and, in the All tab of the Property Sheet, change its Name to txtEmployeeName
14. Position the text box on the right-click side of the Employee # text box

    

15. Close the form
16. When asked whether you want to save, click Yes
17. In the Navigation Pane, right-click the NewPayment form and click Design View
18. In the Controls section of the Ribbon, click the Text Box and click an unoccupied area of the Detail section of the form
19. Delete the accompanying label
20. Click the newly added text box and, in the All tab of the Property Sheet, change its Name to txtEmployeeName
21. In the Navigation Pane, right-click the NewPayment form and click Design View

    

22. Save and close the form

A Pattern that Exactly Matches a Value

The most basic comparison performed on a string-based field is to find out whether it is equal to a certain string. We already know that this operation can be performed using the = operator. Here is an example:

SELECT PropertyNumber,
       City,
       PropertyType,
       PropertyNumber,
       City,
       PropertyType,
       Condition,
       Bedrooms,
       Bathrooms,
       FinishedBasement,
       Stories,
       MarketValue
FROM Properties
WHERE  PropertyType = "single family";

The LIKE operator can be used to perform the same operation. To do this, use the following formula:

WHERE column-name LIKE value

Here is an example:

SELECT PropertyNumber,
       City,
       PropertyType,
       PropertyNumber,
       City, 
       PropertyType, 
       Condition, 
       Bedrooms,
       Bathrooms,
       FinishedBasement, 
       Stories, 
       MarketValue
FROM Properties
WHERE  PropertyType LIKE "single family";

This and the previous code produce the same result:

To make the operation easier to read, you can put it in parentheses. The above code can be written as follows:

SELECT PropertyNumber, 
       City, 
       PropertyType, 
       PropertyNumber, 
       City, 
       PropertyType, 
       Condition, 
       Bedrooms, 
       Bathrooms, 
       FinishedBasement, 
       Stories, 
       MarketValue
FROM Properties
WHERE  (PropertyType LIKE "single family");

Practical Learning: Filtering Like a Simple Pattern

1. The LoanAllocation form should still be opened in the Design View.
   On the form, click the txtEmployeeNumber text box
2. In the Property Sheet, click the Event tab and double-click On Lost Focus
3. Click its ellipsis button and implement the event as follows:

   Private Sub txtEmployeeNumber_LostFocus()
       Dim dbWattsALoan As Database
       Dim rsEmployees As Recordset
       
       If IsNull(txtEmployeeNumber) Then
           Exit Sub
       End If
       
       Set dbWattsALoan = CurrentDb
       Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT FirstName, LastName " & _
                                                    "FROM Employees " & _
                                                    "WHERE EmployeeNumber LIKE '" & Me.txtEmployeeNumber & "';")
       
       If rsEmployees.RecordCount > 0 Then
           txtEmployeeName = rsEmployees!LastName & ", " & rsEmployees!FirstName
       End If
       
       rsEmployees.Close
       dbWattsALoan.Close
   End Sub

4. In the Object combo box, select cmdClose and implement the event as follows:

   Private Sub cmdClose_Click()
       DoCmd.Close
   End Sub

5. Return to Microsoft Access and close the LoanAllocation form
6. When asked whether you want to save, click Yes
7. In the Navigation Pane, right-click the NewPayment form and click Design View
8. On the form, click the txtEmployeeNumber text box
9. In the Property Sheet, click the Event tab and double-click On Lost Focus
10. Click its ellipsis button and implement the event as follows:

    Private Sub txtEmployeeNumber_LostFocus()
        Dim dbWattsALoan As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbWattsALoan = CurrentDb
        Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT FirstName, LastName " & _
                                                     "FROM Employees " & _
                                                     "WHERE EmployeeNumber LIKE '" & Me.txtEmployeeNumber & "';")
        
        If rsEmployees.RecordCount > 0 Then
            txtEmployeeName = rsEmployees!LastName & ", " & rsEmployees!FirstName
        End If
        
        rsEmployees.Close
        dbWattsALoan.Close
    End Sub

11. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

12. Return to Microsoft Access and close the NewPayment
13. When asked whether you want to save, click Yes
14. In the Navigation Pane, right-click the TimeSheet form and click Design View
15. On the form, click the txtEmployeeNumber text box
16. In the Property Sheet, double-click On Lost Focus and click its ellipsis button
17. Implement the event as follows:

    Private Sub txtEmployeeNumber_LostFocus()
        Dim dbWattsALoan As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbWattsALoan = CurrentDb
        Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT FirstName, LastName " & _
                                                     "FROM Employees " & _
                                                     "WHERE EmployeeNumber LIKE '" & Me.txtEmployeeNumber & "';")
        
        If rsEmployees.RecordCount > 0 Then
            txtEmployeeName = rsEmployees!LastName & ", " & rsEmployees!FirstName
        End If
        
        rsEmployees.Close
        dbWattsALoan.Close
    End Sub

18. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

19. Return to Microsoft Access and close the TimeSheet form
20. When asked whether you want to save, click Yes
21. In the Navigation Pane, right-click the Payroll form and click Design View
22. On the form, click the txtEmployeeNumber text box
23. In the Property Sheet, double-click On Lost Focus and click its ellipsis button
24. Implement the event as follows:

    Private Sub txtEmployeeNumber_LostFocus()
        Dim dbWattsALoan As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbWattsALoan = CurrentDb
        Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT FirstName, LastName, HourlySalary " & _
                                                     "FROM Employees " & _
                                                     "WHERE EmployeeNumber LIKE '" & txtEmployeeNumber & "';")
        
        If rsEmployees.RecordCount > 0 Then
            txtEmployeeName = rsEmployees!LastName & ", " & rsEmployees!FirstName
            txtHourlySalary = rsEmployees("HourlySalary").Value
        End If
        
        rsEmployees.Close
        dbWattsALoan.Close
    End Sub

25. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

26. Return to Microsoft Access and close the NewTimeSheet form
27. When asked whether you want to save, click Yes
28. In the Project window, double-click Form_Watts A Loan
29. In the Object combo box, select cmdNewLoanAllocation and implement the event as follows:

    Private Sub cmdNewLoanAllocation_Click()
        DoCmd.OpenForm "NewLoanAllocation"
    End Sub

30. In the Object combo box, select cmdNewPayment and implement the event as follows:

    Private Sub cmdNewPayment_Click()
        DoCmd.OpenForm "NewPayment"
    End Sub

31. In the Object combo box, select cmdEmployeesRecords and implement the event as follows:

    Private Sub cmdEmployeesRecords_Click()
        DoCmd.OpenForm "Employees"
    End Sub

32. In the Object combo box, select cmdNewTimeSheet and implement the event as follows:

    Private Sub cmdNewTimeSheet_Click()
        DoCmd.OpenForm "NewTimeSheet"
    End Sub

33. In the Object combo box, select cmdNewPayroll and implement the event as follows:

    Private Sub cmdNewPayroll_Click()
        DoCmd.OpenForm "NewPayroll"
    End Sub

34. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

35. Close Microsoft Visual Basic and return to Microsoft Access
36. Close the form
37. When asked whether you want to save, click Yes (the resources that accompany these lessons include a Microsoft Excel spreadsheet named Watts A Loan access its Transactions workbook and create some records: deposits, withdrawals, and charges)

Negating a Pattern

As done with all Boolean operations, there are ways you can negate a LIKE operation. Probably the easiest way is to precede the expression with the NOT operator. Here is an example:

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM States
WHERE Region NOT LIKE 'New England';

If you precede the expression with NOT, it is recommended that you put the section after NOT between parentheses.

As an alternative, in most cases, you can also precede the LIKE keyword with NOT. Here is an example:

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM States
WHERE NOT Region Like 'New England';

You can also negate a negation. Here is an example:

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM States
WHERE NOT States.Region NOT Like 'New England';

If you negate a negation, you can get the result as though none of both negations was used.

Sorting Records

If you are visually creating a query, you can sort the records using the Sort combo box in the bottom side of the window and for the field of your choice.

Patterning by a Wildcard

Matching a Character

The idea of using a LIKE operator is to give an approximation of the value you want to compare to a field. To formulate this approximation, you use some specific symbols referred to as wildcards. They are combined with the LIKE operator.

If you want to match any character, in any combination, for any length, use the * wildcard. If you precede it with a letter, as in n*, the condition would consist of finding any string that starts with that letter, in this case n. If you want to apply the search to multiple occurrences of a character, use as many combinations of the letter delimited by *.

Remember that you can negate a LIKE condition by preceding it with NOT. Here is an example:

Matching a Single Character

The * symbol on a LIKE operation is used to find many characters on the left or the right side of its accompanying character. If you want to find only one character, use the ? wildcard. For example, if you don't know by what character a string starts but know one or more characters after it, you can use ? followed by a sub-string. An example would be LIKE '?ane'. This would produce such strings as Cane, Lane, or Sane.

If you don't know by what character a string starts, know the second character, but don't remember the rest of the characters, you can combine the ? and the * symbols.

Remember that you can negate a LIKE condition by preceding it with NOT. Here is an example:

SELECT States.StateName,
       States.AreaSqrKms,
       States.AdmissionUnionOrder,
       States.Region
FROM States
WHERE StateName Like "?a?s*";

Matching Specific Characters

Instead of one specific character, you may want to match many. To do this, use the square brackets [] as the placeholder. Inside the brackets, enter the characters separated by commas. Outside the brackets, apply the * wildcard on either or both sides.

Matching a Range of Alphabetic Characters

A range of alphabetic characters is a list of letters that consecutively follow each other in the alphabet. To let you match the letters that belong to the same range in the alphabet, include the square-brackets [] in the the LIKE string. Inside the brackets, the first letter, followed by -, and followed by the last letter of the range. On any or both parts of the square-brakets, apply the * wildcard using its rules. Consider the following example:

SELECT AtomicNumber, 
       Symbol,
       ElementName,
       AtomicWeight
FROM Elements
WHERE ElementName Like '[c-g]*';

In this case, the result will include all chemical elements whose names start with C, D, E, F, or G. This would produce:

Matching a Sub-String

Besides a single character, the * wildcard can be applied to a group of letters, called a sub-string. The rules are the same for a single character

SELECT StateName,
       AreaSqrKms,
       AdmissionUnionOrder,
       Region
FROM   States
WHERE  Region Not Like '*north*';

Practical Learning: Matching a Sub-String

1. On the Ribbon, click File and click Open
2. In the list of files, click Whirl Water Shine from Lesson 34
3. In the Navigation Pane, right-click BillPreparation and click Design View
4. On the Form, click the txtAccountNumber text box
5. In the Property Sheet, double-click On Lost Focus and click its ellipsis button
6. Implement the event as follows:

   Private Sub txtAccountNumber_LostFocus()
       Dim rsCustomers As Recordset
       Dim rsWaterMeters As Recordset
       Dim dbWaterCompany As Database
       
       If IsNull(txtAccountNumber) Then
           Exit Sub
       End If
       
       Set dbWaterCompany = CurrentDb
       Set rsCustomers = dbWaterCompany.OpenRecordset("SELECT MeterNumber, FirstName, LastName, Address, City, County, State, ZIPCode " & _
                                                      "FROM Customers " & _
                                                      "WHERE AccountNumber LIKE '*" & txtAccountNumber & "*';")
       
       If rsCustomers.RecordCount > 0 Then
           Set rsWaterMeters = dbWaterCompany.OpenRecordset("SELECT MeterSize, Make, Model " & _
                                                            "FROM WaterMeters " & _
                                                            "WHERE MeterNumber = '" & rsCustomers!MeterNumber & "';")
           txtFirstName = rsCustomers!FirstName
           txtLastName = rsCustomers!LastName
           txtAddress = rsCustomers!Address
           txtCity = rsCustomers!City
           txtCounty = rsCustomers!County
           txtState = rsCustomers!State
           txtZIPCode = rsCustomers!ZIPCode
           
           txtWaterMeter = rsWaterMeters!Make & " " & rsWaterMeters!Model & "; Meter Size: " & rsWaterMeters!MeterSize
       End If
       
   
       rsCustomers.Close
       dbWaterCompany.Close
   End Sub

7. In the Procedure combo box, select LostFocus
8. In the Object combo box, select cmdClose and implement the event as follows:

   Private Sub cmdClose_Click()
       DoCmd.Close
   End Sub

9. Close Microsoft Visual Basic and return to Microsoft Access
10. Close the form
11. When asked whether you want to save, click Yes
12. On the Ribbon, click File and click Open
13. In the list, click Sweet Star Clothers from Lesson 34
14. In the Navigation Pane, right-click Payroll Preparation and click Design View
15. On the form, click the txtEmployeeNumber text box
16. In the Property Sheet, double-click On Lost Focus and click its ellipsis button
17. Implement the event as follows:

    Private Sub txtEmployeeNumber_LostFocus()
        Dim dbSweetStar As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbSweetStar = CurrentDb
        Set rsEmployees = dbSweetStar.OpenRecordset("SELECT FirstName, LastName, Title, " & _
                                                    "       PayCategory, HourlySalary, YearlySalary " & _
                                                    "FROM Employees " & _
                                                    "WHERE EmployeeNumber LIKE '*" & txtEmployeeNumber & "*';", _
                                                    RecordsetTypeEnum.dbOpenForwardOnly, _
                                                    RecordsetOptionEnum.dbAppendOnly, _
                                                    LockTypeEnum.dbPessimistic)
        
        If rsEmployees.RecordCount > 0 Then
            txtEmployeeName = rsEmployees!FirstName & " " & rsEmployees!LastName & " (" & rsEmployees!Title & ")"
            fraPayCategory = rsEmployees!PayCategory
            
            Select Case rsEmployees!PayCategory
                Case 1
                    lblWorkUnits.Caption = "Yearly Salary:"
                    txtWorkUnits = rsEmployees!YearlySalary
                    txtTimeWorked.Visible = False
                    lblNetPay.Caption = "Biweekly Salary:"
                Case 2
                    lblWorkUnits.Caption = "Hourly Salary:"
                    txtWorkUnits = rsEmployees!HourlySalary
                    txtTimeWorked.Visible = True
                    lblNetPay.Caption = "Biweekly Salary:"
                Case 3
                    lblWorkUnits.Caption = "Units Produced:"
                    txtWorkUnits = ""
                    txtTimeWorked.Visible = False
                    lblNetPay.Caption = "Periodic Pay:"
            End Select
            
            txtNetPay = "0.00"
        End If
        
        rsEmployees.Close
        dbSweetStar.Close
    End Sub

18. In the Object combo box, select cmdClose
19. Implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

20. In the Project window, double-click Form_Management
21. In the Object combo box, select cmdNewEmployee
22. Implement the event as follows:

    Private Sub cmdNewEmployee_Click()
        DoCmd.OpenForm "EmployeeNew"
    End Sub

23. In the Object combo box, select cmdNewTimeSheet and implement the event as follows:

    Private Sub cmdNewTimeSheet_Click()
        DoCmd.OpenForm "TimeSheetNew"
    End Sub

24. In the Object combo box, select cmdPayrollSystem and implement the event as follows:

    Private Sub cmdPayrollSystem_Click()
        DoCmd.OpenForm "PayrollSystem"
    End Sub

25. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

26. Close Microsoft Visual Basic and return to Microsoft Access
27. Close the forms
28. When asked whether you want to save, click Yes
29. On the Ribbon, click File and click Open
30. In the list of files, click Kolo Bank from the previous lesson
31. In the Navigation Pane, right-click Account Deposit and click Design View
32. On the form, click the txtLocationCode text box
33. In the Property Sheet, double-click On Lost Focus
34. Implement the event as follows:

    Private Sub txtLocationCode_LostFocus()
        Dim dbKoloBank As Database
        Dim rstLocations As Recordset
        
        Set dbKoloBank = CurrentDb
        Set rstLocations = dbKoloBank.OpenRecordset("SELECT LocationName " & _
                                                    "FROM Locations " & _
                                                    "WHERE LocationCode LIKE '*" & txtLocationCode & "*';", _
                                                    RecordsetTypeEnum.dbOpenDynaset, _
                                                    RecordsetOptionEnum.dbDenyWrite, _
                                                    LockTypeEnum.dbOptimisticBatch)
    
        If rstLocations.RecordCount > 0 Then
            txtLocationName = rstLocations!LocationName & "(" & rstLocations!City & ", " & rstLocations!State & ")"
        End If
        
        Set rstLocations = Nothing
        Set dbKoloBank = Nothing
    End Sub

35. In the Project window, double-click Form_Money Withdrawal
36. In the Object combo box, select txtLocationCode
37. In the Procedure combo box, select LostFocus
38. Implement the event as follows:

    Private Sub txtLocationCode_LostFocus()
        Dim dbKoloBank As Database
        Dim rstLocations As Recordset
        
        Set dbKoloBank = CurrentDb
        Set rstLocations = dbKoloBank.OpenRecordset("SELECT LocationName " & _
                                                    "FROM Locations " & _
                                                    "WHERE LocationCode LIKE '*" & txtLocationCode & "*';", _
                                                    RecordsetTypeEnum.dbOpenDynaset, _
                                                    RecordsetOptionEnum.dbDenyWrite, _
                                                    LockTypeEnum.dbOptimisticBatch)
    
        If rstLocations.RecordCount > 0 Then
            txtLocationName = rstLocations!LocationName & "(" & rstLocations!City & ", " & rstLocations!State & ")"
        End If
        
        Set rstLocations = Nothing
        Set dbKoloBank = Nothing
    End Sub

39. In the Project window, double-click Form_Charge Against Account
40. In the Object combo box, select txtLocationCode
41. In the Procedure combo box, select LostFocus
42. Implement the event as follows:

    Private Sub txtLocationCode_LostFocus()
        Dim dbKoloBank As Database
        Dim rstLocations As Recordset
        
        Set dbKoloBank = CurrentDb
        Set rstLocations = dbKoloBank.OpenRecordset("SELECT LocationName " & _
                                                    "FROM Locations " & _
                                                    "WHERE LocationCode LIKE '*" & txtLocationCode & "*';", _
                                                    RecordsetTypeEnum.dbOpenDynaset, _
                                                    RecordsetOptionEnum.dbDenyWrite, _
                                                    LockTypeEnum.dbOptimisticBatch)
    
        If rstLocations.RecordCount > 0 Then
            txtLocationName = rstLocations!LocationName & "(" & rstLocations!City & ", " & rstLocations!State & ")"
        End If
        
        Set rstLocations = Nothing
        Set dbKoloBank = Nothing
    End Sub

43. Close Microsoft Visual Basic and return to Microsoft Access
44. Save and close all forms

Negating a Wildcard Expression

As opposed to considering the characters that are in a specific range, to specify a character, some characters, a range of characters, or a sub-string that must not be considered, you have two options. As seen already, you can precede the LIKE expression with the NOT operator. As an alternative, use the ! character inside the square brackets but before the character(s).

Matching a Date/Time

If the value you want to find is a date, include it between two # signs.

Practical Learning: Ending the Lession

1. On the Ribbon, click File and click Open
2. In the list of files, click Whirl Water Shine1 used earlier
3. In the Navigation Pane, double-click the WaterMeters table
4. Create the following records:

   Meter #	Make	Model	Meter Size
   293-740	Breston	S-93749	3/4 Inches
   820-418	Vashty Worldwide	DD-3840	3/4 Inches
   627-425	Breston	T-39478	5/8 Inches
   304-861	Vashty Worldwide	DD-3840	3/4 Inches

5. Close the WaterMeters table
6. In the Navigation Pane, double-click the Customers table
7. Create the following records:
   
   

   Account #	Meter #	First Name	Last Name	Address	City	County	State	ZIP Code
   2958-314-5294	627-425	Nicholas	Thorn	2599 Phenicia Rd	Silver Spring	Montgomery	MD	20906
   8046-728-5060	304-861	Augustino	Derbez	7507 Westchester Ave	Washington		DC	20008
   4024-850-0482	820-418	Marianne	Petersen	10572 Maya Blvd	Frederick	Frederick	MD	21701
   7029-371-8594	293-740	Danielle	Dormand	2515 Guthierez Str	Falls Church		VA	22046

8. Close the Customers table
9. In the Navigation Pane, double-click the Bill Preparation form

   

10. Enter some values as follows:
    
    

    Account #	8046-728-5060
    Service From (Date)	1/16/2017
    Service To	4/14/2017
    Meter Reading Start	1412.86
    Reading End	1436.64

11. Click Submit and click OK on the message box
12. In the Navigation Pane, double-click the Bill Preparation form
13. Enter some values as follows:
    
    

    Account #	2958-314-5294
    Service From (Date)	1/18/2017
    Service To	4/17/2017
    Meter Reading Start	5827.59
    Reading End	5883.17

14. Click Submit and click OK on the message box
15. In the Navigation Pane, double-click the Bill Preparation form
16. Enter some values as follows:
    
    

    Account #	8046-728-5060
    Service From (Date)	4/14/2017
    Service To	7/6/2017
    Meter Reading Start	1436.64
    Reading End	1454.86

17. Click Submit and click OK on the message box
18. On the Ribbon, click File and click open
19. In the list of files, click Sweet Star Clothers used earlier
20. In the Navigation Pane, double-click the Employees form and create the following records:
    
    

    Employee #	First Name	Last Name	Title	Pay Category	Hourly Salary	Yearly Salary
    720-526-114	Julia	Siegel	Administrative Assistant	Fixed Salary		34615
    208-294-705	Florence	Adkins	Shift Supervisor	Fixed Salary		62500
    92-7485-8	Frank	Ulm	Associate Designer	Piecework Pay
    244-380-295	Scott	Berman	General Manager	Fixed Salary		78965
    35-0320-3	Eduardo	Sanchez	Associate Sower	Piecework Pay
    4958-074	James	Fence	Technician	Hourly Salary	17.25

21. Close the Employees table
22. In the Navigation Pane, double-click Payroll Preparation

    

23. Click Pay Date and select the date for March 17, 2017
24. In the Employee # text box, type 244-380-295 and press Tab
25. Accept the yearly salary and click the Calculate Salary button

    

26. Click Submit Payroll and click OK on the message box
27. In the Navigation Pane, double-click Payroll Preparation
28. Click Pay Date and select the date for March 17, 2017
29. In the Employee # text box, type 720-526-114 and press Tab
30. Accept the yearly salary and click the Calculate Salary button
31. Click Submit Payroll
32. In the Navigation Pane, double-click PayrollPreparation
33. Click Pay Date and select the date for March 17, 2017
34. In the Employee # text box, type 92-7485-8 and press Tab
35. Type the Units Produced as 48
36. Click the Calculate Salary button
37. Click Submit Payroll
38. In the Navigation Pane, double-click PayrollPreparation
39. Click Pay Date and select the date for March 31, 2017
40. In the Employee # text box, type 4958-074 and press Tab
41. Accept the yearly salary and type the Time Worked as 38.50
42. Click the Calculate Salary button
43. In the Navigation Pane, double-click PayrollPreparation
44. Click Pay Date and select the date for March 31, 2017
45. In the Employee # text box, type 92-7485-8 and press Tab
46. Type the Units Produced as 92
47. Click the Calculate Salary button
48. Click Submit Payroll
49. Close Microsoft Access