Access labs 2017

Lesson

3

Lesson 3 - Working with Data

Lesson Topics:

3.1 Editing Data

3.2 Formatting Table Data

3.3 Importing Excel Data into an Existing Table

3.4 Importing a Text File into a New Table

3.5 Selecting and Deleting Records

3.6 Sorting Records

3.7 Finding and Replacing Data

3.8 Filtering Data by Selection

3.9 Filtering Data by Form

3.10 Using Common Filters

3.11 Hiding/Unhiding Columns

3.12 Freezing Columns

3.13 Rearranging Columns

3.14 Displaying Column Totals in a Datasheet

3.1 Editing Data

In this lesson, you will learn how to edit existing data in a table

I

n the last lesson, you learned how to enter data into a table. Once your data is entered, you can modify it at any time by clicking in the field whose data you wish to change and then typing in your desired changes. When tabbing to a field, all of the data in the field is automatically highlighted so you do not even need to delete the data first - typing your changes will automatically overwrite the existing data. XE "Data:editing in tables"

To Edit Data in a Table

1. Display Tables in the Navigation Pane.

2. Open the table in Datasheet View by double-clicking the table name.

3. Click in the field that contains the data to be changed.

4. Make your changes.

Let’s Try It!

What	Why
1. Open Microsoft Access.	Opens the Microsoft Access application.
2. Click the Open Other Files link in the left pane in Backstage View.	Displays the Open pane.
3. Click the Browse button.	Displays the Open window.
4. Click Desktop on the left side of your screen.	Opens the Desktop folder.
5. Double-click the Lesson Files folder.	Opens the Lesson Files folder and displays the files in that folder.
6. Select the Video Sales 3 file and then click Open.	Opens the Video Sales 3 database.
7. If necessary, click the Navigation Pane drop-down arrow and select Tables from the list.	Displays table objects.
8. Double-click tblOrders.	Opens tblOrders in Datasheet view.
9. In the Shipper field, select UPS as shown below.	Selects the data we wish to replace.
10. Type: Federal Express	Overwrites the selected data with “Federal Express.”
11. In the Order Date field, click after the 2 in 5/2 as shown below.	Places the insertion point where we wish to make a change.
12. Press the Backspace key and then type: 1	Changes the date from 5/2/2015 to 5/1/2015.
13. Press Tab.	Notice the calendar button that appears when you enter a date field. This feature allows you to select your date from a calendar.
14. Click the Calendar icon that appears next to the Ship Date field as shown below.	Opens the calendar allowing you to choose a date from the calendar.
15. Click the right-pointing blue arrow on top of the calendar.	Moves to the next month.
16. Click on the 3 in the calendar.	Sets June 3rd as the Ship date.
17. Press Enter.	Confirms the entry.

3.2 Formatting Table Data

In this lesson, you will learn how to format data in a table.

I

f you are unhappy with the size or font of the text in your tables, you can change the Formatting of your table text. For instance, you can change the font size, font type, font or fill color and text alignment. To apply formatting to a table, click the desired Font command button in the Text Formatting command set under the Home tab. XE "Data:formatting" XE "Formatting Table Data" XE "Rich Text Format: using in tables"

Any formatting changes will be applied to the entire table. However, Access provides the ability to add rich text formatting to fields in your table that are set to the Long Text data type. For instance, you can apply bold formatting or change the text color of individual words. To enable rich text formatting in Long Text fields, you must switch to Design view and change the Text Format Property from Plain Text to Rich Text. Once you have changed the data type, the Rich Text command set is also enabled, allowing you to add additional formatting such as bullets and numbering, indenting and highlight color.

To Apply Formatting to Table Data

1. Open the table in Datasheet view.

2. Click the Home tab on the Ribbon.

3. Click the desired formatting command button in the Text Formatting command set on the Ribbon.

To Enable Rich Text Formatting

1. Open the table in Datasheet view or Design View.

2. Change the Data Type of the field to Long Text.

3. If in Datasheet view, switch to Design View.

4. Select the Field Name for the field you wish to modify.

5. In the Field Properties box, click in the Text Format property box and choose Rich Text from the drop-down list.

Let’s Try It!

