Looking for:
Microsoft official academic course microsoft access 2016 free download |
Microsoft official academic course microsoft access 2016 free download.(PDF) Microsoft Official Academic Course | 治 王 -
No current courses available for this certification. Languages: en zh-cn zh-tw nl es fr ja ko. Retirement date:. They understand basic database design principles and are able to complete tasks independently.
They know and demonstrate the correct application of the principle features of Access , and they demonstrate the ability to create and maintain basic Access database objects, including tables, relationships, data entry forms, multi-level reports, and multi-table queries. Price based on the country or region in which the exam is proctored. All objectives of the exam are covered in depth so you'll be ready for any question on the exam. Get help through Microsoft Certification support forums.
A forum moderator will respond in one business day, Monday-Friday. Celebrate your accomplishment with your network. Pricing is subject to change without notice. Pricing does not include applicable taxes. Please confirm exact pricing with the exam provider before registering to take an exam. After the retirement date, please refer to the related certification for exam requirements. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Table of contents Exit focus mode.
Table of contents. Certification details Demonstrate that you have the skills needed to get the most out of Access by earning a Microsoft Office Specialist MOS certification. To change the view, on the Home tab, click the down arrow on the View button and then choose a view from the menu. When you change views, the commands available on the Ribbon change context to match the tasks you will be performing in that view. USE the Student Information database you used in the previous exercise.
The Students table should be displayed in the Access work area. On the Home tab, in the Views group, click the down arrow on the View button. Click Design View. The table is displayed in Design View, as shown in Figure Notice that the Design tab is now displayed on the Ribbon.
On the Design tab, in the Views group, click the down arrow on the View button and then click Datasheet View. On the Ribbon, in the Table Tools group, click the Fields tab to display the contextual commands for that view. Each tab provides commands that are relevant to the kind of task you are performing. Many of the tools and commands for working with Access are accessible through the Access Ribbon.
In Database Essentials 11 addition to the Ribbon, Access also offers tools and commands on the File menu also known as Backstage view , a Quick Access toolbar, and a status bar refer to Figure In this section, you explore the Ribbon, which displays groups of common commands arranged by tabs. Using the Ribbon The Ribbon is located across the top of the screen and contains tabs and groups of commands. The Ribbon is contextual, which means it offers you commands related to the object that you are working on or the task that you are performing.
Some groups have a dialog box launcher, which is a small arrow in the lower-right corner of the group that you click to launch a dialog box or task pane that displays additional options or information.
Some commands on the Ribbon have small arrows pointing down. These arrows indicate that a menu is available that lists more options from which you can choose. In the next exercise, you will practice using the Ribbon. Click the Home tab to make it active. As shown in Figure , the Ribbon is divided into groups of commands. Notice the dialog box launcher in the lower-right corner of the Clipboard group. Active tab Dialog box launcher Figure The Ribbon and dialog box launcher 2.
Click the Create tab to make it the active tab. Notice that the groups of commands change. Click External Data and then click Database Tools to see the commands available on those tabs. Click the Home tab. Click the dialog box launcher in the lower-right corner of the Text Formatting group. The Datasheet Formatting dialog box appears, as shown in Figure Figure The Datasheet Formatting dialog box 12 Lesson 1 7.
Click Cancel to close the dialog box. Double-click the Home tab. Notice the groups are hidden to give you more screen space to work with your database. Double-click Home again to display the groups. Click File and then click Close to close the database. Access appears with no database open. Troubleshooting If you click the Close button the X in the upper-right corner , Access closes and you will have to reopen it for the next exercise.
The Backstage view Figure displays when you click the File tab from an open database and contains a menu of options and commands that allows you to access various screens to perform common tasks with your database files—such as creating a new database, opening, saving, printing, closing, and changing user account settings and options. You can click several options and commands to view related screens containing various options and commands used to create and manage your database.
The Back button returns you to the Access database workspace. Use this option to view the current database file path and view and edit da- tabase properties. Compact and repair the database and encrypt the database with a password to restrict access. You can save the database to a document management server for sharing or you can package the database and apply a digital signature. Click the File tab. Backstage view opens. Click Open and then navigate to the StudentData database from the data files for this lesson.
Click File again. Backstage view now displays a menu of options down the left side of the window and information about the currently opened database. Click the New option to view the options and commands available. Click the Save As option to view more options and commands.
Click Yes in the dialog box that appears informing you that the objects will be closed. Select the location to save the file and then click Save. The new database file opens automatically. Select File again to re-open Backstage view. Click the Print option to view more options and commands. Click the Account option to view the options and commands shown in Figure Click the Options option to view more options and commands.
Click the Cancel button to close the Access Options dialog box and return to the Access workspace. When planning a database, the first step is to consider the purpose of your database. You need to design the database so that it accommodates all your data-processing and reporting needs. You should gather and organize all the information that you want to include, starting with any existing forms or lists, and think about the reports and mailings you might want to create using the data.
Once you have decided how the information will be used, the next step is to categorize the information by dividing it into subjects such as Products or Orders, which become the tables in your database. Each table should only contain information that relates to that subject. If you find yourself adding extra information, create a new table.
In a database table, data is stored in rows and columns—similar in appearance to a spreadsheet. Each row in a table is called a record. Each column in a table is called a field. Once you have decided to create a Student List table, you need to determine what information you want to store in the table—such as age, birthdate, or tuition.
Organize each piece of information into the smallest useful part—for example, use First Name and Last Name instead of just Name if you want to sort, search, calculate, or report using either a first name or a last name or both. These pieces of information will eventually become your fields columns , and each record row will then contain complete information about each student. For each table, you will choose a primary key. A primary key is a column that uniquely identifies each row, such as Student ID Number.
Database Essentials 15 Reviewing Database Fields Reviewing preexisting database fields can give you an idea of the type of information that you can store in a database. In this exercise, you open a database that is further along in the process of being developed and view the fields in a form to see what a more advanced database looks like.
USE the StudentData-final database that is still open from the previous exercise. Click the Guardian Information tab and then click the Emergency Information tab. Each field on each tab is an example of the type of information that could be contained in a database table. Click Close to close the Student Details dialog box. Defining and Modifying Data Types for Fields When designing the database, you set a data type for each field column that you create to match the information it will store.
When defining table fields, it is important to define them as specifically as possible. For example, if you are using a number, you should determine whether you need to use the Currency data type, the Calculated data type, or the Number data type. Or, if you need to store large amounts of text, you may need to use the Long Text data type up to 64, characters instead of the Short Text data type characters.
Sometimes you may also need to modify data types for preexisting fields. Access provides 11 data types, each with its own purpose. Table describes the types of data that each field can store. The most common data type for fields. Can store up to characters of text, and numbers or a combination of both. Stores large amounts of text—up to 1 gigabyte GB —but only the first 64, characters of text, and numbers or a combination of both will be visible on the screen.
Number Age: 19 Grade: Tables often contain an AutoNumber field used as the primary key. Access uses 1 for all Yes values and 0 for all No values. Attachment Any supported type of file You can attach images, spreadsheet files, documents, charts, and other types of supported files up to 2 GB per record to the records in your database, much like you attach files to email messages. Calculated FullName: John Derenzo Stores an expression based on two or more fields within the same table.
You will learn more about table Design View in Lesson 9. When you create a new field in a table and then enter data in it, Access automatically tries to detect the appropriate data type for the new column. In this exercise, you practice reviewing and modifying data types. Click the Date of Birth field header. On the Ribbon, click the Fields tab.
In the Format box, click the down arrow to display the menu of formatting options for that type, as shown in Figure Click the Last Name header. Notice that the Data Type is Short Text and that no formatting options are available for that data type.
Scroll to the right and then click the Address header. In the Data Type box, click the down arrow and then click Short Text to change the data type. Take Note Be aware that changing a data type might cut off some or all of the data in a field; in some cases, it might remove the data entirely. Scroll to the far right and then click the Click to Add column header. Rename the field by typing Additional Contact Info on File?.
Click outside the menu to close it. Take Note The Number data type should be used only if the numbers will be used in mathematical calculations. For numbers such as phone numbers, use the Short Text data type.
Defining Database Tables Tables are the most basic organizational element of a database. In this exercise, you view a visual representation of the relationship between two tables.
In a simple database, you might have only one table. Most databases, however, will have more than just one table. The tables you include in a database will be based on the data available.
For example, a database of students might have a table for contact information, a table for grades, and a table for tuition and fees. In database applications like Access, you can create a relational database.
A relational database stores information in separate tables and these tables are connected or linked by a defined relationship that ties the data together. On the Database Tools tab, in the Relationships group, click Relationships to display a visual representation of the relationship between the Students and Guardians tables, as shown in Figure Database Essentials 19 Figure A relationship between tables 2.
An important principle to consider when planning a database is to try to record each piece of information only once. Duplicate information, or redundant data, wastes space and increases the likelihood of errors. Relationships among database tables help ensure consistency and reduce repetitive data entry.
As you create each table, keep in mind how the data in the tables are related to each other. Enter test data and then add fields to tables or create new tables as necessary to refine the database.
The last step is to apply data normalization rules to see if your tables are structured correctly and make adjustments as needed. Normalization is the process of applying rules to your database design to ensure that you have divided your information items into the appropriate tables.
Database design principles include standards and guidelines that can be used to determine if your database is structured correctly. These are referred to as normal forms. There are five normal forms, but typically only the first three are applied, because that is usually all that is required. Create new tables for data that applies to more than one record in a table and add a related field to the table. Data can be brought into an Access database in a number of ways, including linking and importing.
When defining tables, you have to decide whether data should be linked to or imported from external sources. When you import data, Access creates a copy of the data or objects in the destination database without altering the source.
Linking lets you connect to data from another source without importing it, so that you can view and modify the latest data in both the source and destination databases without creating and maintaining two copies of the same data thereby reducing redundant data. Any changes you make to the data in the source are reflected in the linked table in the destination database, and vice versa.
You find out more about normalizing tables in Lesson 9. Column 1 Column 2 1. Record a. Row in a database table 2. Field b. Database object that asks a table a question 3. Query c. Form d. Column in a database table 5. Data type e. Any list you make for a specific purpose—even a grocery list—can be considered a simple database. By default, the Navigation Pane appears on the right side of the Access screen each time you create or open a database. Forms, queries, and reports are examples of database objects.
An important principle to consider when planning a database is to try to record each piece of information as many times as possible for easy access. In this project, you will use the Navigation Pane to organize your task list. Click the Contacts group header in the Navigation Pane to display those database objects. Click the Supporting Objects group header to display those database objects.
In the Supporting Objects group, double-click Tasks to open that table. In the Tasks group, double-click Tasks by Assigned To to open that report and view data taken from the Tasks table.
In the past, all data has been kept on paper, but you decide it would be more efficient to create a database. In this project, you will create a database and select data types. Click File, click New, and then double-click Blank desktop database. In the Blank desktop database screen that appears in the center of the screen, type Runners-final in the File Name box.
Click Create. Click Click to Add, select Number, and then type Age. Rename Table 1 to Runners. Click OK. Create new databases. Create a database by using import objects or data from other sources. Create new tables. This is the default view after starting Access. You can create a database using one of the many available templates ready-to-use databases that contain all of the tables, queries, forms, and reports needed for performing specific tasks or by creating a new blank database.
Using a Template to Create a Database Access offers a variety of templates to help get you started. Some templates are immediately available for your use because they are built into Access whereas others can be easily downloaded from the Internet. There are built-in and online templates are available that you can use to track issues, manage contacts, and keep a record of expenses.
Some templates contain a few sample records to help demonstrate their use. You can use templates as-is or you can customize them to better suit your needs. In this exercise, you use one of the many available templates to create a database. On the top of the startup screen window, in the Search for online templates search box, type personal, and then press Enter.
In the list of Personal templates that appears in the middle of the startup screen results pane, click Home inventory. A preview screen of the selected template appears in the center of the startup screen, as shown in Figure Close the Home inventory template preview screen.
Information about selected template displayed Figure The Home inventory template preview screen 3. In the search box, type assets and then press Enter.
In the Assets template results list, click the Updated: Asset tracking database. The Updated: Asset tracking template preview screen appears. In the Desktop asset tracking template preview screen, click in the File Name box and then replace the default file by typing Assets, as shown in Figure Click the folder icon to the right of the File Name box.
The File New Database dialog box appears. Navigate to the location where you want to save the file and then click OK. Take Note You should save your files in a separate directory from where your data files are stored. Click the Create button at the bottom of the preview pane. The preview pane indicates that the template is being downloaded. When the download is complete, the preview pane closes.
Access creates and then opens the Assets database, as shown in Figure Because this database was downloaded from Office.
Getting Started and Access Help windows may appear, which contain helpful videos and links about using the Asset tracking database. Press Enter. When you create a new blank database, Access opens a database that contains a table where you can enter data, but it creates no other database objects.
In this exercise, you create a new blank database. Click the File tab to open the New screen in Backstage view. Take Note The New screen appears in the Backstage view after you click on the File tab when no database is open. When a database is open, the File tab takes you to the Info screen in the Backstage view. In the templates section of the New screen, click the Blank desktop database icon. A Blank desktop database screen appears in the center of the screen, as shown in Figure In the File Name box, type BlankDatabase.
If you want to save the file in a location other than the one shown beneath the File Name box, click the folder icon to the right of the File Name box and browse to a different location.
Click Create button create the blank database. Access creates the database and then opens an empty table named Table1 in Datasheet view. Take Note At this point, you could import data from other sources, such as an Excel spreadsheet or another Access database, to populate this database. You simply create your new database as you just did, and then use the import commands on the External Data tab as discussed in Lesson With the insertion point placed in the first empty cell of your new, blank database, you can begin typing to add data.
Entering data in Datasheet view is very similar to entering data into an Excel worksheet, except that data must be entered in related rows and columns, starting at the upper-left corner of the datasheet. The table is structured through rows and columns, which become meaningful as you enter appropriate data. You do not need to format your data by including blank rows or columns, as you might do in an Excel worksheet, because that just wastes space in your table.
The table merely contains your data. All visual presentation of that data will be done in the forms and reports that you design later. Take Note A database has to be saved first before any objects like tables, queries, forms or reports can be added to it. For example, you cannot create a table first and then create the database to hold it.
Application Parts were introduced in Access and consist of predefined templates that you can add to an existing database to help extend its functionality. Another way to create a table is to copy the structure of an existing table and then paste it into the database.
You can copy a database object and paste it into the same database or into a different database that is open in another instance of Access. Application parts vary from a single table to a collection of database objects like tables, forms, and reports.
The Blank Forms category contains a collection of form parts that allows you to add predefined forms to a database. The Quick Start category of these templates contains a collection of predefined objects arranged by parts for tracking things such as comments, contacts, and issues.
In this exercise, you will quickly create a table using the Application Parts gallery and Quick Start. USE the BlankDatabase database that is still open from the previous exercise. On the Create tab in the Templates group, click the Application Parts button to display the gallery shown in Figure Figure The Application Parts gallery 2. In the Quick Start section of the gallery, click Comments. If you are prompted to close all open objects before instantiating this application part, click Yes in the dialog box that appears.
Notice the Comments table appears as a new object in the Navigation Pane. Click the Enable Content button on the Message Bar to trust the database if necessary. In the Navigation Pane, double-click Comments to display the newly created table with fields for comments. In the Create Relationship dialog box that appears, select the There is no relationship option button and then click Create. A new table is created along with supporting forms and report objects, as shown in Figure Creating a Table from Another Table Another way to create a table is to copy the structure of an existing table using the Copy and Paste commands.
In this exercise, you copy the structure of an existing table to create a new table. On the Navigation Pane, right-click the Comments table database object and then select Copy from the shortcut menu. Right-click in a blank area of the Navigation Pane and select Paste from the shortcut menu. The Paste Table As dialog box appears, as shown in Figure The new table appears at the end of the list of database table objects in the Navigation Pane.
Double-click Copy Of Comments to open the new table. Notice that the structure of the new table is the same as the table from which it was copied, as shown in Figure New copied table Duplicated structure Figure New table copied from existing table 6.
Access also provides you with a third option that appends data to an existing table. As you learned in Lesson 1, a relational database stores information in separate tables that are connected or linked by a defined relationship that ties the data together. When you add a new table to an existing database, that new table stands alone until you relate it to your existing tables.
For example, say you need to track orders placed by a distributor. To do that, you add a table named Distributor Contacts to a sales database. To take advantage of the power that a relational database can provide—to search for the orders placed by a given contact, for example— you must create a relationship between the new table and any tables that contain the order data.
But you will need to save the design of a table, or any other database object, after it is created. Additionally, you can use the Save Object As command in the Backstage view to create a duplicate of a database object like a table, query, or report by specifying an alternate name. Lastly, you can even save some objects as other compatible object types. For example, you can save a table as a new report which will, by default, include all table fields. Saving a Table When you save a new table for the first time, give it a name that describes the information it contains.
You can use up to 64 characters letters or numbers , including spaces. In this exercise, you save a database table, and then use the Save Object As command to create a duplicate of the same table. From the File tab, click the New command and then click the Blank desktop database icon; a Blank desktop database screen appears in the center of the screen.
If not, replace the default name by typing Database1. If you want to save the file in a location other than the one shown beneath the File Name box, click the folder icon and browse to a different location. Click the Create button. A new blank database appears with the default table labeled Table1 displayed. Click to place the insertion point in the cell under the Click to Add field and then type Sample Data. Right-click on the Table1 tab to display the shortcut menu and then click Save.
The Save As dialog box appears. In the Table Name box, type Sample Table. Click OK to close the dialog box and return to the table, which now is labeled Sample Table. Click the File tab to display the Backstage view. Click Save As to display the Save As screen. Click the Save As button. The Backup of Sample Table should now be open. Notice the table contains the same row you created in the Sample Table table. Multiple Choice Select the best response for the following statements.
In Access, a template is which of the following? A database to manage contacts b. Where a database is stored c. Two tables linked together d. A ready-to-use database 2. When you create a new blank desktop database, Access opens a database that contains which of the following? One of each type of database object b. A table c. Sample data d. A template 3. The Templates group commands are located on which of the following tabs? Home b. Create c. Database Tools d.
Datasheet 4. To copy a table, you must first select it in which of the following? The Clipboard b. Microsoft Office Online c. The Navigation Pane d. Datasheet view 5. When you paste a table, which of the following dialog boxes is displayed? Table Structure b. Copy Table c. Paste Data d. Paste Table As Creating Database Tables 33 Projects Project Creating a Database In this project, you want to use Access to store, organize, and manage the contact information for the wholesale coffee suppliers used by Fourth Coffee, where you work as a buyer for the 15 stores in the northeast region.
Use a template to create a database for the contacts. In the Search for online templates box, type Call tracker to find Call tracker database template and press Enter. Select the Call tracker database template. Type Call tracker in the File Name box. If necessary, click the folder icon and choose a different location for the file. Click Create to create and open the database. Click Enable Content, if necessary. Click the Supporting Objects header in the Navigation Pane to display the database objects in that group.
Right-click the Customers table under the Customers header to display the menu and click Copy. Right-click in the white area of the Navigation Pane and click Paste. Select the Structure Only option button the table contains no record data so choosing Structure Only will have the same effect as choosing Structure and Data.
Select the Customers Structure object and drag it up to the Customers area to move it from Unassigned Objects to Customers. Changes are saved automatically. You decide to create a database to store the necessary information as well as add some tables to the database.
In Backstage view, on the New tab or on the Startup screen, if Access is initially launched , click the Blank desktop database icon. In the Blank desktop database screen that appears in the center of the screen, type Restaurants in the File Name box. Right-click the Table1 tab and click Save.
In the Save As dialog box, type Locations. Click the Create tab, open the Applications Parts gallery, and then click Comments. Click Yes to close all open Objects.
In the Create Relationship dialog box that appears, click the Cancel button. Click Enable Content. Rename the Comments table to Uptown Comments. Copy the structure of the Uptown Comments table to create a new table. Name the new table Downtown Comments. EXIT Access. Update records. Delete records. Filter records. Hide fields in tables. Set foreign keys. Create and modify relationships. View relationships. When a table contains many records and fields, it is important to be able to navigate among them.
Navigating Using the Keyboard Access users who prefer using the keyboard to navigate records can press keys and key combinations to move among records in Datasheet view.
In Datasheet view, you can navigate among records using the up, down, left, and right arrow keys to move to the field you want. You can also use the Tab key to move from field to field in a record and from the last field in a record to the first field of the next record.
If you prefer to use the mouse, you can move among records by clicking the navigation buttons, which you will do in a later exercise. However, in this exercise, you use the keyboard to navigate among records. Table lists keys and key combinations for moving among records.
Click the File tab and then click the Save As option on the left side of the Backstage view. Click the Save As command. Type Fourth Coffee-final in the File name box. Find the location where you will save the solution files for this lesson and then click Save.
Notice that the first cell of the first record is selected. Press the Down Arrow key to move down to the next row. Notice that the first cell is selected. Press the Right Arrow key to move to the Product Name field. Press the Tab key to move to the next cell.
Press the Tab key to move to the next row. Your ability to click these may differ based on the number of records in the database you have open. Type a record number into the Current Record box and then press Enter to go to that record. Type data into the Search box to find a match in the table. The Filter Indicator shows whether a filter has been applied to the table, which will be covered later in this lesson.
Using Navigation Buttons Access users who prefer to use the mouse can use the navigation buttons at the bottom of Datasheet view to move among records.
In this exercise, you use these buttons to navigate among records. USE the Fourth Coffee-final database that is still open from the previous exercise. Click the First record button. The selection moves to the first record. Click the Next record button. The selection moves to the next record. Select and then delete the number 2 in the Current Record box. Type 5 and then press Enter. The selection moves to the fifth record. Click the Search box to position the insertion point.
Type sunrise into the Search box. Notice that the selection moves to the first occurrence of the word Sunrise. The selection moves to the next occurrence of the word Sunrise. Click the New blank record button. The insertion point moves to the first column and last row of the table.
Record Shortcut menu Records group Figure Records Group, Record Selector Box, and Record Shortcut Menu Record Selector box New record Use the commands in the Records group and the Record shortcut menu, as well as the Record Selector box a blank square to the left of a record , to assist you in entering record data and inserting and deleting records.
You can easily enter data by positioning the insertion point in the table cell where you want to add data and begin typing. To insert a new record, select any record in the table and then click the New button on the Home tab in the Records group. You can also click the Record Selector box, right-click the selected record, and then select New Record from the shortcut menu. A new record is added to the end of the table. Select existing data to edit or delete it.
Entering, Editing, and Deleting Records After you enter data and move to a new field, Access automatically saves the data in the table. Each field in a table is formatted with a specific data type, so you must enter that kind of data in the field.
If you do not, you will get an error message. To delete information from an individual field of a record, highlight the field data and then press the Delete key or click the Delete button on the Home tab in the Records group. If you change your mind after you delete information from a field, you can undo the action by clicking the Undo button on the Quick Access Toolbar. In this exercise, you enter a new record as well as edit and delete existing records. Working with Tables and Database Records 39 You can delete an entire record or several records at once from a database.
Just select the row or rows using the Record Selector box, and then press the Delete key or click the Delete button on the Home tab in the Records group. You can also right-click and select Delete Record from the shortcut menu. After you delete a record, you cannot undo it. The insertion point should be positioned in the first field of the new, blank row at the bottom of the datasheet.
Notice the asterisk in the Record Selector box, which indicates that this is a new record, ready for data. Type and then press Tab. Notice that the asterisk has changed to a pencil icon, indicating that the record is being edited. Type Hazelnut and then press Tab. Type 30 and then press Tab. Type and then press Enter. Highlight sunrise in the Search box and then type Kona to locate the Kona record. Click Kona in the record to position the blinking insertion point there.
Delete Kona, type Hawaiian, and then press Tab. Click the Undo button on the Quick Access Toolbar. Notice Hawaiian disappears and Kona reappears. Press Tab.
On the Home tab, in the Records group, click the Delete button drop-down arrow. Select Delete Record from the menu see Figure Click Delete Record.
Figure Delete menu A dialog box appears asking if you are sure you want to delete the record. Click Yes. Notice that the Undo button on the Quick Access Toolbar is not available because you cannot undo a record deletion. CLOSE the table. Take Note You should be cautious when deleting record data. You cannot undo the deletion of a record. Customer IDs, serial numbers, or product IDs usually make good primary keys. Each table 40 Lesson 3 should have a primary key, and some tables might have two or more.
When you divide information into separate tables, the primary keys help Access bring the information back together again. Defining a Primary Key You can define a primary key for a field in Design view by selecting the row that contains the field for which you want to assign a primary key and then clicking the Primary Key button on the Design tab in the Tools group on the Ribbon.
If you do not have a field in an existing database that you think will make a good primary key, you can use a field with the AutoNumber data type. It does not contain factual information such as a telephone number about a record, and it is not likely to change. In this exercise, you define a primary key. Once a primary key is defined, you can use it in other tables to refer back to the table with the primary key.
When a primary key from one table is used in another table, it is called the foreign key. The foreign key is used to reference the data from the primary key to help avoid redundancy. You can modify a primary key by deleting it from one field and adding it to another field.
To remove a primary key in Design view, select the row and then click the Primary Key button on the Design tab in the Tools group on the Ribbon to remove it. On the Home tab, in the Views group, click the bottom half of the View button, and from the menu that appears, select Design View.
On the Design tab, in the Tools group, click the Primary Key button. The Primary Key button is highlighted. A key icon appears on the Order ID row to designate the field as a primary key see Figure LEAVE the table open to use in the next exercise. Working with Tables and Database Records 41 Defining and Modifying a Multifield Primary Key In some cases, you may want to use two or more fields that, together, provide the primary key of a table.
In Design view, select the rows you want to designate as primary keys and then click the Primary Key button. To remove multiple primary keys, select the rows, and then click the Primary Key button. In this exercise, you practice defining and modifying a multifield primary key. Two or more primary keys in a table are called the composite key. Composite keys are useful in unique situations when a combination of data from two fields needs to provide a unique identifier in a table.
For example, area code field data and phone number field data can be combined to create a unique combination of numbers that cannot be duplicated. Separate, neither the area code data nor the phone number data is unique and can be duplicated; however, together they form a unique set of numbers that cannot be duplicated.
Likewise, a business can repeat both order identification number field data and customer identification number field data for multiple customers; however, these numbers become unique when combined resulting in a composite key for each customer. Be sure you are still in Design View on the Order Summary table.
Press and hold the Ctrl key. Click the Row Selector box beside the Paid row. Both fields should be selected see Figure If not, continue to hold the Ctrl key and then click the Paid Row Selector box again. Figure Primary Key row and another row selected Both fields are selected 42 Lesson 3 3. A key icon should be displayed beside both of the two selected fields. The combination of data from these two fields do not make a sensible composite key, and this designation will be removed.
With the rows still selected, click the Primary Key button again to remove the primary key designation from both fields. Click any field name to remove the selection. Both fields should be selected. Both rows should have a key displayed beside them.
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.
❿
No comments:
Post a Comment