Take Note When you type sufficient data for Excel to recognize a series, the fill handle will do the rest. For example, to record daily sales, you might want to have consecutive columns labeled with the days of the week. If you type Monday in the first cell, you can fill in the rest of the days by dragging the fill handle from the Monday cell to complete the series.
Excel recognizes January as the beginning of a natural series and completes the series as far as you take the fill handle. By definition, a natural series is a formatted series of text or numbers that are in a normal sequence such as months, weekdays, numbers, or times. For example, a natural series of numbers could be 1, 2, 3, or , , , or a natural series of text could be Monday, Tuesday, Wednesday, or January, February, March.
For different natural series, see Table With Flash Fill, you can quickly fill a column of data using an example that is based on existing data in adjacent columns. As the following steps show, for example, you can almost instantly cre- ate columns for first and last names if the full name appears in another column.
After you enter the initial item such as the first name in a column and start to type the second item in that col- umn, Excel displays a preview of entries formatted the same way in the rest of the column.
If the presented preview of the data is what you want, just press Enter to fill the column with the entries. OPEN 02 Customers from the data files for this lesson. Notice the customer list in column A, which includes the last name followed by a comma and then the first name.
You want to create separate columns for the first and last names. Select cell B2 in the First Name column. Type Alice and then press Enter. In cell B3, type Ai to begin the next first name, Aidan.
Excel guesses that you want to enter the rest of the first names in column B and displays a preview of the results see Figure Figure Flash Fill showing possible list Type first name of for all First Names first person in B2 Type first two letters of second person in B3 and Excel automatically guesses that you want all first names in column B. Press Enter to accept the suggestion. The remaining first names fill down the column. Notice that Excel also includes the middle initials for those names that include them.
Select cell C2 in the Last Name column. Type Ciccu and then press Enter. In cell C3, type De to begin the next last name, Delaney. Excel guesses that you want to enter the rest of the last names in column C and displays a preview of the results. Working with Microsoft Excel 29 9. The remaining last names fill down the column. These processes are discussed as the exercises in this section continue.
You can also copy specific contents or attri- butes from the cells. For example, you can copy the format only without copying the cell value, or copy the resulting value of a formula without copying the formula itself. You can also copy the value from the original cell but retain the formatting of the destination cell.
Just select the cell or range of cells you want to copy and hold down Ctrl while you point to the border of the selection. When the pointer becomes a copy pointer arrow with a plus , you can drag the cell or range of cells to the new location. As you drag, a scrolling ScreenTip identifies where the selection will be copied if you release the mouse button. In this exercise, you practice copying data with the mouse. Open the 02 Customer Houses file. Select the range AA Press Ctrl and point to the right border of the selected range.
The copy pointer is displayed. Troubleshooting Be sure to hold down the Ctrl key the entire time you are dragging a data series for copying with the mouse, or you will move the series instead of copying it.
With the copy pointer displayed, press and hold down the left mouse button and drag the selection to the right, until HH22 appears in the scrolling ScreenTip next to the selection. Release the mouse button and then release Ctrl. The data in AA22 also appears in HH Moving a Data Series with the Mouse Data can be moved from one location to another within a workbook in much the same way as copying. To move a data series, select the cell or range of cells and point to the border of the se- lection.
When data is moved, it replaces any existing data in the destination cells. In this exercise, you practice moving a data series from one range of cells to another.
USE the 02 Customer Houses workbook from the previous exercise. Select EE Point to the right border of the selected range. The move pointer a white arrow with four smaller black arrows attached is displayed. With the move pointer displayed, hold down the left mouse button and then drag the selection to the right, until II22 appears in the scrolling ScreenTip beside the selected range. Release the mouse button. In your worksheet, the destination cells are empty; therefore, you are not concerned with replacing existing data.
The data previously in EE22 is now in II Drag A1 to H Note that a dialog box warns you about replacing the contents of the destination cells. Click Cancel. Drag E1 to I Your worksheet should look like the one shown in Figure Take Note When you attempt to move a selection to a location that contains data, a caution dialog box opens. Do you want to replace it? You can click OK or cancel the operation. Copying and Pasting Data The Office Clipboard collects and stores up to 24 copied or cut items that are then available to be used in the active workbook, in other workbooks, and in other Microsoft Office programs.
Working with Microsoft Excel 31 You can paste insert selected items from the Clipboard to a new location in the worksheet.
Cut moved data is removed from the worksheet but is still available for you to use in multiple locations. If you copy multiple items and then click Paste, only the last item copied will be pasted.
To access multiple items, you must open the Clipboard pane. In this exercise, you use commands in the Clipboard group and the Clipboard pane to copy and paste cell data. The Clipboard pane opens on the left side of the worksheet.
The most recently copied item is always added at the top of the list in this pane, and it is the item that will be copied when you click Paste or a shortcut command.
Select A1:E22 and then press Delete. Select HI22 and in the Clipboard group, click the Copy button. The border around the selected range becomes a moving border. Select A1 and then click the Paste button. The moving border remains active around HI A copied range does not deactivate until you type new text, issue another command, double-click on another cell, or press Esc.
Select A20 and then click the down arrow on the Paste button. The Paste options menu appears see Figure Figure The Paste options menu 6. Under Paste Values, select the first option. Notice that the values in the range BB31 are no longer formatted. Select HI22 and then press Delete.
Click the Close button in the upper-right corner of the Clipboard pane to close it. Take Note If you point to the Paste options in either the shortcut menu or the Paste options in the Clipboard group, you will be able to preview your changes before actually implementing them. The properties include items that you indirectly change such as file size and last edit date. The workbook properties also include items you directly change such as keywords.
Assigning keywords also called tags to the document properties makes it easier to organize and find documents. You can also add more notes to your file for classification and document management. Assigning Keywords If you work for Fabrikam, Inc. You can then search for and locate all files containing information about sales.
You can assign more than one keyword to a document. Click File. The Backstage view displays current properties on the right side of the window. At the bottom of the right pane, click the Show All Properties link to display additional properties. Click the Categories field and type Revenue. Click the Company field and type Fabrikam, Inc.. Above the Size field, click the Properties drop-down arrow, and then click Advanced Properties.
The Properties dialog box opens see Figure Click the Summary tab in the dialog box to see the properties you entered. Click the Statistics tab to see the date you modified the file. Click OK to close the Properties dialog box. Press Esc to return to the worksheet. Working with Microsoft Excel 33 After a file is saved, the Statistics tab records when the file was accessed and when it was modified. It also identifies the person who last saved the file.
After a workbook is saved, the Properties dialog box title bar displays the workbook name. Knowledge Assessment Multiple Choice Select the best response for the following statements. Which of the following consists of details that describe or identify a file, including the author?
Paste b. Document properties c. Copy d. Range 2. Which command is used to insert a cut or copied selection to a cell or range of cells?
Range 3. If you want to use a workbook in another kind of document, you have the option to save using which of the following?
File format b. Worksheet c. File sheet d. File range 4. Which is a small green square in the lower-right corner of a selected cell or range that you can use to copy one cell to adjacent cells or to create a series? Cell pointer b. Column marker c. Fill handle d. Formula bar 5. Which of the following can you drag or double-click to change the width of a column?
Using the Delete key removes both text and formats from a cell. You can assign keywords so that others can search for your documents online. The formula bar is found at the bottom of the Excel window.
Use the fill handle to create a natural series, such as the months of the year. Workbooks can be saved as web pages, PDF files, and for use in previous versions of Excel. Click cell A1, type Fabrikam, Inc. Beginning in A4, type the following labels and values. If necessary, adjust the column widths to display all of the text in the columns.
LEAVE the workbook open for the next project. Project Setting Document Properties and Assigning Keywords In this project, you will use Document Properties to assign properties to an existing workbook.
Click Properties and then click Advanced Properties. Click the Summary tab. In the Subject field, type Sales and then press Tab. In the Author field, type [your name] and then press Tab four times. In the Keywords field, type , options, priorities. CLOSE the file. Each tab provides related options and settings to help you manage your Excel workbook files.
This is your access to Backstage view. When you click the File tab to access Backstage view, you see the navigation pane on the left side of the window, with commands related to managing files refer to Figure If you want to return to your workbook from Backstage view, click the Return to document button in the upper left corner or press Esc. You can use the Print settings to manipulate workbook elements such as margins, orientation, paper size, and so on.
Many of these commands are also available from the Page Layout tab on the ribbon. Setting the Print Area You can use the Print options in Backstage view to print only a selected portion, or print area, of an Excel workbook. In this exercise, you learn to select an area of a workbook for printing. You print just the list of items in the worksheet.
On the worksheet, click cell A3, hold the mouse button, and then drag to cell A Your selected cell range should be highlighted in gray see Figure Figure Selecting a print area 2. Click the File tab and then click Print in the navigation pane. Under Settings, click the first button, labeled Print Active Sheets the name on the button changes if you make a different selection.
A drop-down menu of options displays for setting the print area. From the menu that appears, click Print Selection.
You have now set the print area. Notice in the Print Preview pane on the right that you can see only the list of items to bring and not the text in rows 1 and 2 see Figure Click the Return to document button. You will not print at this time. Take Note Specifying a print area from the Backstage view does not save the setting with the workbook.
If you want to save the print area for later use, select the range for the print area, and then on the Page Layout tab, click Print Area, and then Set Print Area. Printing Selected Worksheets In this exercise, you learn to access the options for printing individual worksheets in a workbook. You can use these options to print the current worksheet only or to print multiple worksheets. This is a modified version of the potluck workbook you used previously.
In this case, there are three different worksheets for three different departments. Click each of the three worksheet tabs: HR, Operations, and Finance. Notice that the title in C1 shows the department name and there are a different number of items to bring to each potluck depending on the size of the department. Click the HR tab. In the Print Preview pane, the first worksheet displays.
Excel prints only the active sheets by default. Notice that the page number shows 1 out of 1, indicating that only one of the worksheets will print, and it will all fit on one page. Press Esc or click the Return to document button. While the HR worksheet is active, hold down Ctrl and click on the Finance tab. Now both the HR and Finance worksheets are selected. Click the File tab and then click Print. Now in the Print Preview area, the bottom of the screen shows 1 of 2 with the HR worksheet preview.
Click the right arrow to go to the second page and notice that the Finance worksheet previews see Figure Page 1 is the HR worksheet 7.
In the drop-down menu that appears, notice that you can select several printing options for your workbook or worksheet. Click the Return to document button to return to the workbook without printing. CLOSE the workbook without saving.
Printing an Entire Workbook In most scenarios in business, workbooks are composed of multiple worksheets. In this exercise, you use commands in Backstage view to print an entire workbook.
With Excel open from the previous exercise, perform the following steps. Click the File tab and then click Open if it is not selected. Because you just used the 03 Contoso Potluck Depts workbook, it should be at the top of the Recent list in the right pane. Click 03 Contoso Potluck Depts to open it. Notice that the complete workbook does not display in the Print Preview area. This is because you did not save the workbook after you selected multiple sheets in the previous exercise.
Now in the Print Preview area, the bottom of the screen shows 1 of 3. This exercise prepares you to customize such options as page setup, scale, paper selection, and gridlines, all using the commands in Backstage view. Click the File tab and select Print see Figure Notice that the worksheet is small and it might be nice to have lines for people to write in on a printed page. In the Settings area of the Print window, click the Margins drop-down arrow and then click Wide.
The new margins will allow the worksheet to be hole-punched and put in a binder. Click the Scaling drop-down and see the choices below. The scaling options ensure that all columns, rows, or the entire worksheet fit on one page. In this case, you want to make the text larger without changing the font. Click Custom Scaling Options. The Page Setup dialog box opens. Make sure that the Page tab is selected and select Landscape so the page prints horizontally. Click the Sheet tab and in the Print section, select the Gridlines check box.
Click OK to return to Backstage view. Notice that the bottom of the screen still says, 1 of 1, meaning that only one page will print and notice that Print Preview shows larger text with boxes around each cell. Without printing the document, click the Return to document button and then click the HR-P2 worksheet. Change the Settings to print Landscape, the Scaling to Fit Sheet on One Page, and add gridlines based on the previous steps in this section. Print Preview should look like Figure Default settings are pre-set options that determine how Excel will behave when performing an action.
For example, a default printer is the one your documents always print to unless you change the setting. By default, there is one worksheet in a workbook and the font is Calibri, 11 points—but you can change those settings too. Take Note After you customize the Quick Access Toolbar and the ribbon in the following exercises, you will follow steps to return Excel to the default settings.
In this exercise, you customize the Quick Access Toolbar by adding commands for functions you use most frequently in Excel, and by organizing the command but- tons on the toolbar to best suit your working needs and style.
OPEN a blank workbook in Excel. Click the File tab to access Backstage view. In the navigation pane, click the Options tab. In the right pane, the list on the left includes the commands that you can add to the toolbar. The list on the right shows the commands that are currently shown on the toolbar. In the list on the left, scroll down and click Format Painter see Figure , and then click the Add button in the center of the two lists to add the Format Painter to the Quick Access Toolbar. Using the same process, move five more commands you use often to the Quick Access Toolbar.
Notice that your Quick Access Toolbar now includes additional command buttons. Similarly, you can remove any command that you added to the toolbar. At any time, you can reset the toolbar to its default settings.
Click Yes to confirm and then click OK. Customizing the Ribbon As mentioned in Lesson 1, the ribbon is a visual interface that allows you to manipulate items on your worksheet. You can add a new ribbon tab, add a group on any ribbon tab, and add commands within a group.
A group of commands on the default ribbon tabs are related in functionality. For example, on the Home tab, the Font group allows you to change the font, font size, add bold, italic, or underline, or change the color of the cell or font. The Excel Options dialog box also offers selections for customizing the ribbon. You can add and remove commands, and you can change the location of ribbon commands to make accessing those you use most frequently more convenient.
In this exercise, you use the commands in the Excel Options dialog box to create a new tab and command group to contain your frequently used com- mands. Then you will reset the customizations to return to the default ribbon.
Click the Options tab. In the Excel Options dialog box, click Customize Ribbon. The Customize the Ribbon options appear. By default, Popular Commands is selected in the Choose commands from drop-down box. The list of Popular Commands appears in the list below the drop- down box. In the list of Popular Commands, click Format Painter.
Note the Add button in the center of the dialog box is now active. You can use this method to display the current groups available on a ribbon tab. Under the Customize the Ribbon options, click the New Tab button shown in Figure to insert a new blank tab into the Customize the Ribbon list. When you click New Tab, a New Group is automatically created inside the new tab. Click the New Tab Custom list item on the right to select it, and then click the Rename button. Under your new tab, click New Group Custom to select it.
Click the Rename button again. This time, the Rename dialog box allows you to select a symbol see Figure Select the hand symbol. You see the New Group renamed. In the command list on the left, click on a command of your choice, and then click the Add button. The command appears on your new ribbon tab. In the Choose commands from list, select All Commands and then add another command from this list.
In the Choose commands from list, select File Tab and then add another command. Your screen should look similar to Figure Click OK to close the Excel Options dialog box. When you exit, you see your tab named My New Tab on the ribbon.
Click the My New Tab tab. Right-click anywhere in the ribbon and then click Customize the Ribbon. Near the lower-right corner of the Excel Options dialog box, click the Reset button and then click Reset all customizations.
This exercise familiarizes you with where the templates are located and how to select and use them. Selecting a Template from the New Tab Templates allow you to create professional workbooks in a fraction of the time it would take you to develop them from scratch.
Examples of these are budgets, loan models, invoices, calendars, and so on. Click the File tab and then click New. The New window displays a series of featured templates. You already used the Blank workbook template in this book. Scroll down if necessary and then click the Project Tracker icon. Click the Create button. Notice that there are two worksheets in this workbook: Project Tracker with the sample data you can change and Setup that allows you to input a list of categories and employees.
When you are finished looking at this template, click File and then click Close. If prompted, do not save changes. Look for and click the Academic calendar any year template and click the Create button. Click on the year and use the up or down arrow to change to the current year if necessary. Click the month name and select the current month name from the drop- down list. Scroll down the worksheet to see additional months displayed in the template. Notice that the Academic calendar any year template appears as the third item on your list of templates because it was recently used.
Click in the Search for online templates box at the top of the window. Type Budget and then press Enter. Notice that Excel displays a downloaded list of templates as well as a Category list on the right so you can narrow the choices see Figure Take Note You can modify the calendar workbook with your name, company name, and favorite pictures to personalize your calendar and make it your own or use as a gift for others.
Which of the following is where you can save, select a template, change document properties, and close or exit Excel? Backstage b. Print c. Edit d. Windows 2. To change printer, layout, or margin settings, you click the File tab and use which of the following options? Info b. Options c. Print d. Open 3. Which of the following can be customized for quicker access to your most commonly used commands?
Print Preview b. Quick Access Toolbar c. Printer setup d. Workbook 4. When you modify the ribbon, which of the following is created? Which predesigned file already has a significant amount of formatting, text, and other features?
Blank workbook b. Preset file c. Text file d. To access an Excel template, you can click the File tab and then click New. If you have too many columns on a page, the only option to see them all on a printed page is to decrease the column width.
In Excel, you can add your most commonly used commands to the Quick Access Toolbar. You can create a completely new ribbon tab as well as groups on that ribbon. Click the File tab, click New, and then search for the Movie list template.
Add an additional movie to the list. Project Printing a Specific Area of a Worksheet In this project, you will print a selected area in the worksheet and then print a specific worksheet. Select the range D5:L9. OPEN Backstage view. Click Print in the Backstage view navigation pane.
In the Settings section of the Print window, click the Print Active Sheets drop-down arrow, and then click the Print Selection option in the drop-down menu. Click the Print button to print the selected area of the worksheet. From Backstage view, click the Print tab to return to the Settings options, open the Print Selection drop-down menu and then click Print Active Sheets.
Click the Print button to print the active worksheet. For example, you can create basic formulas for addition, subtraction, multiplication, and division using these methods.
However, as you have discovered in previous lessons, the user interface offers tools that make it easier to work with data. In this lesson, you use a few command groups on the Formulas tab to display formulas and name ranges to be used in formulas. Name box Formula bar View and edit named ranges Define Name Create from Selection Show Formulas Figure The Formulas tab in Excel Use Figure as a reference throughout this lesson as you become familiar with some of the com- mand groups on the Formulas tab and use them to work with formulas.
You learn about additional commands on the Formulas tab in the next lesson, which addresses functions. The formula is one of the essential elements of Excel, which enables you to add, subtract, multiply, and divide numbers. When you enter a formula in a cell, the formula is stored internally and the results are displayed in the cell.
You can view the underlying formula in the formula bar when the cell is active, when you double-click the cell to edit it, or by using the Formulas tab. You just entered a formula. Take Note Formulas should be typed without spaces, but if you type spaces, Excel eliminates them when you press Enter.
Notice that the result of the formula displays in the cell, but the formula itself appears in the formula bar see Figure Figure Viewing a formula in the formula bar Results of the formula Formula displays display in the cell in the formula bar 4. Double-click cell A1. The formula appears in both the active cell and the formula bar. You can edit the formula in this mode.
The formula in cell A1 displays. Take Note While you are displaying formulas in the worksheet, you will not see the results of those formulas. Click Show Formulas again to turn off formula display. A formula is an equation that performs calculations, such as addition, subtraction, multiplica- tion, and division, on values in a worksheet.
The resulting composite key is more valid. Click the Save button on the Quick Access Toolbar. You can access these features from the Find and Replace dialog box. The Find and Replace commands in Access work very much like those in Word and other Office applications. You can use the Find command to search for specific text in a table or to move quickly to a particular word or number in the table.
You can use the Replace command to automatically replace a word or number with something else. In the Find and Replace dialog box, type the text or numbers that you want to search for into the Find What box and then click Find Next to locate the record containing the data.
If you want to replace the data, type the new data into the Replace With box and then click Replace or Replace All. Take Note When replacing data, it is usually a good practice to click Replace instead of Replace All so that you can confirm each replacement to make sure that it is correct. Finding and Replacing Data The Find and Replace dialog box searches only one table at a time; it does not search the entire database. The Look In menu allows you to choose to search by field or to search the entire table.
By default, Access searches the field that was selected when you opened the Find and Replace dialog box. If you want to search a different field, select the field while the dialog box is open; you do not have to close it first. In this exercise, you find and replace table data. Select Any Part of Field for the broadest search. When it does, do not clear the check box, or your search probably will not return any results. Type a question mark? OPEN the Customers table. On the Home tab, in the Find group, click the Find button.
The Find and Replace dialog box appears with the Find tab displayed. Click the Replace tab in the Find and Replace dialog box. Type Elm into the Find What box. Type Little Elm into the Replace With box. Click the down arrow beside the Look in menu, and then select Current document if it is not already selected.
Click the down arrow beside the Match menu and then select Any Part of Field if it already is not selected to broaden the search see Figure Figure Find and Replace dialog box 8.
Click the Find Next button. Access searches the table, finds, and selects the word Elm. Click the Replace button. Access replaces Elm with Little Elm. Access finds Elm in the new text that was just inserted. Click Find Next again. Access displays a message saying that no more occurrences of the word have been found. Click Cancel to close the Find and Replace dialog box.
Press the down arrow to remove the selection and allow Access to save the change. Take Note If you want to use the Find and Replace dialog box to search for characters that are used as wildcards, such as a question mark, you must enclose that character in brackets, for example [? Follow this rule when searching for all wildcard characters except exclamation points!
For example, the human resources department of a large company could keep a photo, a resume, and employee evaluation documents with each employee record. These attached files can also be easily detached, if necessary.
The Attachments dialog box allows you to manage the documents attached to records. You cannot share attachments with a database created in these prior versions of Access. Attaching and Detaching Documents Before you can start attaching documents, you must create a field in a table and format it with the Attachment data type.
You can add the field in Datasheet view or in Design view. Access displays a paper clip icon in the header row and in every record in the field along with a number in parentheses indicating the number of attached files in the field. In this exercise, you create a new field and format it with the Attachment data type, and then remove the attachment from your database records. Double-click the record in the Attachments field to display the Attachments dialog box where you can add, remove, open, or save multiple attachments, such as images, documents, and spreadsheets, for a single record.
You can save attached files to your hard disk or network drive so that you can save changes to documents there before saving them to the database.
If the program that was used to create the attached file is installed on your computer, you can open and edit the file using that program. For example, if you open a Word resume that is attached to a record, the Word program starts and you view the document in Word. If you do not have the program that was used to create a file, Access prompts you to choose a program you do have to view the file.
Click the header row of the Due Date field to select it. The More Fields menu appears see Figure Figure More Fields menu 4. Click Attachment under Basic Types. The Attachment field is inserted in the table. Working with Tables and Database Records 45 5. Double-click the first row of the Attachments field. The Attachments dialog box appears. Click the Add button. Navigate to the data files for this lesson and select invoice Click Open. The document appears in the Attachments dialog box.
The number of attachments in the first record changes to 1. Double-click the attachment number in the Attachment field. Click the Open button. The attachment, an invoice document, opens in Word. Click the Close button to close the invoice document and return to Access. Click the Access button on the taskbar, if necessary, to return to Access.
In the Attachments dialog box, click the Remove button and then click OK. The attachment is removed from the record. Take Note Once a field has been set to the Attachment data type, it cannot be converted to another data type. Sorting allows you to order records.
For example, an office contact list that displays employees in alphabetical order by last name would help the user find information for a particular employee quickly. If you wanted to view only the records of employees in a particular department, you could create a filter to display only those records.
You could also hide or freeze certain fields. For example, in a table that has several fields, you can hide or freeze fields to help you concentrate on certain data. Sorting Data within a Table To sort data means to arrange it alphabetically, numerically, or chronologically. Sorting within a table displays all the records in the table in the order that you select. You can easily sort by one or more fields to achieve the order that you want.
Access can sort text, numbers, or dates in ascending or descending order. Ascending order sorts data from beginning to end, such as from A to Z, 1 to 10, and January to December. Descending order sorts data from the end to the beginning, such as from Z to A, 10 to 1, and December to January. In this exercise, you sort data using multiple fields, and then remove the sort.
You can also right-click a selected column and choose a Sort command from the shortcut menu. The available sort commands in the shortcut menu vary depending on the type of data in the column.
You can also sort records on multiple fields. When you are using multiple fields, determine in which order you want them to be sorted. The primary sort field is called the outermost field.
A secondary sort field is called an innermost field. When designating the sort order, however, you select the innermost field first and choose the type of sort you want from the shortcut menu.
Then select the outermost field and then select the type of sort that you want. These sort commands remain with the table until you remove them. This removes the sorting commands from all the fields in the table. In a table with more than one sorted field, you cannot remove just one sort. Click the header row of the Customer ID field to select it. The data is sorted from largest to smallest, as shown in Figure The data is sorted and an arrow is inserted in the header row indicating that the data is displayed in descending sort order.
The sort is removed from the Customer ID field. Select the First Name field, right-click in the field to display the shortcut menu, and then click Sort A to Z. The data in the First Name field is sorted in ascending order. Select the Last Name field. The data in the Last Name field is sorted in ascending order. Working with Tables and Database Records 47 7. The sort is removed from both the First Name and Last Name fields.
If a dialog box appears asking if you want to save changes to the table, click No. Filtering Data within a Table A filter is a set of rules for determining which records will be displayed. When you apply a filter, Access displays only the records that meet your filter criteria; the other records are hidden from view.
Once the filtered records are displayed, you can edit and navigate the records just as you would without a filter applied. Filters remain in effect until you close the object. You can toggle between views, and the filter settings will stay in effect. To make the filter available the next time you open the object, save the object before closing it. In this exercise, you practice creating filters in several different ways.
Select the Product Name field. A menu appears. Point to Text Filters. A second menu appears. Select Contains see Figure Click Contains. Figure Filter menu with Contains selected 5. The Custom Filter dialog box appears. Type Decaf and then click OK. Access filters the database to display only the records containing the word Decaf.
A filter icon is displayed in the header row of the field. In the second record in the Product Name field, double-click the word Decaf to select it. Notice that the records are filtered to show only those that do not contain the word Decaf. Click in the Pounds field of the first record. Click the check boxes to remove the check marks beside Blanks , 30, 35, 40, and Only the check mark beside 25 should remain. Take Note You can also uncheck Select All, and then check Access filters the records to show only those containing the number 25 in the Pounds field.
Click the Toggle Filter button. This is a placeholder character that is part of the input mask field property for this field. This means that when data is entered in this field, it must follow a certain pattern.
The data is filtered to show only those records with content in the Scheduled Order Date field that matches the filter selection. In the seventh row of the Pounds field, select The records are filtered accordingly.
Take Note Only one filter can be applied per column. When you apply a filter to a column that is already filtered, the previous filter is removed and the new filter is applied. Removing a Filter After applying a filter, you may need to return to records not displayed by the filter. The Toggle Filter button lets you toggle between viewing the filtered records and viewing the table without the filter. Note that the purpose of this button changes accordingly—when the records are filtered, the button is used to remove the filter, and when the filter is removed the button is used to apply the filter.
When you are finished using the filter, you can permanently remove it. In this exercise, you permanently remove the filter you previously applied. Select Clear filter from Pounds. For example, it may be helpful to freeze First Name and Last Name fields so you can keep them fixed on the screen and then horizontally scroll and view other pertinent fields, like E-mail or Telephone Number, to get a better view of your data. You can also hide those fields that may distract you from getting a better view of the data.
In this exercise, you practice freezing and unfreezing fields, as well as hiding and unhiding them. Select Freeze Fields from the menu that appears. Notice that the Last Name field moves to the first field position in the table. Click the Restore Down button see Figure on the application window. The Restore Down button now becomes the Maximize button. Figure Restore Down button 50 Lesson 3 5. Notice that the Last Name field stays fixed as the other fields scroll.
Click the More button again and then select Unfreeze All Fields. Press the Right Arrow key several times until the Last Name field scrolls off from view. Notice how the Last Name field moved with the other fields when the Right Arrow key was pressed several times. Take Note Fields can be rearranged in Datasheet view by clicking on the field name headers and dragging them to where you want to move them. Click the Maximize button on the application window. Select the Customer ID field.
Click the More button and then select Hide Fields. Notice the Customer ID field is now hidden from view see Figure Click the More button and then select Unhide Fields.
The Unhide Columns dialog box appears. Notice the check mark is missing from the Customer ID check box, signifying that it is hidden. Deselect the check boxes next to all the other field representations except Last Name and Business Phone and then click the Close button in the Unhide Columns dialog box. Take Note You can save your table so it retains your formatting the next time you open it.
Creating relationships among these tables allows Access to bring that information back together again through objects such as reports and queries so that you can display information from several tables at once. It is much easier to create effective reports and queries when you start out with well-defined table relationships. Defining Table Relationships You define a table relationship in the Relationships window. To create that relationship, you place common fields in tables and define the relationships between the tables.
Common fields used in different tables do not have to have the same names, but they usually do. They must have the same data type, though. In this exercise, you use a table that already has a primary key field to create a relationship with another table.
You can create three types of relationships in Access tables: one-to-one, one-to-many, and many-to-many. In a one-to-one relationship, both tables have a common field with the same data. Each record in the first table can only have one matching record in the second table, and each record in the second table can have only one matching record in the first table. This type of relationship is not common, because information related in this way is usually stored in the same table.
A one-to-many relationship is more common, because each record in the first table can have many records in the second table. For example, in a Customers table and an Orders table, one customer could have many orders.
The Customer ID would be the primary key in the Customers table the one and the foreign key in the Orders table the many. In a third type of relationship, called a many-to-many relationship, many records in the first table can have many records in the second table.
On the Database Tools tab in the Relationships group, click the Relationships button. The Relationships view appears with the Customers table represented. The Show Table dialog box appears see Figure Figure Show Table dialog box 3. Select Order Summary and then click Add. Click Close.
The Customer table and Order Summary table are represented in Relationships view. The Customer ID field represents the common field between the two tables. The Edit Relationships dialog box appears see Figure A relationship line representing the one-to-many table relationship of the Customers and the Order Summary tables is displayed. You just created a one-to-many relationship between these tables using Customer ID, the common field. The one-tomany relationship type signifies that each customer record in the Customers table can have many order records in the Order Summary table.
Modifying Table Relationships A table relationship is represented by the line that connects the tables in the Relationships window. To modify the relationship, you can double-click the line to display the Edit Relationships dialog box or delete the line to delete the relationship.
The Edit Relationships dialog box allows you to change a table relationship. You can change the tables on either side of the relationship or the fields on either side. You can also perform actions like enforcing referential integrity and choosing cascade options.
In this exercise, you delete the relationship you previously created, and then recreate and edit the relationship to enforce referential integrity. Referential integrity is an option that you can select in the Edit Relationships dialog box to prevent orphan records.
An orphan record is a record in one table that references records in another table that no longer exist. For example, when referential integrity is enforced, Access will not permit a Customer ID value as the foreign key in the Order Summary table that does not have a matching Customer ID value as the primary key in the Customers table. In this way, referential integrity ensures your tables contain logically related data.
If an operation that violates referential integrity is performed once this option is selected, Access will display a dialog box with a message stating that referential integrity is being violated and will not permit the operation. You can also choose one or both types of cascade options—cascade update related fields or cascade delete related records—in the Edit Relationships dialog box once referential integrity has been selected.
For example, if the cascade update related fields option is selected, Access will update the Customer ID value in the Order Summary table if the Customer ID value in the Customers table is updated.
This ability to update related fields automatically ensures consistent Customer ID values in the related tables. Similarly, if the cascade delete related records option is selected, Access will delete all Customer ID records from the Order Summary table if the related Customer ID record is deleted from the Customers table; therefore, preventing orphaned records.
When you enforce referential integrity between tables, the line connecting the tables becomes thicker. To remove a table relationship, you must delete the relationship line. You can select the line by pointing to it and clicking it. When the relationship line is selected, it appears thicker. Press the Delete key to delete the line and remove the relationship or right-click the line to display the shortcut menu.
Right-click the center section of the relationship line connecting the two tables. A shortcut menu appears see Figure Select Delete. A message appears asking if you are sure you want to delete the relationship. The line disappears. Select the Customer ID field in the first table. Drag the mouse to the Customer ID field in the second table and release the mouse button.
The Edit Relationships dialog box appears. A line appears, creating the relationship. Double-click the center section of the relationship line. The Edit Relationships dialog box appears again, listing the tables and the Customer ID fields on each side. Viewing and Printing Table Relationships You may want to print a table relationship to save for your records or to discuss with a colleague. The Relationship Report command makes this easy. When you choose to print the relationship report, the Print Preview tab appears with options for viewing and printing the report.
The Page Size group controls margins and page size; the Page Layout group controls page orientation and printing options; the Zoom group controls viewing options; and the Data group controls all aspects of data exporting. After you make any changes to the layout of the report, click the Print button to start printing. After printing the report, you can choose to save it.
In this exercise, you view and print table relationships without saving the relationship report. The report is created and the Print Preview tab appears see Figure Click the Print button.
The Print dialog box appears, allowing you to select the printer you want to use. Click OK to keep the default settings, and then print the report or click Cancel to not print the report. Click the Close button to close the Relationships for Fourth Coffee-final tab.
A message appears asking if you want to save changes to the report. Click No. Foreign key a. Composite key b. A primary key from one table that is used in another table 3. Referential integrity c. Filter d. The secondary sort field in a multifield sort 5. Innermost field e. You can use the Navigation buttons to search for data in a table. After you delete a record, you can click the Undo button to bring it back.
The Find and Replace dialog box searches all the tables in a database at one time. The outermost field is the primary sort field in a multifield sort. In a one-to-many relationship, each record in the first table can have many records in the second table.
Part of your job is helping your supervisor organize a charity event. Use an Access table to create a contacts list that your supervisor will use to make calls to local businesses requesting sponsorships and donations for the event. OPEN the Contacts table. Enter the records shown in the following table: Do not type the hyphens when entering the Business Phone data.
Click the bottom half of the View button and then click Design View. Select the ID row. Save the design of the table, and then return to Datasheet view. The Find and Replace dialog box appears. Type into the Find What box. Click the Replace tab and then type into the Replace With box. Click Find Next and then click Replace. Select the Lucerne Publishing record.
On the Home tab, in the Records group, click the Delete button. Click Yes to delete the record. CLOSE the database. Children from the classes may purchase holiday gifts for their angels. In this project, you are an office assistant at the school and you are working with the Angel Project staff to organize information about each angel.
OPEN the List table. Select the Gender field. Select the Age field. In the Gender field, select the M in the first record. Select the Wants field. Select Text Filters from the menu, select Contains from the next menu, type Bike in the Custom Filter dialog box, and then press Enter. Open the Contact Information table. Change to Design view. Remove the primary key from the Home Phone field and define the ID field as the primary key.
SAVE the design and return to Datasheet view. Select the ID field and sort it in ascending order. On the Database Tools tab, in the Relationships group, click the Relationships button. Rename tables. Delete database objects.
Using input masks. Set default values. Change field captions. Add validation rules to fields. Add fields to tables. Delete fields. You can make many changes to a table—or other database object—using its property sheet. You can also rename or delete a table, but keep in mind that such a change could possibly break the functionality of the database, because in a relational database the various components work together. Other table properties are more advanced and used less often.
In this exercise, you modify the description property for a table. Sign on, if necessary, and start Access. We stop indicating it as a separate step from this point on but always click Enable Content when prompted.
In the Navigation Pane, double-click Events to open that table. On the Home tab, in the Views group, click the bottom half of the View button and then click Design View from the menu that appears. The Property Sheet pane appears on the right of the Access window see Figure Place the insertion point in the property box for Description. Type Most popular events for Click the Close button on the Property Sheet pane to close it. Click the File tab and then click Save to save the design changes you have made to the table.
Click the Close button to close the table. Table lists the available table properties on the Property Sheet and what they control. Subdatasheet Height Specify whether to expand to show all available subdatasheet rows default when opened or to set the height of the subdatasheet window to show when opened.
Orientation Set the view orientation, according to whether your language is read left-to-right or right-to-left. Description Provide a description of the table. Default View Set Datasheet as the default view when you open the table. Validation Rule Supply an expression that must be true for you to add a record or change a record. Validation Text Enter text that appears when a record violates the Validation Rule expression.
Filter Define criteria to display only matching rows in Datasheet view. Order By Select one or more fields to specify the default sort order of rows in Datasheet view. Subdatasheet Name Specify whether a subdatasheet should appear in Datasheet view, and, if so, which table or query should supply the rows in the subdatasheet. Link Master Fields List the primary key field s in this table that match the child fields for the subdatasheet. Filter On Load Automatically apply the filter criteria in the Filter property by setting to Yes when the table is opened in Datasheet view.
Renaming a Table To rename a table or other database object, you must first close it. In the Navigation Pane, locate and right-click the object that you want to rename and then click Rename on the shortcut menu that appears. Or, select the table in the Navigation Pane, press F2, type a new name, and then press Enter. Think carefully before you rename a table. If existing database objects, such as queries or reports, use data from that table, the name modification might break the functionality of the database.
In this exercise, you create a new table and then rename it using the shortcut menu. USE the Events-final database that is still open from the previous exercise. On the Create tab, in the Templates group, click the Application Parts button and then click Comments to create a new table. In the Create Relationship dialog box that appears, select There is no relationship and then click Create.
Close the dialog box. Modifying Tables and Fields 61 4. Right-click Comments in the Navigation Pane to display the shortcut menu. Click Rename. The table name is now selected for renaming see Figure Figure Table name selected for renaming Type new table name 7. Type Event Comments and then press Enter. The table has been renamed. Deleting a Table Deleting an entire table is not a complex process; however, remember that when you delete an entire table you might break the functionality of your database.
Although you will be asked to confirm the deletion of a table, you can always undo the action. In this exercise, you delete a table. Open Preview See a Problem? Details if other :. Thanks for telling us about the problem. Return to Book Page. The Microsoft Official Academic Course MOAC textbook for Project is designed to help develop and reinforce common workforce skills within today's competitive job markets. With this textbook students learn to establish and navigate through project resources like establishing and adjusting resource pay rates and working times.
Task assignments include assigning wo The Microsoft Official Academic Course MOAC textbook for Project is designed to help develop and reinforce common workforce skills within today's competitive job markets. Task assignments include assigning work resources and assignments to tasks; allowing certain actions to tag and change Project's scheduling behaviors.
Scheduling refinements and formatting allows students to understand the different task types and the effects of the work formulas. Students will be able to understand how to utilize the task information dialog box to change a task type. This edition also covers project reporting, integrating Microsoft Project with other programs, and managing multiple projects at once. Skills mastery of Project can help students with casework and differentiate job hunters in today's competitive job market.
Get A Copy. Kindle Edition. More Details All Editions Add a New Edition. Friend Reviews. To see what your friends thought of this book, please sign up. To ask other readers questions about Microsoft Project , please sign up.
DEFAULT
DEFAULT
DEFAULT
DEFAULT
90 Best Microsoft Project Books of All Time - BookAuthority - Account Options
Goodreads helps you keep track of books you want to read. Want to Read saving…. Want to Read Currently Reading Read. Project Other editions. Error rating book.
Refresh and try again. Open Preview See a Problem? Details if other :. Thanks for telling us about the problem. Return to Book Page. Get A Copy. More Details All Editions Add a New Edition. Friend Reviews. To see what your friends thought of this book, please sign up.
To ask other readers questions about Project , please sign up. Lists with This Book. This book is not yet featured on Listopia. Add this book to your favorite list ». Community Reviews. Showing Average rating 0. Rating details. All Languages. More filters. Sort order.
Start your review of Project There are no discussion topics on this book yet. Be the first to start one ». Need another excuse to treat yourself to a new book this week? We've got you covered with the buzziest new releases of the day.
To create our Read more Trivia About Project No trivia or quizzes yet. Add some now ». Welcome back.
Just a moment while we sign you in to your Goodreads account.
DEFAULT
DEFAULT
- See a Problem?
MOAC Materials for MOS Testing - Outlook I'm needing the MOAC e-book (Imagine Academy) for MOS certification test in Outlook All I see is the Outlook e-book. Microsoft Project by MOAC (Microsoft Official Academic Course). Wiley's publishing vision for the Microsoft Official Academic Course series is to provide stu- dance of projects, exercises, cases, and assessments. objects in other formats such as the Adobe Portable Document Format (PDF) or XML. III. Preface. Welcome to the Microsoft Official Academic / May 10, · The Microsoft Official Academic Course (MOAC) textbook for Project is designed to help develop and reinforce common workforce skills within today's competitive job markets. With this textbook students learn to establish and navigate through project resources like establishing and adjusting resource pay rates and working times. Task assignments Author: Microsoft Official Academic Course.
DEFAULT
DEFAULT
5 comment