What	Why
1. Click the Home tab on the Ribbon.	Ensures that the Home tab is active.
2. Click the Font Size drop-down arrow and select 14 from the list as shown below.	Changes the Font Size of the entire table to 14.
3. Click the arrow on the Font Color button and select Red from the color palette as shown below.	Changes the font color of the entire table to Red.
4. Click the Font Color button arrow again and select Automatic from the color palette.	Changes the font color of the entire table back to black.
5. Click the Close button on tblOrders. Click No when asked to save your changes.	Closes the table without saving our design changes.

3.3 Importing Excel Data into an Existing Table

In this lesson, you will learn how to import data from another application into Access.

A

n extremely powerful feature of Access is its ability to import data from other applications. For instance, you can bring in data from an Excel spreadsheet or even from a plain text file into a new or existing table. You can import data from a variety of formats such as: XE "Data:importing Excel data" XE "Importing: Excel data into tables"

· HTML

· Text Files (delimited or fixed-width)

· Outlook Folder

· Data Services

· XML

· ODBC Databases such as SQL and Oracle

· SharePoint List

In this lesson, we are going to learn how to import data from Microsoft Excel into Access.

To Import Data from Excel

1. Click the External Data tab on the Ribbon.

2. Click the Excel command button on the Import & Link group of the Ribbon.

3. Click the Browse button and navigate to the folder that contains the file you wish to import. Select the file and click Open.

4. To import data to a new table, click the radio button next to Import the source data into a new table in the current database.

5. To import data into an existing table, click the radio button next to Append a copy of the records to the table: and choose the desired table from the drop-down list.

6. To link to the spreadsheet rather than importing it, click the radio button next to Link to the data source by creating a linked table.

7. Click OK to launch the Wizard.

8. Select the Worksheet you wish to import from the Show Worksheets window. Click Next.

9. If the first row of the worksheet contains column headings, click the First Row Contains Column Headings checkbox. Click Next.

10. Click Finish.

Let’s Try It!

What	Why
1. Double-click tblCustomers.	Observe the table – it is empty.
2. Click the Close button for tblCustomers.	Closes tblCustomers.
3. Click the External Data tab on the Ribbon.	Displays commands for working with external data.
4. Click the Excel command button on the Import & Link group of the Ribbon.	Displays the Get External Data – Excel Spreadsheet dialog box.
5. Click the Browse button.	Displays the File Open dialog box from where we navigate to the file we wish to open.
6. Navigate to the Lesson Files folder (if necessary) and then select the Customers file.	Selects the Excel file we wish to import.
7. Click Open.	Closes the Get External Data – Excel Spreadsheet dialog box.
8. Click the radio button next to Append a copy of the records to the table and select tblCustomers from the drop-down list as shown below.	Selects the table into which we want to import the file.
9. Click OK.	Launches the Import Spreadsheet Wizard.
10. Click Next.	As there is only one worksheet in our file (Sheet 1) we will accept the default and move to the next step of the wizard.
Worksheets in the Excel Workbook
11. Ensure that the checkbox next to First Row Contains Column Headings is checked.	As the first row of the spreadsheet contains the headings or field names, we want to ensure that we check this box. Otherwise, the headings will be imported as regular data.
12. Click Next.	Moves to the next step of the wizard.
13. Click Finish.	Completes the import process and imports the data.
14. Click Close.	If you repeatedly import the same file into Access, you can now save your import steps so that you can quickly execute the same import without using the Wizard.
15. Double-click the tblCustomers table.	Opens tblCustomers in Datasheet view. There should be 23 records.
16. Click the Close button for tblCustomers.	Closes tblCustomers.

3.4 Importing a Text File into a New Table

In this lesson, you will learn how to import data from a comma-delimited text file into a new Access table.

I

f you wish to import data into Access from an application that Access does not support, the easiest way to accomplish this might be to first export the data as a text file, and then import it into Access. Most applications support exporting data to text files.

There are two types of text files: delimited and fixed-width. In delimited text files, the fields are separated by a delimiter such as a comma or quotation marks. In a fixed width file, fields are separated by a tab stop of equal distance. The most common type of file is the comma delimited file. XE "Delimited Text Files:defined"

Before importing a text file, you may have to “clean it up” a bit first. For instance, if you are importing a comma delimted text file, there can be no commas within the data, as Access treats each comma as a new field. XE "Importing:text field into tables" XE "Data:importing - text files"

To Import A Text File

1. Click the External Data tab on the Ribbon.

2. Click the Text File command button in the Import & Link command set.

