You will need to save this file to your pc. Adventure Works 03.mdb

Microsoft Access provides many ways to change the entry, format, properties, organization, and storage of information in your database tables. As the tables in your database become more efficient, your database becomes more efficient.
To reduce errors during data entry, you can modify individual fields within tables to accept only certain types of data or specific data formats. For example, you can set a Date field to accept only six numbers and have Access automatically insert slashes between those numbers so that all dates in the field will appear in the format 10/27/05. You can further reduce the amount of time spent typing by importing information from other tables or spreadsheets into Access.
After the data is entered into the table, you can change the organization of the data by performing a sort to list information numerically or alphabetically. For example, you could organize a table of employee hiring information by date hired, salary offered, or position. If you want to see only some of the information in a table, you can use a filter to restrict the data that appear by specifying which criteria you want to view, such as records for students enrolled in a particular class or who have a GPA greater than 3.0.
Although the Access default datasheet format is clear and easy to read, you can change the formatting of the datasheet to meet your particular needs. You can increase or decrease the size of the font, add color to the datasheet, and apply special effects to the datasheet cells. For cells with large data fields, you can increase the size of the columns to make all or more of the data visible.
Before beginning this exercise, open the Adventure Works 03 database file from the folder on your hard disk.

Your test database should be open. You will now open the tblEmployees table in datasheet view and change the appearance of the datasheet. You will change the font used for the table, the size of the font, and the font style.
In the Database window, on the Objects bar, click Tables, if necessary. All tables currently defined in the database will be listed in the Database window.
Click tblEmployees, and click Open on the Database window toolbar. The table opens in Datasheet view. Notice that the table appears in the default format of 10-point Arial font with silver gridlines.
If the Formatting (Datasheet) toolbar is not visible, click the View menu, point to Toolbars, and click Formatting (Datasheet). The Formatting toolbar appears.
On the Formatting toolbar, click the Font down arrow, scroll down, and then click Times New Roman. The font changes to Times New Roman.
On the Formatting toolbar, click the Bold button. The font changes to bold.
On the Formatting toolbar, click the Font Size down arrow, and click 12. The font changes to 12 points.
You will now change the table background to light blue, use a special effect to make the cells appear raised, and then adjust the column widths to accommodate the formatting changes.
On the Formatting toolbar, click the Fill/Back Color down arrow, and click a light blue square. The table background changes to light blue.
On the Formatting toolbar, click the Special Effect down arrow, and click the Raised (second) option.  The table changes to the raised cell effect. Next, you will change the column widths on some of the columns in order to view all of the data for those fields.
Move the mouse pointer over the line between the Address and City field names until the pointer changes to a resizing doubleheaded arrow. When the pointer changes to the double-headed arrow, you can double-click it to automatically adjust the column width, or you can drag it left or right to change the width.
Double-click the line. The column expands so that the widest record in the Address field, 241 W. 29th Avenue, is fully visible.
Double-click the line between the City and State field names. The column expands so that the widest record in the City field, Sandyland Cove, is fully visible.
Scroll to the right, and double-click the line between the Home Phone and Notes field names. The column expands so that all the phone numbers are fully visible.
Save the tblEmployees table to save your changes.
Every field in an Access table has a set of field properties that can be set and adjusted to make sure your data looks like you expect it to look. The properties available for any given field depend on the Data Type defined for the field. You can modify these properties in Design view by first selecting the field, then accessing the property you want to change in the lower half of the Design view window.
In Access, the data entered into fields and the appearance of that data are controlled by field properties. You set and adjust field properties in the Field Properties section of the Table Design view window.
Every field has field properties specific to the data type for the field. However, a few field properties are common to almost all data types:
The Field Size property limits the number of characters contained in text fields and the amount of disk space reserved for number fields. For example, if you have a field that contains state abbreviations (such as NY or CA), you could limit the size of the field to only two characters to reduce the chance for errors. Text fields can be set to hold from one to 255 characters. Number fields have several advanced settings, which are beyond the scope of this course.
■ The Format property allows you to specify a format for the data in the field. For example, if the field will contain a date, you can choose from several date and time formats such as Short Date (10/27/05) or Long Date (Wednesday, October 27, 2005).
■ The Caption property sets a caption for a field, which will appear as the field name—instead of the field name—when the table is in Datasheet view. For example, a field name might be EmployeeID  (without spaces), but if the caption is Employee ID (with a space), Employee ID (with a space) will appear on the datasheet.
■ The Default Value property reduces the time spent on data entry by automatically entering a default value in a field every time a new record is added to the table. This is a good feature to use if the data in a certain field is almost always the same. For example, if most of Adventure Works guests are from California, you could enter CA as the default value for the State field in a table containing guest addresses. If a guest comes from out of state, you can delete the default value and enter the correct state.
You have seen that you can change the overall appearance of the datasheet. You can also modify the appearance of individual fields by modifying field properties. You will now add captions to some of the fields, change the date format of the DateHired field, and use the Field Size property to limit the size of the EmployeeID field. You will also specify a default value for the Hours field.
Click tblHumanResourcesData, and click Open on the Database window toolbar. The table opens in Datasheet view. Note that the field names appear without spaces in them, dates appear in Short Date format (such as 2/25/98), and the first blank record already has a default value of 0 entered in the Hours field.
On the Table Datasheet toolbar, click the View button. The table appears in Design view, with the Employee ID cell already selected. The properties for the EmployeeID field appear in the Field Properties section of the Table Design view window.
In the Field Properties section, click in the Caption box, and type Employee ID (with a space). The caption of the EmployeeID field changes to Employee ID.
In the Field Properties section, click in the Field Size box, delete the existing text, and then type 4. All employee identification numbers at Adventure Works are four characters long. Access will now limit the Field Size for the EmployeeID field to four characters.
In the Field Name column, click in the DateHired cell. The properties for the DateHired field appear in the Field Properties section of the Table Design view window.
In the Field Properties section, click in the Format box, click the down arrow that appears, and then click Medium Date. The format of the DateHired field changes to Medium Date. The Medium Date format displays dates as 25-Feb-98.
Click in the Caption box, and type Date Hired (with a space). The caption of the DateHired field changes to Date Hired
In the Field Name column, click in the Hours cell. The properties for the Hours field appear in the Field Properties section of the Table Design view window.
In the Field Properties section, click in the Default Value box, delete the existing text, and then type 40. The default value for the Hours field changes to 40. This value will now appear in the blank record at the end of the current datasheet data.
On the Table Design toolbar, click the Save button. An alert box appears, warning that some data might be lost because you changed the Field Size property for one of the fields.
Click Yes. Access saves the table with the changes you have made.
On the Table Design toolbar, click the View button. The table appears in Datasheet view. Note that the EmployeeID field has the caption Employee ID (with a space), the DateHired field has the caption Date Hired (with a space), the data in the DateHired field follows the Medium Date format, and the Hours field in the blank record at the bottom of the table displays a default value of 40.
Even if you didn’t follow a particular order when you first entered data into a table, you can quickly organize the data in a field alphabetically or numerically by performing a sort. There are two types of sorts: ascending and descending. An ascending sort organizes the data in a field from the lowest value to the highest or in alphabetical order starting with A. A descending sort has the opposite effect; it organizes the data in a field from the highest value to the lowest or in reverse alphabetical order starting with Z.
The tblHumanResourcesData table is open in Datasheet view, and you have modified some of the field properties in a previous exercise. You would like to see the data in descending salary order, so you will sort that field using the Sort Descending button. You then perform another sort so that the employee IDs appear in order, from lowest to highest.
Click in any record in the Salary field, and click the Sort Descending button on the Table Datasheet toolbar. Access reorganizes the table in descending order by salary. The record for the employee who earns the largest salary of $45,200 appears at the top.
Click in any record in the EmployeeID field, and click the Sort Ascending button on the Table Datasheet toolbar. Access reorganizes the table in ascending order by employee ID. The record for employee H101, whose employee ID number is first in ascending order, appears at the top.
Save the tblHumanResourcesData table. Tables in Access often get very large, and finding certain data can become very time-consuming. To locate specific information in a table, you can use the Find command. For example, in a table listing employee names and addresses, you could use Find to locate the record for an employee with a specific last name or to locate all addresses for employees who live in Santa Barbara.
You can use the Find command to search just one field or the entire table. If you are working with a large table, limiting your search to a single field means that the search will run faster than it would if the search had to examine all the information in the table. For example, if you are searching for the employee with the last name of Chai, the search will run faster if you search just the LastName field, instead of the entire table. By default, Access searches the last field you clicked in before beginning the search. If you want to search the entire table, you can select that option in the Find and Replace dialog box. The tblHumanResourcesData table is still open in Datasheet view, sorted on the EmployeeID field. You will now use the Access Find command to search the table for any employees with a job title of Host.
Click in any cell in the Title field.
On the Table Datasheet toolbar, click the Find button. The Find and Replace dialog box appears, with Title already in the Look In box.
In the Find What box, type Host, and click Find Next. Access selects the record that contains Host in the Title field
Close the Find and Replace dialog box.
By using a filter, you can restrict data so that only records in a table meeting certain criteria are displayed. For example, you can create a filter to view records in tblHumanResourcesData for only employees who earn more than $30,000 a year.
In Access, you can create filters in two different ways. Filter By Form allows you to specify a field and a specific value in that field to use as the filter. Filter By Selection filters based on the contents of the currently selected field. Filters do not affect the table itself, and they can be turned on and off using the Apply Filter and Remove Filter buttons. These two buttons are actually the same button; the button name changes (although the picture on the button does not) depending on the filter state. After you have created a filter, the Apply Filter button becomes the Remove Filter button. When you click the Remove Filter button, the table reverts to its original display.
Applying and remove Access filters
You previously used the Find command to locate specific data. You can also use the Access Filter commands to find data. You will first use the Filter By Form feature to create a filter that restricts the data in tblHumanResourcesData to employees who work 40 hours each week. You will then use the Filter By Selection feature to create a filter to find only those employees who have the job title of Marketing Rep.
On the Table Datasheet toolbar, click the Filter By Form button. The Filter By Form window for tblHumanResourcesData appears.
Click in the Hours field, click the down arrow that appears, and then click 40.
On the Filter/Sort toolbar, click the Apply Filter button. The table appears, showing only records for employees who work 40 hours each week.
On the Table Datasheet toolbar, click the Remove Filter button. The table returns to its original display.
In the Title field, click in any record containing Marketing Rep.
On the Table Datasheet toolbar, click the Filter By Selection button. The table appears, showing only records for employees with the title Marketing Rep.
On the Table Datasheet toolbar, click the Remove Filter button. The table returns to its original display.
Save the tblHumanResourcesData table.
Close the tblHumanResourcesData table

