ACCESS DB WEEK 5

week_5_pdf3.pdf

Home >Computer Science homework help >ACCESS DB WEEK 5

Week 5  What We'll Be Working on This Week Once your database is loaded with data and operational, you may want to change the appearance of tables or control what's displayed in the tables. You have the ability to move, resize, freeze and hide columns in datasheets, and you can also change the table reference for a subdatasheet, in order to be able to see different related data there. In addition, you can locate and replace data in multiple records, sort the data in tables and forms, and apply filters to data in order to quickly display a subset of records. All of these features will save you lots of time when it comes to manipulating the data in an

Access database file, and they're just waiting to be used. Goals:

 Moving columns in a table page 2

 Resizing columns in a table page 4

 Freezing columns in a table page 5

 Hiding columns in a table page 7

 Controlling subdatasheets page 9

 Sorting Records in a table on a single field page 17

 Sorting Records in a table by multiple fields page 18

 Locating and Replacing records in a table page 20

 Finding and replacing data in multiple records page 25

 Filtering by selection criteria page 28

 Filtering by selection with multiple criteria page 31

 Using filter exclusion selection page 32

 Using filter by form page 34

 Applying multiple criteria in the same field page 36

 Applying multiple criteria in different fields page 37

 Combining AND and OR conditions in filters page 39

 Using wild cards in filters page 41

 Using filter for input page 42

Note: Despite this brief introduction to the week's material, the lessons for this week are rather long, so plan to spend a bit more time on them than you have in past weeks.

Let's get to it!

Goals for this section:

 Moving columns in a table

 Resizing columns in a table

 Freezing columns in a table

 Hiding columns in a table

 Controlling subdatasheets

Changing the Appearance of Tables

As we have seen, when you design a database the things you primarily need to be concerned with are the fields, the distribution of data, and the relationships between tables. If those aspects are in place and correctly defined, everything else should fall into place, as well. As a result, it is critical that you plan the database carefully before any tables are populated with data. (But you knew this already, right?) On the other hand, display aspects such as the order and width of columns, the sort order of records, and the contents of subdatasheets are purely superficial changes that may be changed at any time without compromising the integrity of the data.

Moving Columns in a Table

Access displays columns in the datasheet in the order in which they were defined in Design View. If you would like the columns to be displayed in a different order in Datasheet View, you have two options:

1. Display the table in Design View, and move the fields to the desired position. When you save the design changes and switch to Datasheet View, the order of the fields will be changed.

2. Display the table in Datasheet View, position the mouse pointer on the column heading of the field to be moved, and drag the column to the desired position.

It's important to note here that this doesn't change the order of the fields in Design View, even when you save the changes to the table. However, every time you display the table in Datasheet View, the changed column order will still be in effect.

Note: If you want to move multiple contiguous columns at the same time, you can use the Shift-click method to select the columns, and then drag them to the desired position in the datasheet.

Hands-On Activity: Moving columns Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Open the tblBidData table in Datasheet View.

2. Click in the Award Date column heading, to select the column.

3. Position the mouse pointer in the column heading, and drag to the left until you see a solid vertical black line between the Definition and the Address fields (your Captions may differ).

Once the black line appears where you want the field to be positioned, release the mouse to move the column.

4. Select the Work Order # field, by clicking the column heading.

5. Hold down the Shift key and click the column heading for the Customer ID field. Both columns should be selected.

6. Drag both of the selected fields (together) between the Bid # and the Bid Date fields.

7. Re-save the table.

8. Switch to Design View and observe the field order: The strAwardDate, strWorkOrder#, and strCustomerID fields haven't moved. Dragging columns in Datasheet View does not change the field order in Design View.

Clearly, this is a good way to make a quick fix in a table. But in the long run, it could be confusing to others who might not understand why the fields are in different orders in the two views.

Resizing Columns in a Table

By default, Access assigns the same width to all of the columns in a datasheet. Column widths may be changed at any time without compromising the integrity of the data in the table. To resize columns, you have several options that are identical to the techniques we use for resizing columns in an Excel worksheet: 1. To resize a single column, place the cursor on the Column Sizing line to the right of the field

name in the column heading, so that the mouse pointer changes to a horizontal double- edged arrow with a vertical line running through it. Drag to the left or right to narrow or widen the column.

2. To resize multiple contiguous columns simultaneously, use the Shift-Click method to select

the columns and then drag the Column Sizing line to the right of any one of the selected columns to the left or right.

3. To have Access automatically adjust the width of one or more columns to accommodate the longest entry in the column(s), select the columns and then double-click the Column Sizing line to the right of any one of the selected column headings.

Hands-On Activity: Resizing Columns Before beginning: Your Home Tech Repair database file is open, with the tblBidData table displayed in Design View.

1. Switch to Datasheet View. Note: Your field Captions may differ from those in the exercises.

2. Place the mouse pointer on the Column Sizing line to the right of the State column heading. The mouse pointer should change to a horizontal double-edged arrow with a vertical line going through it.

3. Hold down the mouse button and drag to the left, to narrow the State column. As you drag, a thin vertical black line indicates what the width of the column will be when you release the mouse.

4. Select both the Principal and Phone columns.

5. Drag the Column Sizing line to the right of either of the two selected columns, to widen the columns a little bit. Both columns are simultaneously widened.

6. Observe the Undo button on the toolbar: Column width changes cannot be undone.

7. Place the mouse pointer on the Column Sizing line to the right of the Definition column heading and double-click the left mouse button. As in Excel, this procedure tells Access to look for the longest entry in the column and to adjust the width of the columns to accommodate that entry.