3. Click the Browse button and navigate to the folder that contains the file you wish to import. Select the file and click Open.

4. To import data to a new table, click the radio button next to Import the source data into a new table in the current database.

5. To import data into an existing table, click the radio button next to Append a copy of the records to the table: and choose the desired table from the drop-down list.

6. To link to the file rather than importing it, click the radio button next to Link to the data source by creating a linked table.

7. Click OK to launch the Wizard.

8. Select the type of text file – delimited or fixed width.

9. Click Next.

10. If importing a delimited file, select the type of delimiter.

11. If the first row of the text file contains field names, click the First Row Contains Field Names checkbox.

12. Click Next.

13. If importing to a new table, set additional information for each field (data type, indexed, etc.).

14. Click Next.

15. Choose the desired primary key option (add your own, let Access add key or no primary key)

16. Click Finish.

Let’s Try It!

What	Why
1. Click the External Data tab on the Ribbon.	Displays commands for working with external data.
2. Click the Text File command button on the Import & Link command set.	Displays the Get External Data – Text File dialog box.
3. Click the Browse button.	Displays the File Open dialog box from where we navigate to the file we wish to open.
4. Navigate to the Lesson Files folder (if necessary) and then select the Product_List file.	Selects the text file we wish to import.
5. Click Open.	Closes the File Open dialog box.
6. Click the radio button next to Import the source data in a new table in the current database.	This time, we are going to import our data into a new Access table.
7. Click OK.	Launches the Import Text Wizard.
8. Make sure that Delimited is selected as shown below and then click Next.	Selects delimited as the type of text file we are importing and then moves to the next step of the wizard.
9. Select Comma as the type of delimiter and then click the checkbox next to First Row Contains Field Names box as shown below.	Selects comma as the delimiter that separates the fields. As the first row of our text file contains field names (or column headings), we want to check the field names box.
10. Click Next.	Moves to the next step of the wizard.
11. Scroll to the right until the Price field is visible.	Moves to the right until we can see the price field. We are going to change the data type for this field to Currency.
12. Click the word Price to select the column and then choose Currency from the Data Type drop-down list as shown below.	Changes the data type for the Price field to Currency.
13. Click Next.	Moves to the next step of the wizard.
14. Click the Choose my own primary key radio button and then click ProductID field from the drop-down list.	Sets the ProductID field as the primary key field in the table.
15. Click Next.	Moves to the next step of the wizard.
16. Type: tblProducts in the Import to Table box.	Specifies a name for our new table.
17. Click Finish.	Imports the data from the text file to a new table named tblProducts.
18. Click Close.	Closes the message box that informs us that our data was imported.
19. Double-click the tblProducts table and observe the data.	Opens tblProducts in Datasheet view. There should be 128 records in the table.
20. Click the Close button for tblProducts.	Closes tblProducts.

3.5 Selecting and Deleting Records

In this lesson, you will learn how to select and delete records from your table.

D

eleting records from a table is straight-forward — click on the row selector to select the record and then press the Delete key or right-click and select Delete Record from the contextual menu. You can select more than one record by clicking and dragging upwards or downwards over the records you wish to delete. Keep in mind though, that once you delete records, they are gone for good. There is no undo! The only way to get a deleted record back is to retype the information.

SHAPE \* MERGEFORMAT

You can also delete records from the Records command set under the Home tab. Select the row selector and click the Delete button. Make sure that you click the Delete button under the Home tab and not the Datasheet tab as clicking the Delete button under the Datasheet tab will delete the entire field, not just an individual record. XE "Records:selecting in tables" XE "Records:deleting in tables" XE "Delete:records in tables"

To Select and Delete a Record

1. Open the table in Datasheet view.

2. Click the row selector of the record or records you wish to delete.

3. Press the Delete key on the Home tab of the Ribbon

Or

Right-click and select Delete Record from the contextual menu.

Or

Click the Delete button on the Records command set under the Home tab.

4. Click Yes when the message box appears, asking you if you are sure you want to delete the record(s).

Note: Clicking No will cancel the delete records action and the records will not be deleted from the table.

Let’s Try It!

What	Why
1. Double-click tblProducts	Opens tblProducts in Datasheet view.
2. Click on the Row Selector for the Matrix, the 5th record in the table as shown below.	Select the record for the Matrix.
3. Press the Delete key.	Displays a message box warning you that you cannot undo the deletion and asking you if you really wish to delete the records.
4. Click Yes.	Closes the message box and deletes the selected record.

