Click the dialog box launcher in the lower-right corner of the Text Formatting group. The Datasheet Formatting dialog box appears, as shown here. 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.

See the figure on the next slide. It contains a menu of options and commands that allows you to access various screens to perform common tasks with your database files 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. Used to view the current database file path, view and edit database properties, compact and repair the database, and encrypt the database. New: Use this option to create a new database.

Open: Use this option to open an existing database and view a list of recently accessed databases. Save: Use this option to return to the open database window where objects can be saved. Save As: Use this option to save the current database object as a new object or save the database in another format that is compatible with earlier versions of Access. You can save the database to a document management server for sharing or you can package the database and apply a digital signature.

Close: Use this option to close the open database but keep the Access application open. Account: Use this option to view and modify user account settings, change application background and theme, add a service, manage your Office account, update office, and learn more about Access.

Options: Use this option to customize language, display, proofing, and other settings. 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. 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 the figure on the next slide. 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.

Design the database so that it accommodates all your data-processing and reporting needs. 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. Categorize the information by dividing it into subjects, 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.

Each row in a table is called a record. Each column in a table is called a field. Determine what information you want to store in the table. Organize each piece of information into the smallest useful part. These pieces of information will eventually become your fields columns , and each record row will then contain complete information about the items.

For each table, you will choose a primary key. A primary key is a column that uniquely identifies each row. 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. When defining table fields, it is important to define them as specifically as possible. Access provides 11 data types, each with its own purpose.

The table on the next two slides describes the types of data that each field can store. You will learn more about table Design View in Lesson 9.

Click the Date of Birth field header. On the Ribbon, click the Fields tab. Click the Last Name header. Notice that the Data Type is Short Text and that no formatting options are available for that data type. In the Data Type box, click the down arrow and then click Short Text to change the data type. Scroll to the far right and then click the Click to Add column header. 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.

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. In a simple database, you might have only one table. Most databases will have more than just one table.

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 the figure on the next slide. 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.

These are referred to as normal forms. There are five normal forms, but typically only the first three are applied: First Normal Form 1NF : This form divides each field according to its smallest meaningful value, removes repeating groups of data, and creates a separate table for each set of related data. Second Normal Form 2NF : With this form, each non-key column should be fully dependent on the entire primary key.

Create new tables for data that applies to more than one record in a table and add a related field to the table. Third Normal Form 3NF : Use this form to remove fields that do not relate to, nor provide a fact about, the primary key. 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. Advanced Tables Lesson 9.

Using Backstage Lesson 2. Similar presentations. Upload Log in. My presentations Profile Feedback Log out. Log in. Auth with social network: Registration Forgot your password?

Download presentation. Cancel Download. Presentation is loading. Please wait. Copy to clipboard. Microsoft Official Academic Course, Access 8 Microsoft Official Academic Course, Access Starting Access Microsoft enables you to sign into Office to make it easier for you to access and share your documents from virtually anywhere. Microsoft Official Academic Course, Access 12 Opening an Existing Database When you open an existing database, you access not only your previously entered and saved data, but also the elements you created to organize that data.

Microsoft Official Academic Course, Access 16 Step by Step: Open an Existing Database If you open a file in a multi-user environment, other users can read and write to the database as well. Microsoft Official Academic Course, Access 17 Microsoft Official Academic Course, Access Software Orientation The Access user interface was designed to help you find the commands you need quickly so that you can successfully perform your tasks.

Microsoft Official Academic Course, Access 19 Using the Navigation Pane Before you can create a database, you need to understand its most basic elements.

Microsoft Official Academic Course, Access 24 Microsoft Official Academic Course, Access Using Object Tabs When you create a database in Access, all the objects in that database—including forms, tables, reports, queries—are displayed in a single window separated by tabs. Microsoft Official Academic Course, Access 30 Selecting Tools and Commands A command is a tool such as an icon, a button, or a list that tells Access to perform a specific task.

Microsoft Official Academic Course, Access 38 Using the Backstage View Print: Use this option to quick-print to a printer, open a dialog box from which to choose print options, or preview your document before printing. Microsoft Official Academic Course, Access 43 Defining Data Needs and Types In a database table, data is stored in rows and columns—similar in appearance to a spreadsheet.

Microsoft Official Academic Course, Access 45 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.

Microsoft Official Academic Course, Access 48 Defining and Modifying Data Types for Fields 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.

Microsoft Official Academic Course, Access 56 Defining Database Tables An important principle to consider when planning a database is to try to record each piece of information only once. Microsoft Official Academic Course, Access 57 Defining Database Tables Database design principles include standards and guidelines that can be used to determine if your database is structured correctly. About project SlidePlayer Terms of Service. Feedback Privacy Policy Feedback. All rights reserved. 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. 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. To delete a table or other database object like a report, form, or query, right-click it in the Navigation Pane and click Remove or Delete.

Or, select the table in the Navigation Pane and then press Delete. USE the Events-final database that is still open from the p revious exercise. A confirmation message appears.

 
 

Where is the Access training course(s) in MS Virtual – Microsoft Community.(PDF) Microsoft Official Academic Course | 治 王 –

 
Microsoft Official Academic Course MICROSOFT WORD Includes coverage of the following Microsoft Office Specialist (MOS) exams: MOS EXAM WORD. As of today we have 81,, eBooks for you to download for free. Microsoft Official Academic Course. MICROSOFT ACCESS Includes coverage of the. Microsoft Official Academic Course MICROSOFT ACCESS Includes coverage of the following Microsoft Office Specialist. Views Downloads File size.

 

Microsoft official academic course microsoft access 2016 free download. Microsoft Official Academic Course

 

I have narrowed the search, but nothing. I truly need to get going with the training, especially after finishing a SharePoint book that covered Access but lightly, I got hooked, what I learned was great!! I took a look in the MVA. By my count there are only about 65 courses, and it appears that none are specifically for Access. Change in MS Office programs is “evolutionary” rather than “revolutionary”.

Even the “ribbon” is simply a new shell on top of essentially the same underlying program with just a few new features added. The point is, if you find a resource for an older version, don’t ignore it simply because it is old. There is lots of training material out there, so vary your search terms. If you are looking for more learning resources this link provides a collection of published IT books, filtered for “Access”. A few are not for MS Access, but it is a good way of finding books you can look for in stores or library :.

It has course outlines for multiple levels of training they used to run. Do download them! Laws of the seven R’s: “Always, always do the right thing for the right reason at the right time with the right people.

Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site.

Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software.

Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation. Details required : characters remaining Cancel Submit. Was this reply helpful?

Yes No. Sorry this didn’t help. Thanks for your feedback. Choose where you want to search below Search Search the Community. This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread. I have the same question 4. Report abuse. Details required :. Cancel Submit. Rohn MVP. In reply to DavesterBloomington’s post on November 27, How satisfied are you with this reply? Thanks for your feedback, it helps us improve the site.

This site in other languages x.