8. Close the tblBidData table. Because you made changes to the layout of the table, you're asked if you want to save the changes.

9. Click Yes in response to the question regarding saving the changes.

Freezing Columns in a Table

Freezing a column is an excellent technique to use for viewing large tables, because it will enable you to scroll to the right in a table and continue to view the data in the frozen column(s) as you do. It's analogous to the Freeze Panes command in Excel. You have the option of freezing a single column or multiple columns. To freeze one or more columns in a table, select the column(s) to be frozen and then choose Format / Freeze Columns. To unfreeze columns, choose Format / Unfreeze All Columns.

Hands-On Activity: Freezing Columns in a Table Before beginning: The Home Tech Repair database is open and there are no objects currently open.

1. Display the tblCustomer table in Datasheet View.

2. Adjust the following fields by double-clicking the Column Sizing line to the right of the column heading:

Company Name First Name Last Name Address Notes

3. Widen the City and Phone fields to add more space to the fields.

4. Notice that now that the columns are wider, when you're viewing the Customer ID and Company Name fields, you're not able to see the Notes for that customer on the screen.

5. Select the Customer ID and Company Name columns.

6. Choose Home / Records / More / Freeze Fields

When you remove the selection from these two columns, a thicker (although not by much) solid grey line appears to the right of the Company Name column, to reflect the Freeze.

7. Scroll to the right, to view the Notes column. As you do, the contents of the Customer ID and the Company Name columns remain visible.

8. Choose Home / Records / More / Unfreeze All Fields, to remove the freeze.

9. Close tblCustomer, clicking Yes to save the changes to the layout

Hiding Columns in a Table

If you have one or more columns of data in a table that you don't need to see on a regular basis, you can hide those columns. Hiding columns in a table does not affect the stored data in the table, so any forms, queries or reports that are based on the table won't be affected. This is a good idea for an OLE object field in a table, since pictures inserted in the field are only going to display in a form or report, never in the table itself, as we have seen. Why not hide the column, so it isn't taking up space unnecessarily. The contents of the field will still be available to forms and reports that are based on the table. Another good use of hiding columns involves Date/Time fields in which you've used the =Now() function as the Default Value, so that the current date is automatically entered in the field when a new record is added to the table. Your purpose in defining such a field would be, presumably, to be able to use a Query to locate all the records created within a range of dates. But you don't need to be seeing this field in order for the Default Value to be operating. To hide one or more columns in a table, select the columns to be hidden (or simply place the cursor in a single column if you only want to hide that one column) and choose Home / Records / More / Hide Fields. To return the hidden columns to the display, choose Home / Records / More

/ Unhide Fields. When you do, the Unhide Columns dialog box appears with a list of all of the fields in the datasheet. A check mark next to the field name means the field is currently displayed.

Make the appropriate changes to the selections, and click Close.

Hands-On Activity: Hiding Columns in a Table Before beginning: The Home Tech Repair database is open and there are no objects currently open.

1. Display the tblEmployeeHRData table in Datasheet View.

2. Select the Record Entry Date column. This field doesn’t need to be seen here, and the system will auto-enter the current date for us.

3. Choose Home / Records / More / Hide Fields.

4. On the Create / Forms select Form icon to create a form based on this table. For now we will take the default format for the form. The format for an ‘auto form’ can be customized and we will discuss this in week 8.

5. Observe the absence of the Record Entry Date field on the form. Although the field is hidden in the Datasheet View of the table on which the form is based, it is still a functioning field in the database.

6. Save the form as frmHRData.

7. Close the form.

8. With the tblEmployeeHRData table displayed in Datasheet View, choose Home / Records / More / Unhide Fields

9. Notice that there is no check mark next to the Record Entry Date field name on the Unhide Columns dialog box.

10. Click the check box to the left of the Record Entry Date.

11. Click Close, to close the dialog box.

12. Close the table, saying Yes in answer to the question regarding saving changes to the layout.

Controlling Subdatasheets

As we have seen, when you create relationships between tables, Access automatically adds a subdatasheet to the parent table of the relationship, so that you can see the records from its child table at the same time.

In a One-to-Many relationship, the plus signs for accessing the subdatasheet always appear in the table on the One side of the relationship (that is, the parent table), while the contents of the subdatasheet itself are from the table on the Many side of the relationship (the child table).

Access records this subdatasheet setting as [Auto] on the properties window of the parent table.

If you have multiple relationships for a table and want to change the contents of the subdatasheet for a parent table, you can do so. The procedure is as follows:

1. Display the parent table of the relationship in Design View.

[Remember, the parent table is the one that has the plus or minus signs next to each record.]

2. On the Table Tools / Design / Show/Hide, click the Property Sheet icon.

3. Place the cursor in the Subdatasheet Name line.

4. Click the  to display the list of available table object names.

5. Select the name of the table whose contents you want reflected in the subdatasheet.

6. Close the Table Properties window.

Another way to work with subdatasheets:

1. Display the parent table of the relationship in Datasheet View.

2. On the Home / Records / More, click the Subdatasheet icon.

3. A list of available tables is displayed for your selection:

Look at the relationships in our database:

There will be plus signs next to the records in tblEmployeePersonal, leading to the Subdatasheet that contains the tblEmployeeHRData records. The red arrow that I have drawn here leading from tblEmployeePersonal to tblEmployeeHRData illustrates the direction of the relationship.