3.6 Sorting Records

In this lesson, you will learn how to sort records in a table.

R

ecords are automatically sorted alphabetically or numerically by the primary key field if there is one designated. If there is no primary key set, then the records appear in the order in which they were entered. To change the order of the records, you can use sorting. Sorting allows you arrange the records in a table in alphabetical or numerical order. You can sort your records in either ascending (A-Z) or descending (Z-A) order. XE "Records:sorting" XE "Sorting:records in tables"

The easiest way to sort records is to click anywhere in the column of the field you wish to sort and then click either the Sort Ascending button or the Sort Descending button on the Home Ribbon. You can also right-click on the column you wish to sort and choose Sort A to Z or Sort Z to A from the contextual menu. Another way to quickly sort records is to click on the drop-down arrow on the column heading and choose the desired sort order from the sort window.

To remove a sort and return to the original order of the records, click the Remove Sort button under the Home tab.

To Sort Records in a Table

1. Open the table in Datasheet View.

2. Click anywhere in the column of the field you wish to sort.

3. To sort multiple adjacent columns, select the first column, hold down your Shift key and select any other columns you wish to sort.

4. To sort records in ascending order, click the Sort Ascending Button (A to Z) under the Home tab.

Or

Right-click and choose Sort A to Z from the contextual menu.

5. To sort records in descending order, click the Sort Descending Button (Z to A) under the Home tab.

Or

Right-click and choose Sort Z to A from the contextual menu.

6. To return the table to its original sort order, click the Remove Sort button under the Home tab.

Let’s Try It!

What	Why
1. Click the Home tab on the Ribbon.	Ensures that the Home tab is active.
2. Click anywhere in the Title column.	Selects the column we wish to sort.
3. Click the Sort Ascending button in the Sort & Filter command set under the Home tab.	Sorts the data in the Title field in ascending order.
4. Click the Sort Descending button in the Sort & Filter command set under the Home tab.	Sorts the data in the Title field in descending order.
5. Click the Column Heading for the Category field.	Selects the first column we wish to sort.
6. Hold down the Shift key and click the Column Heading for the Price field.	Selects both the Category and Price columns.
7. Click the Sort Ascending button on the Sort & Filter command set.	Sorts the columns in ascending order by Category and then by Price.
8. Click the Remove Sort button on the Sort & Filter command set.	Restores the records to their original sort order.

3.7 Finding and Replacing Data

In this lesson, you will learn how to use the Find and Replace feature in Access.

T

here will be times when you need to find certain information in your table. It can become quite challenging to manually look for data, especially once your tables become large. The Find feature allows you to quickly search for information in tables, queries and forms. XE "Data:finding and replacing" XE "Find and Replace:data in tables"

If you know in which field the information resides, this will speed up your search. When searching, you can match either Whole Field (find data that is exactly the same as what you entered in the Find What box), Any Part of Field (find records that contain the data in the Find What box at any position in the field), or Start of Field (locate records that contain the data in the Find What box at the beginning of the field).

To automatically change the data in a field to something else, click the Replace tab and enter the new data in the Replace With box.

Find and Replace Dialog Box

To Find and Replace Data in a Table

1. Open the table in Datasheet View.

2. Click anywhere in the column of the field in which you wish to search for data.

3. Click the Find icon under the Home tab.

Or

Press the Ctrl + F keystroke combination.

4. Type the data you wish to find in the Find what box.

5. Select Whole Field, Any Part of Field or Start of Field from the Match drop-down list.

6. Select either Up, Down, or All in the Search drop-down list.

7. Click Find Next to begin searching.

8. Click Find Next again to search for the next instance of the desired data.

9. To replace data, click the Replace tab.

10. Type the new data in the Replace with text box.

11. Click Replace to replace each instance one at a time. Click Replace All to replace all instances of the data at once.

12. Click Cancel to close the Find dialog box.

Let’s Try It!