You can establish relationships between tables that contain similar information or fields. There are three types of relationships that you can establish between the fields in tables: one-to-one, one-to-many, and many-to-many. Many-to-many relationships are beyond the scope of this lesson. The field that is used to establish a relationship between two tables is referred to as the join field. A one-to-one relationship exists when two tables have an identical field containing the same information, meaning each record in a table has one matching record in a related table. For example, both tblEmployees and tblHumanResourcesData have an EmployeeID field that contains 28 records listing the employee IDs for the same 28 employees of Adventure Works. The lists are identical, which means that employee H101 has one record in tblEmployees (listing the employee’s address and phone number) and one record in tblHumanResourcesData (listing the employee’s salary, position, date of hire, and hours worked per week). The relationship between tblEmployees and tblHumanResourcesData is a one-to-one relationship because if there is a record containing H101 in the EmployeeID field of tblEmployees, there is one and only one record containing H101 in tblHumanResourcesData
The tblProducts table contains descriptions of products sold by the vendors that Adventure Works buys from. You will now create a relationship between the tblProducts table and the tblVendors table. You will use the VendorID field that exists in both tables to establish the relationship.
On the Database toolbar, click the Relationships button. The Relationships window appears.
On the Relationship toolbar, click the Show Table button. The Show Table dialog box appears. In this case it lists all of the tables that are defined in the database. You can also create relationships using queries by clicking the Queries tab at the top of the dialog box.
Click tblProducts, and click Add. Access adds the field list for tblProducts to the Relationships window.
Click tblVendors, and click Add. Access adds the field list for tblVendors to the Relationships window.
Click Close. The Show Table dialog box closes, and the Relationships window becomes completely visible. The Relationships toolbar is also visible
In the tblProducts field list, click VendorID, and drag the field name on top of the VendorID field in the tblVendors field list. The Edit Relationships dialog box appears, as shown in the following figure. This dialog box shows you the tables and the fields involved in the relationship. At the bottom of the dialog box it tells you what type of relationship this is.
Select the Enforce Referential Integrity check box, and click Create. The Edit Relationships dialog box closes, and a line appears between the VendorID fields in the tblProducts and tblVendors field lists, indicating that a relationship exists between these two tables.
On the Relationship toolbar, click the Save button. Access saves the Relationships window.
Click the Close button in the top-right corner of the Relationships window. The Relationships window closes.
In the Database window, click tblProducts to select it and then click Open on the Database window toolbar. The table opens in Datasheet view.
Click the New Record button at the bottom of the datasheet. The insertion point is placed in the VendorID field of a blank record.
In the VendorID field, type RQ, and press Tab. The insertion point moves to the Description field.
In the Description field, type Pillow Chocolates (1000), and press Enter. An alert box appears, indicating that you cannot add this record because a related record is required in tblVendors. This is because you set the Enforce Referential Integrity option in the Edit Relationships dialog box.
Click OK. The alert box closes. The record has not been added to the table.
Click in the VendorID field, delete the existing text, type WW, and then press Enter. Access selects the text in the Description field. No alert box appears; WW is an acceptable value. The record has now been added to the table.
Close the tblProducts table.
If you have two tables in a one-to-many relationship, you can create a subdatasheet to allow you to see the records from one table while the other table is open. A subdatasheet is a table within a table. For example, if you create a subdatasheet from the one-to-many relationship between tblVendors and tblProducts, you can see the products carried by each vendor listed in tblProducts by expanding the record in tblVendors.
For a subdatasheet to work, there must be at least one identical field in both the table that you are working in and the table that you want to insert as a subdatasheet, and that field must be the primary key for at least one of the tables. For tblVendors and tblProducts, that field is VendorID, the primary key in tblVendors.
Create a subdatasheet for related tables
You have already established a relationship between two tables, using the primary key field from tblVendors as the relationship field. Your database now has the required elements defined to create a subdatasheet. In this exercise, you create a subdatasheet in tblVendors that contains the related information from tblProducts.
In the Database window, Click tblVendors, and click Open on the Database window toolbar. The table opens in Datasheet view.
On the Insert menu, click Subdatasheet. The Insert Subdatasheet dialog box appears, with tblProducts already selected.
Click OK.  The Insert Subdatasheet dialog box closes, and tblVendors appears in Datasheet view, with plus signs (+) to the left of the records, indicating the presence of subdatasheets.
Click the plus sign (+) to the left of the first record, for VendorID AD, A. Datum Corporation. The subdatasheet of products offered by A. Datum Corporation, taken from tblProducts, appears.
Save the tblVendors table.
Close tblVendors.
Exit Access
Okay here is the twist.  I need you to make sure you have exited Access, then I need you to find where you saved, once you find and rename it Adeventure works 03.joe yes .joe.  it is a long story but Microsoft will not let you email a mdb file so we have to fool it
Email your completed file as an attachment to me.  Good job well done.