In tblEmployeeHRData, there will be plus signs next to the records leading to tblWorkOrders, because that's the "Many" side of the One-to-Many relationship with tblEmployeeHRData. However, in tblWorkOrders there won't be any plus signs next to the records, because, as the child table for the relationships with tblCustomer and tblBidData, this table will supply the subdatasheet records for those two tables.

Some Notes on the Properties Window

Before moving on, we need to stop for a moment and talk about how the Properties window works in Access. When you opened this window, you may have noticed that there were no OK and Cancel buttons, only a Close (X) in the upper right-hand corner of the window. When you make any selections on the Properties window, those settings are immediately applied to the currently active object. Therefore, if you make a mistake, you won't be able to click the Cancel button to abort the change, as you're accustomed to doing in other applications. This is a window, not a dialog box.

I'm mentioning this to you because sometimes we have the tendency to try things out, making changes here and there without really paying attention to what we're doing. If you do this in

Access (that is, you start making random changes to properties without noting which options you're changing and what the setting was before you made the change), you could get yourself into deep trouble. Always pay close attention to what you're doing, always take note of current settings (even taking the time to write them down) before you change them, and always remain conscious when working in Access.

Hands-On Activity: Controlling Subdatasheets Before beginning: The Home Tech Repair database and there are no objects currently open.

1. Display the tblEmployeePersonal table in Datasheet View.

2. Click the plus sign next to any one of the records in the table, and observe what is displayed

in the subdatasheet. Because tblEmployeePersonal is joined to tblEmployeeHRData in a One-to-One relationship that originates in tblEmployeeData, the contents of the tblEmployeeHRData table are displayed in the subdatasheet.

3. Click the plus sign next to any record in the tblEmployeeHRData table. Notice that the contents of tblWorkOrders are reflected in the subdatasheet, because tblWorkOrders is the child table in the One-to-Many relationship with tblEmployeeHRData.

4. Notice that there is no plus sign next to the active record in tblWorkOrders (in the

subdatasheet). This is because the table is on the "Many" side of the relationships with tblCustomer and tblBidData.

5. Open the tblCustomer table in Datasheet View and click the plus sign next to any one of the records.

6. Notice that the subdatasheet displays the records in the tblWorkOrders table, because tblWorkOrders is the child table in the One-to-Many relationship with tblCustomer. The same thing holds true of the relationship between tblBidData and tblWorkOrders.

Suppose you want to be able to see the contents of tblBidData in the subdatasheet of tblWorkOrders. In order to do this, you will need to change the relationship between those two tables so that tblBidData is the child of the One-to-Many relationship. To create that type of join between the two tables, we will need to change the relationship so that the two tables are joined on the strWorkOrder# field. This would make tblWorkOrders the child table in the relationship, and tblBidData the parent.

Once you have changed the relationship, you will be able to change the subdatasheet for tblWorkOrders.

7. Close all tables.

8. Open the Relationships window. Notice that you have tblEmployeeHRData_1. This is because tblEmployeeHRData has more than 1 relationship (tblEmployeePersonal and tblWorkOrders). Access seems to think that this is easier for the user to visualize. You can hide the table by right clicking on any field and select Hide. This will not change the existing relationships.

9. Click on the join line between tblBidData and tblWorkOrders to select it.

10. Select the Delete option to delete the join, and click Yes to confirm the deletion of the

relationship.

11. Create a new join from the strWorkOrderNumber field of tblWorkOrders to the strWorkOrder# field of tblBidData.

12. Make sure the Relationship type is One-to-Many. Do not enforce Referential Integrity.

13. Click Create to complete the relationship change.

14. Close the Relationships window.

15. Click Yes to save the changes.

16. Open the tblEmployeePersonal table in Datasheet View, and click the plus sign next to any one of the records. You should still be seeing the employee HR information in the subdatasheet.

17. Click the plus sign next to any one of the records in the Employee HR subdatasheet, to see the work order information for that employee.

18. Click the plus sign next to any one of the records in the Work Order subdatasheet, to see the bid information.

19. Close the tblEmployeePersonal table.

Goals for this section:

 Sorting on a single field

 Sorting a table by multiple fields

Sorting Records in a Table on a Single Field

When you open a table in Datasheet view, Access displays the rows sorted in sequence by the primary key you defined for the table (ascending order). If you didn't define a primary key, you'll see the rows in the sequence in which you entered them in the table. If you want the records arranged differently in a table, you have the option of sorting the table by one or more fields in the table, in Ascending or Descending order, and then saving the sorted arrangement. When you subsequently create forms, queries, or reports based on the table, the same Sort order is followed until those objects have a different Sort applied to them. It isn't possible to sort a table on Memo, Hyperlink, or OLE Object fields, for obvious reasons.

Sorting Records in a Table on a Single Field To sort a table on a single field, you have several options:

1. Place the cursor in any record of the field to be used as the basis of the sort, and click the

Ascending or Descending icon on the Home / Sort & Filter group.

2. Another way is to click the in the column’s heading and select the desired sort.

3. Another way is to right-click on any record in the field to be used as the basis of the Sort, and

choose either Sort Ascending or Sort Descending.

Hands-On Activity: Sorting on a Single Field Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Open the tblEmployeesHRData table in Datasheet View.

2. Put the cursor in the Emp Last Name field of any record.

3. On the Home / Sort & Filter group, click the Sort Ascending icon .

4. Notice the results: The 2 Wilsons (Tony and Andy) aren't correctly sorted by the Emp First Name field. If the active cell is in one column and you use the Sort Ascending or Sort Descending buttons on the toolbar, the table will be sorted by only that one column.

Sorting Records in a Table by Multiple Fields

In order to sort a table by multiple fields, the fields must be adjacent to one another and they must be arranged from left to right according to the desired Sort order. The reason is that Access sorts fields from left to right. If the columns by which you want to sort the table aren't arranged appropriately, you need to move them.

Note: This applies to moving columns in Datasheet View. As we discussed in the last section, it isn't necessary to change the field order in Design View in order to change the order of columns in the Datasheet.

Once the columns have been arranged appropriately, select the columns that you want to sort by and then use one of the Sort options mentioned above.

Hands-On Activity: Sorting a table by multiple fields Before beginning: Your Home Tech Repair database file is open, with the tblEmployeeHRData table displayed in Datasheet View. The records have been sorted by the Emp Last Name field.

1. Drag the Emp Last Name column to the left of the Emp First Name column.

2. Select the Emp Last Name and Emp First Name columns.

3. Click the Sort Ascending icon in the Home/Sort & Filter group.

4. Observe the order of the Wilsons, in rows 23 and 24 of the table: Now Andy precedes Tony.

5. Re-save the table, to preserve the Sort order.

6. Close tblEmployeeHRData.

7. Re-open tblEmployeeHRData. The Sort order has been preserved.

8. Close tblEmployeeHRData.

Goals for this section:  Locating records in a table

 Finding and replacing data in multiple records

Locating and Replacing Records in a Table When you only have a limited amount of data in your tables, it will be fairly easy to scroll to locate records whose contents needs to be changed or which need to be deleted. However, once your tables reach hundreds or even thousands of records, this won't be so easy. Access is prepared to locate records for you, so that you don't need to waste time searching. The program will enable you to locate records based on an exact match of values, and it also recognizes the use of wildcards so that you may find an inexact match of values.

Once a record has been located, its contents may be edited or the entire record may be deleted.

Finding Records in a Table

The procedure for locating records in a table is as follows:

1. Put the cursor in the field whose contents are to be searched for a match.

2. In the Home / Find group, click the Find icon.

The Find and Replace dialog box is displayed.

3. In the Find What: text box, enter the string of data to be found.

4. From the Look In: drop-down list, make a selection based on where you want Access to search:

 Select the name of the field the cursor is in, if you want Access to search only in that field.

 Select the name of the currently open table, to tell Access to search for a match in all fields of the table.

5. From the Match: drop-down list, make a selection based on how the search string is to be matched:

 Any Part of Field

Will search for the specified string in any part of the field (for example, window would be located in a field that contains bay window or window replacements.)

 Whole Field (the default) Will find the specified string only if it matches the entire contents of the field. (For example, window would not match bay window.)

 Start of Field Will find the specified string only if it occurs at the beginning of the field, not in the middle or at the end. (For example, window would match window box, but not bay window.)

6. From the Search: drop-down list, select the direction of the Search (Up, Down, or All).

7. Click the Find Next button, to locate a matching record in the table.

8. Continue clicking the Find Next button to locate other matching records. When Access reaches the end and doesn't find any more records, it displays a Message Box.

Hands-On Activity: Locating records

Before beginning: Your Home Tech Repair database is open and there are no objects open.

1. Open the tblBidData table in Datasheet View.

2. Put the cursor in any record of the Definition field.

3. In the Home / Find group, click the Find icon .

The Find and Replace dialog box is displayed.

4. In the Find What: text box, enter window. We want to locate all records that contain this

word.

5. Observe the Look In: drop-down menu: Current field (the name of the currently active field) will tell Access to search for the word window only in this field.

6. Click the  to display the contents of the Look In: drop-down list. If you wanted Access to search for the word window in all fields of the table, you would need to select Current document.

7. Leave Current field selected in the Look In box.

8. Click the  on the Match: drop-down list, and observe the three options:

 Any Part of Field Will search for the specified string in any part of the field (for example, window would be located in a field that contains bay window or window replacements.)

 Whole Field (the default) Will find the specified string only if it matches the entire contents of the field. (For example, window would not match bay window.)

 Start of Field Will find the specified string only if it occurs at the beginning of the field, not in the middle or at the end. (For example, window would match window box, but not bay window.)

9. In the Match drop-down list, select Any Part of Field.

10. Leave the Search: option set to All, so that Access will search all of the records.

11. Click the Find Next button. Record 9, containing Garden Window as the definition is found.

12. Click the Find Next button. Record 16, which also contains Garden Window is found.

13. Click the Find Next button. Record 22, containing Bay Window is located.

14. Click the Find Next button. Access cycles back to record 9. It does not display a Message Box (at least not right away) telling you it finished searching the records and didn't find any more occurrences of the search item.

15. Continue clicking on the Find Next button, until the Message Box is displayed, telling you it

has reached the end of the occurrences of the matching search string.

16. Click OK to close the Message Box.

17. Click Cancel, to close the Find and Replace dialog box.

Replacing Data in Tables If you need to change the contents of a field across multiple records, you should allow Access to perform a Find and Replace, rather than spending countless hours doing it yourself manually.

Remember: Access is loaded with timesaving features. It's up to you to remember to use them!

The procedure for finding and replacing data in multiple records is as follows: 1. In the table, place the cursor in any record of the field that contains the data to be changed.

2. Click the Find icon on the Home / Find group. 3. On the Find and Replace dialog box, activate the Replace tab. 4. In the Find What: text box, enter the string that you want to find. 5. In the Replace With: text box, enter the string that you want to appear in the fields of the

table. 6. From the Look In: drop-down list, choose the field name (to tell Access to look for a match

only in the current field) or the table name (to tell Access to search in all fields). 7. From the Match: drop-down list, choose the portion of the field that should be matched (Any

Part of Field, Whole Field, or Start of Field). 8. From the Search: drop-down list, choose the direction of the search. 9. Click Find Next, to locate the first occurrence of the Search string.

Note: It's always a good idea to check to make sure you've given Access the correct information before you go ahead and make all of the replacements. Remember – Always lay out your safety nets.

10. Click either Replace, to replace just one record at a time, or Replace All, to replace all

records.

If you click Replace All, Access displays an Alert Box warning you that the procedure cannot be undone and asking you to confirm.

Hands-On Activity: Finding and Replacing Data in Multiple Records

Before beginning: Your Home Tech Repair database file is open, with the tblBidData table displayed in Datasheet View.

Our situation is this: We have multiple records in this table that contain Lost in the Award Date field. We decide that Lost is potentially misleading, and want to change it to Not Awarded. It would take too long to change each one individually, so we'll use the Find and Replace feature to make all of the changes at once.

1. Put the cursor in the Award Date field of any record.

2. Click the Find icon in the Home / Find group.

3. On the Find and Replace dialog box, activate the Replace tab.

Notice that window, the last search string, is still reflected in the Find What: text box.

Note: If you have exited Access between these two activities, you won't see the reference to window in the Find What: text box. The selections made on all dialog boxes are cleared when you exit the application.

4. In the Find What: text box, enter Lost. As long as you don't select the Match Case option at

the bottom of the Find and Replace dialog box, you can use whichever case you prefer for typing the search item.

5. Press the Tab key to move the cursor to the next text box.

6. In the Replace With: text box, enter Not Awarded. Here, you need to type the replacement string exactly as you want it to appear in the field of the table. Whichever case you use will be the way in which the string will appear.

7. Leave Current field indicated in the Look In: text box. We want Access to search for the word Lost only in this field.

Tip: If you get to this point and realize you forgot to activate the field in which you want Access to look for the search string, you can always simply change the contents of the Look In box to reflect the Current document. In that way, Access will search the entire table, rather than just the current field. However, if there are a lot of fields in the table, this can take a very long time. I suggest you take a couple of seconds to decide which field Access should look in and activate that field before you open the Find and Replace dialog box. This will streamline the data replacement process substantially.

8. Leave the Match: option set to Any Part of Field.

Note: The Whole Field option would also work here, because Lost is the only item in the field.

9. Leave All selected as the Search option, so that Access will look in all of the records of the

table.

10. Click the Find Next button. The first entry is found in record 2 (depending on which record you first select). Now that you know that you have given Access the correct information, it is safe to do the replacing of all records.

11. Click the Replace All button. Access makes the changes to all six of the records that contained Lost in the Award Date field, and displays an Alert Box that warns you that this procedure cannot be undone.

12. Click Yes, to confirm the procedure. 6 records are changed, as indicated by the message in the lower left corner of the window.

13. Close the Find and Replace dialog box. The finished table should now look as follows:

14. Close tblBidData.

15. Click Yes if Access asks you if you want to save the changes.

Goals for this section:

 Filtering by selection criteria

 Filtering by selection with multiple criteria

 Using filter exclusion selection

 Using filter by form

 Applying multiple criteria in the same field

 Applying multiple criteria in different fields

 Combining AND and OR conditions in filters

 Using wildcards in filters

 Using filter for input

Filtering Data in Tables If you want to see only a limited number of records in a datasheet, subdatasheet, or form, you can apply filters to the data so that all of the records that don't match your conditions are hidden. Filtering doesn't delete the records that don't match the criteria, but rather simply removes them from view. By changing the criteria or removing the filter, all of the records will be available. If you print the datasheet or form to which a filter has been applied, only the filtered records will be printed. Access provides several ways to apply filters to data:

 Filter By Selection

 Filter Excluding Selection

 Filter By Form

 Filter For Input We're going to look at each of these, to see how they work. Then when it comes to filtering your own database, you'll be able to make an informed decision as to which method will work best for you.

Filter by Selection This is the most commonly used means of applying filters to data, because it is the easiest. All you have to do is select a sample value in one of the records, and then click the Selection icon in the Home / Sort & Filter group.

When you apply a filter to a table and close the table, Access asks you if you want to save the changes. If you click Yes, Access will remove the filter while at the same time remembering the criteria for the filter. As a result, when you subsequently reopen the table and click the Filter icon, the last-applied filter will be reapplied to the table.

Hands-On Activity: Filtering by Selection

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

Our objective: To see a list of only the people with Electrical as the Specialty, in the tblEmployeeHRData table.

1. Display the tblEmployeeHRData table in Datasheet View. All 24 records should be

displayed. 2. In record 3, select Electrical in the Specialty column.

This will be the Selection containing the criterion that Access will match for the filter.

3. On the Home / Sort & Filter group, click the Selection icon, and select Equals “Electrical”.

5 records of the original 24 should be displayed.

4. Observe the Status Bar Area, at the bottom of the screen:

 In the Navigation Area, you see 1 of 5 and to the right of that you see to indicate that the 5 displayed records have had a filter applied to them.

 In the Status Bar, Filtered also indicates the fact that the displayed records have been filtered.

5. Click the Print Preview option from File / Print / Print Preview. Notice that only the filtered records will print.

6. Close the Print Preview window. 7. Close the table. Access asks if you want to save the changes to the design.

Note: Access is asking you if you want it to remember the filter, so that you can easily reapply it in the future.

8. Click Yes. The table is closed.

9. Reopen the tblEmployeeHRData table. All 24 of the records are displayed.

In the event you're confused by this, let me explain. When you clicked Yes to save the changes when closing the table, you were telling Access to remember the previous filter, not to display the records with the filter on when the table is subsequently opened. Right now Access does know what the last filter entailed, and when we click the button to apply the filter, it will know exactly how to display the records in the table.

10. To see the results of the last filter, you can either:

 Click the Toggle Filter icon On the Home / Sort & Filter group.

 Or click on Unfiltered in the Status Bar

The last filter is reapplied, and the 5 Electrical people are again displayed.

11. On the Home / Sort & Filter / Advanced group, click the Clear All Filters.

12. Leave the tblEmployeeHRData table open.

Using Filter by Selection with Multiple Criteria When you filter by selection, you aren't limited to applying only one criterion to the records. By applying one filter and then applying another filter, Access applies the new filter to the currently displayed set of records. Because filters are cumulative, you can easily apply AND conditions in filters. Suppose, for example, you want to see the people in tblEmployeeHRData with Electrical as the Specialty and who also work 40 hours. By applying the first filter to see the Electrical people (which will display 5 records) and then applying the second filter of 40 hours, the result will be those 4 people who have Electrical as the Specialty and who work 40 hours per week.

Hands-On Activity: Using Filter by Selection with Multiple Criteria

Before beginning: Your Home Tech Repair database file is open, with the tblEmployeeHRData table displayed in Datasheet View. No filters are currently applied, so 24 records are displayed in the table.

1. In the Specialty column of tblEmployeeHRData, select Electrical in any record.

2. On the Home / Sort & Filter group, click the Selection icon, and select Equals “Electrical”.

5 filtered records are displayed. Notice that of these 5 Electrical people, 4 work 40 hours and 1 works 28 hours.

3. In the Hours column, select 40 in any record. 4. Click the Selection icon again and select Equals 40

5. Now 4 records are displayed, for those employees who have Electrical as the Specialty and work 40 hours.

6. On Home / Sort & Filter, click Advanced / Clear All Filters, to redisplay all 24 records.

7. Leave the table open.

Filter Excluding Selection This method of applying filters to data involves telling Access what you don't want included in the displayed set of records. For example, you might want to see all records for employees except those with Electrical as the Specialty This works in a similar way as Filter By Selection, in that you first select the value to be used for the filter in the datasheet. The difference, however, is that instead of clicking the Filter By

Selection icon on the toolbar, you choose the Home / Sort & Filter / Selection / Does Not Equal or Does not Contain option. Once a filter has been applied using the Does Not Contain method, the Toggle Filter icon becomes available on the toolbar, so that you can easily remove the filter.

Hands-On Activity: Using Filter Excluding Selection Before beginning: The tblEmployeeHRData table is displayed in Datasheet View with all 24 records displayed.

1. In the Hours column, select 40 in any record.

2. Choose Home / Sort & Filter / Selection. Select Does Not Equal 40.

The 14 records of people who do not work 40 hours are displayed.

3. On the Home / Sort & Filter group, click the Toggle Filter icon

Filter by Form This method of applying a filter is similar to Filter By Selection, except that instead of selecting a value in the datasheet, you enter a value in a filter grid that resembles a blank record in the table. The procedure for applying a Filter by Form is as follows:

1. Choose the Home / Sort & Filter / Advanced / Filter By Form.

A blank record is displayed for the table.

Note: If criteria have been applied to the datasheet in previous filters, they'll be displayed on the blank form when you choose this command.

2. Place the cursor in the appropriate field and enter the desired criteria.

Note: If you enter multiple criteria in different fields, you will be applying an AND condition. That is, all of the indicated criteria must be met in order for a record to be displayed.

3. Once the criteria have been specified, click the Apply Filter button on the toolbar, to

apply the filter to the table data.

Hands-On Activity: Using Filter by Form Before beginning: The tblEmployeeHRData table is open in Datasheet View with all 24 records displayed.

1. Choose Home / Sort & Filter / Advanced / Filter By Form. A blank record is displayed for

the table. <>40 or Null is displayed in the Hours field because it was the last criterion applied to the data.

2. Delete the <>40 or Null criterion in the Hours field. 3. Put the cursor in the Hired field and enter the following criterion:

>=1/1/2001 to specify that you want to see all records that have a Hired date starting with January 1, 2001.

4. Press Enter. Access automatically adds delimiter symbols to the date criterion.

5. On the Home / Sort & Filter / Advanced, click the Apply Filter/Sort.

9 records are included in the filtered subset. Notice the Filter icon in the Hired column.

6. Click the Toggle Filter icon on the toolbar, to return all 24 records to the screen.

Using Filter by Form with Multiple Criteria As with other filters, it is possible to specify multiple criteria when using Filter By Form. This entails applying either an AND or an OR condition to the criteria, and it's absolutely critical that you make the right choice here so that you get correct results. The problem we regularly have with multiple criteria in filters is that the way in which we express our objectives in English is not necessarily the way in which Access (or any other database program, for that matter) is going to interpret our objectives. Here's a common example I have seen many times: Let's assume that in your Customer table you have Name, Address, City, State and ZIP fields. The City field contains cities in the Boston area. You want to see all of your customers in Boston and Cambridge. If you were to specify this criteria as:

City = Boston AND Cambridge

Your filter results would show no records because if each customer has only one record, each of them will only be in one of the cities (Boston or Cambridge), not both cities. This condition must be specified as:

City = Boston or Cambridge

in order to get the desired results. OK, so back to Access. If you're applying an AND condition in your filter, you'll be able to reference the same field or different fields in the filter. For example: Suppose you want to see everyone in your tblEmployeeHRData table who are in Engineering and work fewer than 40 hours. This condition involves two different fields (Specialty and Hours) and would be specified as follows:

Because two different fields have been used, Access will interpret this as an AND condition and will only display records if they satisfy both criteria. Now suppose you want to see a list of employees who work between 30 and 40 hours per week. This condition involves only one field (Hours), so both criteria need to be entered into that field in the Filter By Form grid. This is how you would specify this criterion for the filter:

According to this condition, anyone who works between 30 and 40 hours (inclusive) will be included in the filter, but people who work fewer than 30 hours will be excluded.

The two examples we've looked at so far involve AND conditions. But what about OR conditions? When you choose the Home / Sort & Filter / Advanced / Filter By Form command to display the blank grid, you'll see two tabs in the lower left corner of the screen:

Once you have specified a criterion in a field, if you click the Or tab in the lower left corner of the screen a new (blank) form grid will open so that you may specify the next criterion for that same field. The problem here is that you won't see both criteria on screen at the same time, so you may think you're doing it wrong. But have faith and rest assured that once you've done this 3 or 4 (or 400) times you'll be more comfortable with it. Once you've entered all of the criteria in this way, applying the filter will tell Access to consider all of the indicated criteria as an OR condition.

Here's an example: Suppose in your Employee table you want to see everyone in Engineering OR Electrical who works fewer than 40 hours, OR Labor/Driver people. After the last criterion is specified, the Filter by Form screen looks as follows:

Notice that you only see one criterion in the grid (Labor/Driver). However, if you look in the lower left corner of the screen, you see three Or tabs, which tell you that multiple criteria have been applied.

Let's do a series of activities that illustrate all three of these situations:

Hands-On Activity 1: Applying Multiple Criteria in the Same Field

Before beginning: The tblEmployeeHRData table is displayed in Datasheet View.

Objective: To see all employees who were hired in the first six months of 2001.

1. In Home / Sort & Filter / Advanced / Filter By Form.

The last criterion applied to the Hired field is still reflected in the Filter By Form grid.

Note: If you exited Access between the last Activity and this one, the Filter By Form grid will be blank. Access only remembers the work done in the current session.

2. If the >=#1/1/2001# criterion appears in the Hired field, delete it. 3. Place the cursor in the Hired field, and enter the following criterion:

>=1/1/2001 and <=6/30/2001

4. Press Enter. Access adds the # delimiter symbols to the dates.

[You will need to widen the Hours column in order to see the complete entry.]

5. On the toolbar, click the Advanced/ Apply Filter/Sort.

Of the 24 original employee records, only 5 employees were hired in the first 6 months of 2001.

6. Remove the filter. (Click Advanced / Clear All Filters)

Hands-On Activity 2: Applying Multiple Criteria in Different Fields Before beginning: The tblEmployeeHRData table is displayed in Datasheet View. No filters are currently applied.

Objective: To see all Engineering employees who were hired before 1999.

1. On the toolbar, click the Advanced / Filter By Form command. 2. Delete the Hired criterion from the grid (if you did not Clear All Filters from the previous

exercise.) 3. In the Hired field, enter:

<1/1/1999

to specify that you want to see only the records in which the Hired date precedes the first day of 1999 (that is, is before that year).

4. In the Specialty field, choose Engineering from the drop-down list.

Your Filter By Form grid should look as follows:

5. Click the Advanced/Apply Filter/Sort command on the toolbar. Of the five Engineering

employees, only two were hired before 1999.

6. Remove the filter.

Hands-On Activity 3: Combining AND and OR Conditions in Filters

Before beginning: The tblEmployeeHRData table is displayed in Datasheet View. No filters are applied, so that all 24 records are displayed.

Objective: To see all Electrical employees who work fewer than 40 hours per week, Engineering employees who work fewer than 40 hours per week, and Plumbing people.

1. On the toolbar, click the Advanced / Filter By Form command. 2. Select "Electrical" as the criterion in the Specialty field. 3. Delete the criterion from the Hired field. 4. Place the cursor in the Hours field, and enter the following criterion:

<40 5. In the lower left corner of the screen, click the Or tab.

The Filter By Form grid is again blank. Access has recorded your criteria on the previous OR tab, and is now prepared to add to the previous selections.

6. In the Specialty field, choose Labor/Driver from the drop-down list. 7. On the same line of the grid, enter <40 in the Hours field.

8. In the lower left corner of the screen, click the second Or tab. The Filter By Form grid is again

blank. 9. Enter Plumbing in the Specialty field. (Choose it from the drop-down list.) 10. Click the Advanced/Apply Filter/Sort command. 3 records are displayed. Notice that the

Plumbing employee works 40 hours, but is listed here because we didn't apply any Hours criteria to that group of employees. All of the Engineering and Electrical people who are listed, on the other hand, work fewer than 40 hours.

11. Remove the filter.

On Your Own Apply the following filters to tblEmployeeHRData using Filter By Form:

 All Engineering employees with an Hourly Rate that is greater than $20. (The result should be 3 records).

 Electrical employees with an Hourly Rate between $25. and $35, inclusive. (The result should be 3 records.)

 Administration people hired after 6/1/2001, Electrical people hired since 2000, and

Engineering people hired since 2001. (The result should be 6 records.)

[Remove all filters when done.]

Using Wildcards in Filters Access recognizes the * and ? wildcard characters, in the event you want to define criteria in a more general way. The * wildcard matches any number of characters in the location of the wildcard, whereas the ? wildcard matches one character per wildcard character. For example, using E* as the criterion in the Specialty field would display only the specialties that begin with the letter E (Electrical and Engineering). Using the criterion B??? (4 character criterion) in the Last Name field would match Bard and Boyd, but not Butler. When you use the * wildcard and press Enter after entering the condition, Access adds the word Like to the beginning of the condition, and adds quotation marks around the criterion.

Hands-On Activity: Using Wildcards in Filters

Before beginning: The tblEmployeeHRData table is displayed in Datasheet View. No filters are currently applied to the table.

First, we'll find all employees whose specialty begins with the letter E. 1. Click the Advanced / Filter By Form command. 2. In the grid, delete all of the criteria that were specified in the last filters

Note: You'll need to click each of the Or tables in the lower left, to make sure there are no residual criteria from previous filters. Remember that Access remembers the criteria that have been applied to tables. You will have the opportunity to close the table without saving the changes, in which case the conditions will be cleared. But as long as you leave the table open and continue applying different filters to it, you need to keep checking to see what's currently in force.

3. Make sure the Look for tab is selected in the lower left corner of the window. This is where

you start entering criteria.

4. Place the cursor in the Specialty field, and enter: E* 5. Press Enter, to move the cursor to the Work Ext field. Access changes the criterion in the

Specialty field to: Like "E*". 6. Click the Advanced / Apply Filter/Sort command. 11 records are displayed.

7. Remove the filter.

Next, we'll use the * wildcard in the Hire Date field, to display all employees hired in the year 2000.

1. Click the Advanced / Filter By Form command. 2. Delete the Like "E*" criterion from the Specialty field. 3. In the Hired field, enter the following criterion: */*/2000 4. Press Enter. Access changes the criterion to Like "*/*/2000" 5. Apply the filter. 6 records are displayed.

6. Remove the filter.

Filter for Input This filter option enables you to apply conditions to the filter directly in the Datasheet or Form. To use it, display the data in either Datasheet or Form View, and right-click on the field where you want to enter a criterion. In the Filter For text box, type the criterion for the field. Once you have entered the criterion, press Enter to apply the filter. To apply multiple criteria in the same field, you must use an AND or an OR operator within the field. For example, the criterion "Electrical" Or "Engineering" would display people in both

specialties. The criterion Between 1/1/2001 And 12/31/2001 in the Hired field would list people hired in the year 2001. To apply multiple AND conditions to different fields, enter one criterion and apply the filter. Then enter the next criterion and apply the filter. The filter conditions are cumulative, as with Filter by Selection. It isn't possible to apply OR conditions that involve different fields using Filter By Input.

Hands-On Activity: Using Filter for Input

Before beginning: The tblEmployeeHRData table is displayed in Datasheet View, with all 24 records displayed.

1. Click the Advanced / Filter By Form command. 2. Delete all of the criteria that were previously applied to this table.

Remember: If you don't clear the previously applied filters before applying new filters to your data, you can easily end up with incorrect results.

3. Click the Advanced / Apply Filter/Sort command. All 24 records should be displayed.

4. Right-click in the Specialty field.

Note: It doesn't matter in which record you right-click.

5. Select Text Filters…Equals

6. Click to place the cursor in the Specialty is equal to: text box, and enter: Masonry

7. Press OK, to apply the filter. 2 records are displayed.

8. Remove the filter.

9. Close tblEmployeeHRData. Click No in answer to the question regarding whether or not you want to save the changes to the design of the table.

Assignment for Week 5

1. Do Final Database Project Assignment #2. Due by midnight Eastern Standard Time

on Sunday February 21, 2016.

2. This is the second of the 8 individual assignments that you'll be doing to create your own database. In this assignment, you're going to do the field definitions, create your relationships, and populate your tables with data. It is extremely important that your tables be set up correctly, because all future assignments will depend on your having the correct number of records. So please pay close attention to what you end up with in your tables. I have provided all of the data in the form of an Excel file, for you to transfer into your database, so you won't have any data-entry to do. Please make sure the data are being transferred in correctly. If an Excel worksheet has 25 records and you're transferring those data into your Access table, your Access table should end up with 25 records. (I know this is a "Duh" thing to say, but I have to mention it nonetheless. Stay awake while doing this! In previous semesters there were cases where the tables looked OK initially, but when we got to queries and reports, suddenly the wrong number of records were showing up. And this went back to the contents of the source data in the tables.) Here's the assignment:

1. Based on the plan you developed in Assignment #1 for Week 4, create all of the tables for the database. Be sure to use the Leszynski Naming Convention for all table names.

2. Define all fields. Again, be sure to use the Leszynski Naming Convention for all field names.

3. Set captions for all fields.

4. Create all necessary and appropriate relationships.

5. Populate the tables with the data that are provided in the Excel workbook named Final Project Data.xls.

6. The name of your database will be Final DB Week x. Therefore, this week's database

should be named <Your First Name> Final DB Week 5.