What	Why
1. Click anywhere in the Title column.	Selects the column in which we wish to search for data.
2. Click the Find button on the Home tab.	Opens the Find and Replace dialog box.
3. In the Find What box, type: Mad Max	Enter the data that we wish to find.
4. Click the Match drop-down list and then select Whole Field as shown below.	Data must match exactly what we typed in the Find What box.
5. Click Find Next.	Begins the search and stops when the first instance of Mad Max is found.
6. Click Find Next again.	Searches for another instance of Mad Max.
7. Click OK.	Closes the dialog box that informs you that the search item was not found.
8. Click Cancel.	Closes the Find dialog box.
9. Click anywhere in the Price column.	Selects the column in which we wish to search for data.
10. Press the Ctrl + F keystroke combination.	Displays the Find and Replace dialog box.
11. Click the Replace tab.	Switches to Find and Replace options.
12. Type: $19.99 in the Find What box. Don’t forget the currency symbol!	Enters the data that you wish to find.
13. Type: $22.95 in the Replace with box.	Enters the new data that will replace the information in the Find What box.
14. Click the Replace All button.	Replaces all instances of $19.99 with $22.95.
15. Click Yes.	Closes the dialog box that tells you that you will not be able to undo the replace operation.
16. Click Cancel.	Closes the Find and Replace dialog box.

3.8 Filtering Data by Selection

In this lesson, you will learn how to filter data based on selected information.

A

filter is a tool that retrieves only a subset of the records in a table. For instance, if you only wanted to see customers in the state of Wisconsin, you could apply a filter that hides all the records except those where the state field is equal to Wisconsin. To apply such a filter, click in any field whose contents are “Wisconsin” and then click on the Filter by Selection button under the Home tab or right-click and choose the desired filter commands from the contextual menu. The available filter commands will vary depending on the type of data selected. Once the filter is applied, all other records will be hidden from view.

To remove the filter and restore all records, click the Toggle Filter button under the Home tab or right-click and select Clear filter from [Column Name] from the menu. XE "Filter:data by selection" XE "Filter:data to include/exclude selection" XE "Data:filtering by selection"

To Filter Data for a Selection

1. Open the table in Datasheet View.

2. Click in a field whose information you wish to filter.

3. Click the Filter by Selection icon and chose the desired filter command. XE "Filter by Selection icon"

Or

Right-click and choose the desired filter commands from the contextual menu.

To Remove a Filter

1. Click on the Toggle Filter button.

2. Click to Toggle Filter icon again to restore the filter.

Let’s Try It!

What	Why
1. Click on any instance of the word Comedy in the Category column as shown below.	Selects the data for which we wish to filter.
2. Click the Selection button on the Sort & Filter command set and choose “Equals Comedy” from the command list as shown below.	Hides all records except those whose category is “Comedy”
3. Click on any instance of the number $39.99 in the Price column.	Selects the next column we want to add to the filter.
4. Click the Selection button and choose “Less than or Equal to $39.99” from the command list.	Hides all records except those whose category is “Comedy” and whose price is less than or equal to $39.99.
5. Click the Toggle Filter button.	Removes the filter and displays all records.

3.9 Filtering Data by Form

In this lesson, you will learn how to filter data based on selected information from a drop-down list.

F

iltering by Form allows you to choose the data you want the filtered records to contain from a drop-down list of available data. Clicking the Filter by Form button displays a blank record row. Clicking in any of the blank fields displays a drop-down list of available data from which you can choose. This feature is especially helpful if you want to find a specific record or want to filter on several fields in a datasheet. XE "Filtering:data by form" XE "Data:filtering by form"

To Filter by Form

1. Open the table in Datasheet View.

2. Click the Advanced icon under the Home tab and choose Filter by Form from the menu. XE "Filter by Form icon"

3. Click in the field whose information you wish to filter.

4. Click the drop-down arrow and then select the data you want the filtered records to contain.

5. Repeat step 4 for any additional fields you wish to filter.

6. Click the Toggle Filter button.

Let’s Try It!

What	Why
1. Click the Advanced button the Home tab and select Filter by Form from the menu.	Opens a blank record row with the values automatically inserted from our previous filter.
2. Click the Category drop-down arrow and then select Romance.	Selects the Category by which we want to filter.
3. Press Tab.	Moves us to the Price field and displays the drop-down arrow for the Price field.
4. Click the Price drop-down arrow and then select 22.95 from the drop-down list.	Selects the Price amount by which we want to filter.
5. Click the Toggle Filter button.	Only displays records with a Category of Romance AND a price of $22.95.
6. Click the Toggle Filter button again.	Restores the hidden records.

3.10 Using Common Filters

In this lesson, you will learn how use the Common Filters feature in Access.

C

