Access Ch. 1
Create a New Object
1. Click the Create tab on the Ribbon. 2. Click the object button of the object you wish to create. You may be prompted to save an open object before Access creates the new object. The button choice determines the view that displays when the object opens. For example, selecting the Table button opens a table in Datasheet View, whereas selecting the Table Design button opens a table in Design View. Wizard buttons, such as Query Wizard, launch a wizard that guides you through the steps to creating that object.
View and Edit Database Properties
1. Click the File tab to open the Backstage view and then click the Info button. 2. Click the View and edit database properties link (Figure 1-22) to open the Database Properties dialog box. 3. Click the Summary tab Some properties may already be completed, depending on the settings selected when Office was installed on the computer. 4. View the properties that have been entered. 5. Edit any desired properties. Be sure to complete any properties required by your instructor. 6. Click OK to save and close.
Create a New Database Using a Template
1. Click the File tab to open the Backstage view if an Access database is already open. If Access is closed, open Access. The Start page displays showing the Recent list on the left and thumbnails of sample templates on the right. In this case, skip to step 3. 2. Click the New button. The list of sample templates displays (Figure 1-20). The templates may display in a different order based on the most recently selected template. 3. Select the template you wish to use. 4. Type the file name for your database in the File Name box. 5. Click the Browse for a location to put your database button (Figure 1-21) to select the location on your computer or USB drive to save the database and click OK. 6. Click the Create button. The new database opens. A Welcome form may display based on the template selected. Click the Get Started button on the Welcome form. 7. Click the Enable Content button in the Security Warning message bar if prompted.
Find Data in a Table Using the Search Box
1. Click the Search box in the Navigation bar. 2. Enter the search value you want to locate. Access evaluates the criteria as you enter each character and moves to the first field that matches those criteria As you type more data into the Search box, Access continues to locate the first field that matches. The search is not case sensitive. A search for Health matches health, Health, and HEALTH. If a match is not found, the field or record that was previously selected remains highlighted.
Modify the Column Width of All Fields Using AutoFit
1. Click the Select All button (Figure 1-75), in the upper-left corner of the datasheet, to select the entire datasheet. All of the rows and columns are highlighted in blue. 2. Place your pointer on the right border of any of the field names. 3. Double-click the resize arrow All the fields automatically adjust to their widest entry or to the width of the field caption if that is larger than the contents.
Open a Table in Datasheet View
1. Click the Shutter Bar Open/Close Button or press F11 if the Navigation Pane is closed. 2. Double-click the table name. The table opens in Datasheet view.
Open a Datasheet Object
1. Click the Shutter Bar Open/Close Button or press F11 if the Navigation Pane is closed. 2. Right-click the object name and select the desired view from the context menu (Figure 1-28). The object opens in the work area. Select Open to open the object using its default view. Select Layout View, if available, to modify the design visually. However, not all design changes can be made in Layout view. Select Design View to modify the structure of the object.
Open a Table in Design View
1. Click the Shutter Bar Open/Close if the Navigation Pane is closed or press F11. 2. Select the table in the Navigation Pane and press Ctrl+Enter
Make Multiple Formatting Changes with the Datasheet Formatting Dialog Box
1. Click the Text Formatting launcher [Home tab, Text Formatting group] to open the Datasheet Formatting dialog box 2. Select the desired settings of the different options. 3. Click OK when finished.
Create a New Table in Datasheet View
1. Click the Create tab on the Ribbon. 2. Click the Table button in the Tables group (Figure 1-34). A new table opens in Datasheet view.
Sort Data in a Table on Multiple Adjacent Fields
1. Click and hold the pointer over the Field name of the first column you want to sort. With the selection pointer still displayed, drag the pointer to the adjacent columns. 2. Release the pointer. 3. Click either the Ascending or Descending button [Home tab, Sort & Filter group]. The datasheet updates to show the results of the sort. Figure 1-93 shows the employee records sorted in ascending order by the Department [Administration, Health and Benefits, Insurance Sales, and Property and Casualty] and the Title [Administrative Assistant, Chief Executive Officer, Chief Information Officer, Chief Operating Officer, Executive Assistant, etc.] fields. In the example shown in Figure 1-93, column entries "tie" for the Department, so the Title field determines which record appears at the top of each Department.
Remove Filter Criteria from All Fields
1. Click the Advanced button [Home tab, Sort & Filter group] to open the drop-down list. 2. Select Clear All Filters.
Add New Fields by Entering Field Names
1. Click the Click to Add column header. The drop-down list of data types displays (Figure 1-38). Figure 1-38 Click to Add data types Technically, the Rich Text option is not a data type. Rather it assigns additional formatting selections to the Long Text data type. 2. Select the appropriate data type. The new field is added and assigned a default field name, such as Field1, as shown in Figure 1-39. Figure 1-39 Field name column header 3. Type the desired field name in the column header. Recall that each field name is limited to 64 characters. Each field in a table must have a unique name. 4. Press the Enter key. You move to the next column with the list of data types displayed. 5. Repeat steps 2 through 4 to add the additional fields into your table.
Close a Database
1. Click the Close button [File tab] to close a database. The current database closes. Access remains open and you are positioned on the Home tab (Figure 1-29). Notice that the Navigation Pane does not display because a database is not open.
Back Up a Database
1. Click the File tab to open the Backstage view. 2. Click the Save As button. Your screen should look similar to Figure 1-30, with Access Database selected in the Save Database As area. 3. Select Back Up Database in the Advanced grouping of the Save Database As section. 4. Click the Save As button. Select the desired location in which to store the backup database file. Access suggests a filename, such as CollegeOfSciencesClasses-01_2020-01-28, which adds today's date to the end of the file name. This is a good practice, because you can quickly determine when a backup copy was created. As desired, click the File Name box and change the default name. Click the Save button
Find Data in a Table Using the Find Button
1. Click the Find button [Home tab, Find group] (Figure 1-88) to open the Find and Replace dialog box If you want to search only in a specific field, select that field before you click the Find button. 2. Enter the search value you want to search in the Find What box. 3. Click the Look In drop-down list to select either Current field or Current document. If Current field is selected, the search only looks for a match in the current field. 4. Click the Match drop-down list to select the appropriate option. Any Part of Field: The search value must be somewhere in the field. Use this option to find a value that contains the search value anywhere in the field. Whole Field: The search value must match the entire contents of a field. Use this option to find an exact match. Start of Field: The search value is at the beginning of the field. Use this option to find a value that starts with a certain character or number. 5. Click the Search drop-down list to select All, Up, or Down. Selecting Up or Down searches in that direction from the current record only. 6. Check the Match Case check box to perform a case-sensitive search and limit the results to the case of the search value. 7. Click the Find Next button to execute the search. Access searches the table to find the first record that meets the criteria. If that is the desired record, click the Cancel button to close the Find and Replace dialog box or click the Find Next button to continue searching. If the search value does not exist in the table, Access displays an information message box, indicating the record was not found. 8. Click Cancel to close the Find and Replace dialog box.
Add New Fields Using the More Fields Button
1. Click the More Fields button [Fields tab, Add & Delete group] to display the drop-down list. 2. Select the appropriate data type. 3. Type the field name in the column header. 4. Press the Enter key.
Unhide Table Fields
1. Click the More button [Home tab, Records group] and then select Unhide Fields to open the Unhide Columns dialog box (Figure 1-80). Checked fields display in the table, and unchecked fields are hidden. ANOTHER WAY Right-click the field column heading and select Hide Fields from the context menu. 2. Click all of the field check boxes that you wish to unhide. 3. Click Close.
Modify the Navigation Pane Category and Group Filter
1. Click the Navigation Pane arrow, shown in Figure 1-4, to open the Navigation menu. 2. Select the desired category. The Navigation menu closes. The Navigation Pane updates to display the objects based on your category selection. 3. Click the Navigation Pane arrow to open the Navigation menu. 4. Select the desired filter selection. 5. The Navigation menu closes. 6. The Navigation Pane updates to display the objects based on your filter selection.
Recover Database Objects from a Backup
1. Click the New Data Source button [External Data tab, Import & Link group], select From Database, and then select Access The Get External Data - Access Database dialog box launches 2. Click the Browse button to launch the File Open dialog box. 3. Locate and select the file that contains the backup copy of the database. 4. Click the Open button. The File Open dialog box closes. 5. Select the Import tables, queries, forms, reports, macros, and modules into the current database radio button. 6. Click OK to open the Import Objects dialog box 7. Click the tab corresponding to the type of object you wish to import. 8, Select the objects you wish to import. You can select multiple objects on a tab. Click the Select All button to select all of the objects on the tab. 9. Repeat steps 7 and 8 to select objects from a different tab. 10. Click OK. The Save Import Steps screen displays. 11. Click the Close button. 12. Open the object to verify that the object was successfully recovered.
Import Data from Excel
1. Click the New Data Source button [External Data tab, Import & Link group], select From File and then select Excel The Get External Data - Excel Spreadsheet dialog box launches 2. Click the Browse button to launch the File Open dialog box. 3. Locate and select the Excel file that contains the records you want to import. 4. Click the Open button. The File Open dialog box closes. 5. Select the Append a copy of the records to the table radio button. 6. Click the drop-down arrow in the table name box and select the desired table. 7. Click OK to launch the Import Spreadsheet Wizard (Figure 1-63). The data records should display in the Wizard window. 8. Click the Next button to advance to the next page of the Wizard. Access confirms the name of the table to append the records. 9. Click Finish. The Wizard displays the Save Import Steps screen. If you are frequently going to repeat an import process, you can save the steps to perform the action more quickly. 10. Click the Close button. 11. Open the table to verify that the records were successfully added into the table.
Create a New Blank Database
1. Click the New button [File tab] if an Access database is already open. If Access is closed, open Access. The Start page displays where you can open a new blank database. ANOTHER WAY Ctrl+N opens the Backstage view with the New button selected. 2. Select Blank database 3. Type the file name in the File Name box. 4. Click the Browse for a location to put your database button to select the location on your computer or USB drive to save the database and click OK. 5. Click the Create button. The database is created. A new table is automatically created and opens in Datasheet View.
Open a Database
1. Click the Open button [File tab] to open an existing database. 2. Click the Browse button. The Open dialog box displays. 3. Select the database file and click the Open button. You can also double-click the file name to open the database
Save a New Table
1. Click the Save button [File tab]. If this is the first time you are saving the table, the Save As dialog box displays. 2. Type the name you wish to use in the Table Name box. 3. Click OK.
Save an Object
1. Click the Save button [File tab]. The Save As dialog box opens if this is the first time you save the object. 2. Type the name you wish to use in the Save As 'Object Name' box. Instead of displaying the phrase "Object Name," Access displays the type of object you are saving. 3. Click OK.
Add a Total Row
1. Click the Totals button [Home tab, Records group]. A Total row is added below the append row. The Totals button on the Ribbon is gray to indicate that a Total row has been added. 2. Click the Total row cell of the field you wish to summarize, click the drop-down arrow in that cell, and select the desired function The available functions vary depending on the data type of the field. The table updates and the calculated value displays in the Total row cell. You can add totals to multiple fields in the table. As records are added or deleted, the totals automatically recalculate.
Add New Fields Using a Data Type Button
1. Click the appropriate data type button in the Add & Delete group on the Fields tab. The new field is added and assigned a default field name, such as Field1. 2. Type the field name in the column header. 3. Press the Enter key.
Add New Fields by Entering Data
1. Click the cell in the first Click to Add column. 2. Type the data value into the cell. 3. Click the Click to Add column header (Figure 1-44) to add the field. Access assigns a default field name, such as Field1, and selects that column. Click to Add becomes the next column. Access automatically assigns a data type for the field based on the contents that you enter. As desired, you can change the data type. 4. Repeat steps 1 through 3 to add all the additional fields into your table.
Filter Data Using Filter by Selection
1. Click the cell that contains the value you wish to filter. You can select only part of the cell contents as desired. 2. Click the Selection button in the Sort & Filter group on the Home tab The drop-down list shows the available filter options. The available filter options depend on the data type of the field and what criterion is selected. If you only select part of a field, the beginning of the field, or the end of the field, the options differ. 3. Select the desired filter option. The datasheet updates to show the results of the filter. The filter status message changes, indicating the datasheet is filtered. The Field name cell displays a Filter symbol to the right of the drop-down arrow, indicating this field has been filtered Once a filter has been applied, switch between viewing the complete dataset and the filtered results by clicking the Toggle Filter button [Home tab, Sort & Filter group], shown in the top image in Figure 1-96. When toggled off, the filter status message, shown in the bottom image of Figure 1-96, changes to Unfiltered to indicate that a filter exists but is toggled off.
Filter Data Using a Text, Number, or Date Filter
1. Click the drop-down arrow to the right of the field name you wish to filter. 2. Select Text Filters on the drop-down list The button name is context sensitive and determined by the type of field you are filtering. The drop-down list displays the available filter options. The filter options are also context sensitive and change based on the type of field. 3. Select the desired filter option on the drop-down list to open the Custom Filter dialog box (Figure 1-98). The content of this dialog box depends on the filter option selected. 4. Type the word or value you wish to filter. 5. Click OK. The datasheet updates to show the results of the filter. The filter status message changes, indicating the datasheet is filtered. The Field name cell displays a Filter symbol to the right of the drop-down arrow that indicates this field has been filtered.
Filter Data Using "OR" Compound Criteria on One Field
1. Click the drop-down arrow to the right of the field name you wish to filter. The drop-down list opens. Initially, all the check boxes are selected. 2. Click the (Select All) check box to clear all the check boxes. 3. Click all of the check boxes on the drop-down list that contain the desired criteria 4. Click OK. The datasheet updates to show the results of the filter.
Sort Data in a Table on a Single Field Using the Drop-Down List
1. Click the drop-down arrow to the right of the field name you wish to sort. The column turns blue, indicating it has been selected. The drop-down list opens 2. Select either Sort A to Z or Sort Z to A on the drop-down list. The datasheet updates to show the results of the sort. If you are sorting a numeric field, the drop-down list options are Sort Smallest to Largest and Sort Largest to Smallest.
Remove Filter Criteria from a Single Field
1. Click the drop-down arrow to the right of the first field name where you wish to remove a filter. The drop-down list opens. 2. Select Clear filter from Field name to remove the filter. The phrase "Field name" will not display, but instead the actual field name that has been filtered displays. The datasheet updates to show the results of this filter being removed.
Filter Data Using "AND" Compound Criteria on Multiple Fields
1. Click the drop-down arrow to the right of the first field name you wish to filter. The drop-down list opens. Initially, all the check boxes are selected. 2. Click the (Select All) check box to clear all the check boxes. 3. Click all of the check boxes on the drop-down list that contain the desired criteria. 4. Click OK. The datasheet updates to show the results of the filter. 5. Click the drop-down arrow to the right of the second field name you wish to filter. 6. Click the (Select All) check box to remove that criterion. 7. Click all of the check boxes on the drop-down list that contain the desired criteria AND criteria 8. Click OK. The datasheet updates to show the results of the additional filter. Only the records that meet both of the criteria display. 9. Add a filter to additional columns by repeating the earlier steps.
Sort Data in a Table on a Single Field Using the Sort & Filter Group
1. Click the field name to select the column you wish to sort. 2. Click either the Ascending or Descending button [Home tab, Sort & Filter group] The datasheet updates to show the results of the sort. Figure 1-91 shows the employee records sorted in descending order by the Department field The field name cell changes to display either an up or down sort indicator arrow to the right of the drop-down arrow to visually indicate that the datasheet has been sorted on this field. If you want the table records to remain in this sorted order, you must save the table.
Add Records in a Table
1. Click the first empty cell in the append row. ANOTHER WAY Click New [Home tab, Records group] to move automatically to the first empty cell in the append row. 2. Type the data value. The append row asterisk changes to a pencil icon (Figure 1-51), indicating that you are editing a record that has not yet been saved. A new append row is added. 3. Press the Tab key to move to the next field. ANOTHER WAY Press Enter to move to the next field. Press Shift+Tab to move to the preceding field. 4. Continue entering all remaining data values for the record, pressing Tab or Enter after each value. After you press the Tab or Enter key of the last field, you advance to the first field of the next record. If you enter a duplicate value for the primary key (Figure 1-52), Access displays an error message (Figure 1-53) when you navigate out of the record. If you enter a value that does not match the data type, Access displays an error message when you navigate out of that field
Delete a Record from a Table
1. Click the record selector cell of the record you wish to delete 2. Click the Delete button [Home tab, Records group]. Access displays a message box warning you about the record deletion and informing you that it is not possible to undo a delete operation. Click the Yes button if you want to confirm the delete operation.
Modify the Row Height Using the Context Menu
1. Click the record selector of a row in your table. The selected row is highlighted in blue. Because your changes affect all the rows, it does not matter which row you select. 2. Place your pointer over the record selector and right-click to open the context menu 3. Select Row Height to open the Row Height dialog box. 4. Type the desired height in the Row Height box. 5. Click OK to close the dialog box.
Rename a Table
1. Close the table if it is open. 2. Right-click the table name in the Navigation Pane and select Rename from the context menu. 3. Type a new name and press Enter.
Edit the Default Primary Key
1. Double-click the cell containing the ID field name (column header) in your table to select it (Figure 1-36). Figure 1-36 Field name column header 2. Type the new name for your field and press the down arrow key. This action updates the change to the field name and keeps you in the same column. 3. Change the default Data Type of AutoNumber as desired. Click the Data Type [Fields tab, Formatting group] drop-down arrow to display the list of data types (Figure 1-37). Figure 1-37 Field data types Select the desired Data Type property.
Save a Copy of an Object
1. Open the desired object if not already open. 2. Click the Save As button [File tab]. 3. Click the Save Object As button in the File Types area and the Save Object As button in the Database File Types area Figure 1-26 Save Object As button 4. Click the Save As button to open the Save As dialog box Figure 1-27 Save As dialog box Access suggests the default object name of "Copy of" and the original object name. Access selects the same object type as the original object. 5. Type the name you wish to use in the Save 'Object name' to box. 6. Select the type of object you wish to create. Make a duplicate of the object by selecting the same object type. Create a different type of object, based on the original objects settings, by selecting another object type. 7. Click OK.
Modify the Row Height
1. Place your pointer on the border of a record selector of a row in your table. Because your changes affect all the rows, it does not matter which row you select. The resize arrow appears 2. Click, hold, and drag the resize arrow to increase or decrease the height of the rows. A black, horizontal line moves to indicate the new height 3. Release the resize arrow. The rows change to the new height. The content of columns that are narrower than needed automatically wraps within the cell
Modify the Navigation Pane Width
1. Place your pointer on the right border of the Navigation Pane. The resize pointer appears as a white two-pointed arrow 2. Click, hold, and drag the resize pointer to increase or decrease the width of the Navigation Pane. 3. Release the resize pointer. The pane changes to the new width.
Modify the Column Width of Selected Fields
1. Place your pointer on the right border of the field name of the column you wish to change. The resize arrow appears If you wish to change the width of adjacent columns, you must first select all of the columns. Click and hold to select the first column, then drag the selection pointer to the adjacent columns (Figure 1-74). Place your pointer over the border of the right-most field. 2. Click, hold, and drag the resize arrow to increase or decrease the width of the field(s). A black, vertical line moves with you to indicate the new width. 3. Release the resize arrow. The field column(s) changes to the new width.
Modify the Field Order
1. Place your pointer over the field name of the column you wish to move. The selection pointer appears 2. Click to select the column. The selected column is highlighted in blue. The pointer changes to the normal pointer. 3. Click, hold, and drag the pointer to move the column to the new location. A black, vertical line (Figure 1-66) moves with you to indicate the location where the column will be moved. 4. Release the pointer. The column moves to the new location
Delete a Field
1. Place your pointer over the field name of the field you wish to delete. The selection pointer appears as a black down arrow. 2. Click to select that column. 3. Click the Delete button [Fields tab, Add & Delete group] to delete the field. Access displays a message box asking you to confirm the deletion. 4. Click Yes to delete the field.
Add New Fields between Existing Fields
1. Put your pointer on the field name of the column to the right of where you wish to insert a new field. The selection pointer appears as a black down arrow 2. Click to select that column. The column is highlighted in blue. 3. Right-click to open the context menu. 4. Select Insert Field (Figure 1-46). The new field is inserted to the left of the selected field, as shown in Double-click the field name to change the name.
Rename a Database Object
1. Right-click the object and select Rename from the context menu. 2. Type a new name and press Enter.
Add a Table Description
1. Right-click the table name in the Navigation Pane and select Table Properties from the context menu. The Table Properties dialog box opens (Figure 1-50). Instead of displaying the word "Table," Access displays the table name. 2. Type a description in the Description property. 3. Click OK.
Edit Field Properties in Datasheet View
1. Select the cell containing the field name (column header) you wish to change. 2. Click the Name & Caption button [Fields tab, Properties group]. The Enter Field Properties dialog box opens 3. Type a new value in the Name property as desired. 4. Type a value in the Caption property as desired. 5. Type a value in the Description property as desired. 6. Click OK to close the dialog box. 7. Click the Data Type option drop-down arrow [Fields tab, Formatting group]. 8. Select the desired Data Type property. 9. Type a new value in the Field Size property [Fields tab, Properties group]
Hide a Table Field
1. Select the column to be hidden. 2. Click the More button [Home tab, Records group] and then select Hide Fields.
Navigation Buttons
Access provides many different ways to navigate among the data records stored in your tables. For example, you can navigate using the keyboard arrow keys and the scroll bar. This works fine on tables that do not have too many records. However, in many cases, you may want to get to a specific record more quickly. The Navigation bar provides additional methods to move among table records. It is located in the bottom-left corner of a table viewed in Datasheet view. The Navigation bar contains navigation buttons, a filter status message, and a Search box (Figure 1-64). Searching and filtering will be explained in SLO 1.6: Using Search, Sort, and Filter Tools to Find Records in a Table. The Navigation buttons are located on the left side of the Navigation bar.
Remove Sorting Criteria
To return the records in your datasheet to their original order, remove the Sort criteria. 1. Click the Remove Sort button [Home tab, Sort & Filter group] to remove all Sort criteria.