ommon Filters allow you to quickly apply a filter to your data from a contextual menu command. To access these commands, click the Filter button on the Home tab or right-click the field you wish to filter and then choose from the data type filter contextual menu (the available commands will vary depending on the data type of the column). You can also click the drop-down arrow on the column heading to access common filters.

From the Common Filter box, you can filter for specific values by using the check box list to include or exclude fields from the list. To filter for a range of values, choose the contextual filter command (i.e. Date Filters, Text Filters, etc.), choose the range of values for which to filter and then enter your criteria. XE "Filtering:Common Filters" XE "Data:filtering using Common Filters"

To Use Common Filters

1. Open the table in Datasheet View.

2. Click the drop-down arrow on the Column Heading of the field you wish to filter

Or

Click the Filter button under the Home tab.

3. To filter for specific values, check the checkbox next to the records you want to include and uncheck the checkbox next to the records you want to exclude.

4. To filter for a range of values, click the contextual filter command, choose the range by which you wish to filter and enter your values in the dialog box.

Let’s Try It!

What	Why
1. Click anywhere in the Price column and click the Filter button under the Home tab.	Displays the Common Filter Menu.
2. Uncheck the boxes next to $29.99. $39.99, $49.99	Selects the data we wish to exclude.
3. Click OK.	Hides all records except for those with a price of $22.95
4. Click the Toggle Filter button.	Removes the filter.
5. Click the drop-down arrow on the Title column header, click on Text Filters and then choose Begins With from the menu as shown below.	Allows us to filter this column for a range of values. In this case, we are going to only display records which begin with a specific letter.
6. Type F in the Custom Filter dialog box as shown below.	Specifies the criteria for our filter.
7. Click OK.	Only displays records whose title begins with the letter “F”
8. Click the Toggle Filter button on the Ribbon.	Removes the filter.

3.11 Hiding/Unhiding Columns

In this lesson, you will learn how to hide and unhide table columns.

T

here may be instances when you wish to view only certain columns in a table datasheet. Perhaps you need to print table data for only a few relevant columns. Or perhaps you want to hide sensitive columns from those entering data into the tables. To hide columns from view, click on the field name to select it, right-click and select Hide Fields from the contextual menu or click the More button under the Home tab and select Hide Fields from the menu. Saving the design of your table ensures that the columns will remain hidden the next time the table is opened. XE "Columns:hiding/unhiding in tables" XE "Hide:columns in tables" XE "Unhide:columns in tables"

To Hide a Column

1. Open the table in Datasheet View.

2. Click on the field name of the column you wish to hide.

3. Right-click and select Hide Fields from the menu.

Or

Click the More button under the Home tab on the Records command set and choose Hide Fields from the menu.

To Unhide a Column

1. Open the table in Datasheet View.

2. Right-click on any column heading

Or

Click the More button under the Home tab and select Unhide Fields.

3. In the Unhide Columns dialog box, check the column(s) you wish to unhide.

4. Click Close.

Let’s Try It!

What	Why
1. Click the field name of the ProductID field.	Selects the entire column.
2. Right-click and select Hide Fields from the contextual menu.	Hides the ProductID field.
3. Click the More button on the Records group of the Ribbon and choose Unhide Fields.	Dispalys the Unhide Columns dialog box.
4. Click the ProductID box as shown.	Unhides the ProductID column
5. Click Close.	Closes the Unhide Columns dialog box.
6. Click the Close button for tblProducts. Save any changes.	Closes tblProducts and saves the design changes.

3.12 Freezing Columns

In this lesson, you will learn how to freeze a column to the left side of the Datasheet.

I

f you have a large table, you may wish to keep a particular column in view while you scroll to the right. To accomplish this, freeze your columns in place by using the Freeze Fields command. When scrolling to the right, the frozen columns are restrained to the left side of your screen. XE "Columns:freezing in tables" XE "Freezing:columns in tables"

To Freeze a Column in Place

1. Open the table in Datasheet View.

2. Click on the field name of the column you wish to freeze.

3. Right-click and select Freeze Fields from the contextual menu.

Or

Click the More button under the Home tab on the Records command set and choose Freeze Fields from the menu.

To Unfreeze Columns

1. Open the table in Datasheet View.

2. Click on the field name of the column you wish to unfreeze.

3. Right-click and select Unfreeze All Fields from the menu.

Or

Click the More button under the Home tab on the Records command set and choose Unfreeze Fields from the menu.

Let’s Try It!

What	Why
1. Double-click tblCustomers.	Opens tblCustomers in Datasheet View.
2. Click anywhere in the LastName column.	Selects the column we wish to freeze.
3. Click the More button under the Home tab on the Records command set and choose Freeze Fields from the menu.	Freezes the LastName column to the left side of the Datasheet.
4. Scroll to the right as shown below.	Notice that the LastName column is now unscrollable. You can also press the right arrow key or the Tab key to move to the right.
5. Right-click the Column Heading of the frozen column and select Unfreeze All Fields from the contextual menu.	Unfreezes all frozen columns. Notice that although the column is now unfrozen and scrollable, it remains in the same position as when it was frozen.

3.13 Rearranging Columns

In this lesson, you will learn how to rearrange columns in a Datasheet.

I

n Datasheet View, you can change the column order of your table by selecting the column you wish to move and then dragging it to its new location. As you drag, a thick black line appears marking the new location of the column should you release the mouse button. Saving the table design ensures that the column will be in the same position the next time the table is opened. XE "Columns:rearranging in tables"

To Rearrange Columns

1. Open the table in Datasheet View.

2. Click on the field name of the column you wish to move and then release the mouse button.

3. Click on the field name, hold down the mouse button and then drag until the column is in the desired new position.

4. Release the mouse button.

Let’s Try It!

What	Why
1. Click the column heading of the LastName field.	Selects the LastName Field.
2. Click the LastName field name and hold down the mouse button.	Enters drag mode. You are now ready to drag the column to its new location.
3. Drag to the right until the thick black line is to the right of the FirstName field.	Positions the LastName column to the right of the FirstName field.
4. Release the mouse button.	Completes the move process.
5. Click the Close button on the tblCustomers table. Save any changes.	Closes tblCustomers and saves the design changes.

3.14 Displaying Column Totals in a Datasheet

In this lesson, you will learn how to display column totals in a Datasheet.

A

ccess allows you to display column totals right in your datasheet. By clicking the Totals tool in the Records command set under the Home tab, you can quickly sum the values in a Datasheet column. In addition, you can also apply other aggregate functions to the column. Available aggregate functions are Sum, Average, Count, Minimum, Maximum, Standard Deviation and Variance. XE "Column Totals:displaying in Datasheet" XE "Aggregate Functions:using in Datasheet"

To Display Column Totals in a Datasheet

1. Open the table in Datasheet View.

2. Click the Totals button under the Home tab on the Records command set. A new Total row will appear at the bottom of the Datasheet.

3. Click the arrow in the Total row for the column whose totals you wish to display.

4. Choose the desired aggregate function from the list.

5. To remove the Total row, click the Totals button.

Let’s Try It!

What	Why
1. Double-click tblProducts.	Opens tblCustomers in Datasheet View.
2. Click the Totals button on the Ribbon.	Adds a Totals row on the bottom of the datasheet.
3. Scroll down and click in the Total row for the Price column.	Displays the Total drop-down arrow.
4. Click the drop-down arrow and select Sum from the list as shown below.	Sums the values in the column.
5. Click the Totals button on the Ribbon.	Removes the Total row.
6. Click the Close button on the tblProducts table. Save any changes.	Closes tblCustomers and saves the design changes.
7. Press the Alt + F keystroke combination and select Close from the File Options menu in Backstage View.	Closes the Video Sales 3 database. Alt + F is the keyboard shortcut for displaying the File Options menu.

Lesson Summary – Working with Data

· In this lesson, you learned how to edit data in a table datasheet by clicking in the table cell and typing your changes.

· Then, you learned how to import data from Excel by clicking the Excel button under the External Data tab and following the directions of the wizard.

· Next, you learned how to import a delimited text file by clicking the Text File button under the External Data tab and following the directions of the wizard.

· Next you learned how to select records by clicking on the record selector. You also learned how to delete selected records by either pressing the Delete key or clicking the Delete button under the Home tab.

· Next, you learned how to sort records by clicking either the Sort Ascending button or the Sort Descending button.

· Next, you learned how to find and replace data in a table by clicking the Find button under the Home tab.

· Next, you learned how to filter data based on the selected field by clicking in a table cell, clicking the Selection button and choosing the desired filter commands from the contextual menu.

· Next, you learned that by clicking the Filter by Form button, you can filter records by choosing the data you want the filtered records to contain from a drop-down list of available data.

· Next, you learned how to quickly apply a filter to your data from a contextual menu command by clicking the Filter button and then choosing from the data type filter contextual menu.

· Next, you learned how to hide columns from view, by clicking on the field name to select it, right-clicking and selecting Hide Fields from the contextual menu. You learned that you can also hide columns from the More button on the Ribbon.

· Next, you learned that you can freeze columns in place by clicking on the field name of the column you wish to freeze, right-clicking and selecting Freeze Fields from the menu.

· Next, you learned how to rearrange columns by selecting the column you wish to move and then dragging it to its new location.

· Lastly, you learned how to display column totals in your datasheet by clicking the Totals button on the Ribbon and the selecting the desired aggregate function from the list on the Totals row for the column you wish to total.

Lesson 3 Quiz

1. Under which command tab can you find the commands to import an Excel file?

A. Excel

B. Import & Export

C. External Data

D. Database Tools

2. You want to apply bold formatting to only one word in a field that is set to a short text data type. What is the command to accomplish this?

A. Click the Bold button on the Ribbon.

B. You cannot apply formatting to a single field or word if the data type is set to short text.

C. Highlight the word to which you want to apply bold formatting and click the Bold button.

D. Highlight the word to which you want to apply bold formatting and press the Ctrl + B keystroke combination.

3. What is a delimited text file?

4. To delete a single record, you (select all that apply):

A. Click the record selector for the record that you wish to delete and press the Delete key.

B. Select the record you want to delete and click the Delete button under the Datasheet tab.

C. Select the record you want to delete and click the Delete button under the Home tab.

D. Select the record you want to delete and click the Delete button under the Database Tools tab.

5. You have sorted your records in descending order and wish to return the order of the records to the way they were before the sort. How can you accomplish this?

A. Click the Clear All Sorts button.

B. Click the Remove Sort button.

C. Click the Remove Filter button.

D. Click the Column Heading of the sorted row.

6. To search for specific data in your datasheet, click the:

A. Search button

B. Find button

C. Replace button

D. Search Records button

7. Which of the following filters allow you to choose the data you want the filtered records to contain from a drop-down list of available data from a blank record row?

A. Filter by Selection

B. Filter by Form

C. Filter by List

D. Common Filters

8. How can you remove a filter from a datasheet?

A. Right-click and select Remove Filter from the menu.

B. Click the Remove Filter button.

C. Click the Clear All Filters button.

D. Click the Toggle Filter button.

9. You want to display only those records whose value is between 200 and 400. What is the best filter to use?

A. Filter by Selection

B. Filter by Form

C. Common Filters

D. You cannot accomplish this with a filter. You will need to create a query.

10. On what tab would you find the command to remove (delete) a field from a table?

A. Home

B. Database Tools

C. Fields

D. Create

11. When you freeze columns, the columns are restrained to the right side of your screen.

A. True

B. False

12. Which of the following is NOT an aggregate function?

A. Sum

B. Multiply

C. Average

D. Minimum

13. What happens to the position of a column when you unfreeze it?

A. It returns to its original position.

B. It jumps to the right side of the screen.

C. Nothing – it stays in the same position where it was when it was frozen.

D. It repositions itself after the column that is selected.

14. How do you remove the Totals row from a datasheet?

LAB 3 – ON YOUR OWN

1. Open the Lab3 database in the Lesson Files folder.

2. Import the Excel file in the Lesson Files folder named Comics to a new Access table. Make sure to check the First Row Contains Column Headings box. Let Access add a primary key. Name the new table tblComics.

3. Open tblComics in Datasheet view. Change the font type of the data in tblComics to Times New Roman. Changes the font size to 12.

4. Use the Search tool to find the May 1967 issue of: Kid Colt Outlaw. Delete the record from the table.

5. Sort the records in Ascending order by the Year Published field. Which is the oldest comic in the database?

6. Using the Filter by Selection tool, only show comics whose Publisher is Classics Illustrated. Remove the filter.

7. Using the Filter by Form tool, only show comics whose Year Published is 1970 (delete the entry in the Publisher field, if there is one). Remove the Filter.

8. Add a Total field to the table which sums the Amount Paid field.

9. Hide the NoPrintings column and the ID column.

10. Freeze the Title Column.

11. Unhide the ID Column.

12. Save the design changes. Close the database.