ACCESS LNC

week_3_pdf6.pdf

Home >Computer Science homework help >ACCESS LNC

Week 3  What We'll Be Working on This Week Now that your table structures are built, you're ready to create the appropriate relationships between the tables, and then begin entering data. With regard to table relationships, we'll be creating two types:

 One-to-One relationships, in which one record in table x matches one record in table y.

 One-to-Many relationships, where one record in one table matches multiple records in one or more other tables.

I know I have been mentioning this numerous times already, but it's extremely important and therefore bears repeating. Please pay particular attention to this aspect of database design in Access. You need to fully comprehend this concept in order to get your database set up correctly. Not fully grasping the concept will trip you up later on, so if you're not sure of it, please ask for as much clarification as you need. In addition to defining table relationships, we'll look at a number of different ways to enter data in a table. Typing, of course, is one way to do it, and Access provides several great keyboard shortcuts for quickly and easily entering the same information into a given field in multiple records. But you can also create a Value List in a field, so that the contents of the field are displayed on a drop-down list from which the user simply has to make a selection. In this way, you can streamline the data-entry process and eliminate errors. In addition to these time-saving features, we'll look at how to add hyperlinks to fields.

Goals:

 Defining relationships page 2

 Moving and resizing field list windows page 10

 Creating relationships page 11

 Typing data into a table page 19

 Creating a new table in Design View page 23

 Adding a value list to a field page 25

 Inserting hyperlinks into fields page 31

 Rotating a hyperlink image page 38

This is where we're headed this week. So here we go!

Goals for this section:

 Defining relationships

 Moving & resizing list windows

 Creating relationships

Defining a Relationship Between Tables

If you plan to pull data from multiple tables for forms, queries, or reports, you should establish relationships between the tables so that such procedures are easy. If you have one-to-many relationships created between tables, Access will automatically use the child table to create a Subdatasheet or Subform for the parent table. (Remember, in a One-to-Many relationship the parent table is the One side of the relationship, and the child table is the Many side.) This will eliminate the need to create subdatasheets or subforms manually. In addition, when you create queries based on multiple related tables, Access will automatically connect the key fields on the Query Design window, so that the contents of your queries will be correct without your having to think about connecting the tables again. The bottom line is this: You design your table relationships once, and Access then uses those relationships again and again as you go about creating other objects for your database. Although relationships may be created at any time, it's best to do so before your tables have too much data in them. In this way, you'll be able to make the necessary adjustments to the tables and/or relationships without compromising the integrity of your data. The field that uniquely identifies each row in a table is called a primary key. The corresponding field in a related table is called the foreign key. When you attempt to establish a relationship between two tables, Access makes some decisions based on a few predefined factors:

When deciding how to relate the tables in your database, you should keep the following in mind:

 In a one-to-one relationship, each record in Table A can have only one matching record in

Table B and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons (i.e. a table with employee personal data and a table with employee work data), or to store information that applies only to a subset of the main table. For example, you might want to create a table to track employees participating in a fundraising soccer game. Each soccer player in the Soccer Players table has one matching record in the Employees table. In this type of relationship, the primary key field in Employee table will be related directly to the primary key field of the Soccer Player table.

If you need one record in table x to match multiple records in table y, you need a one-to- many relationship. This is the most common type of relationship The table on the One side of the relationship is the Parent table, and the primary key field of that table will be the matching field. Each record in the table must contain a unique value in the primary key field.

The table on the Many side of the relationship is the Child table, and the matching field in that table will not be a primary key field (otherwise it would be a 1-to-1 relationship). The matching field in the child table is referred to as the Foreign key field and it doesn't need to contain a unique value in each record. In the following illustration, one employee (Howell) is working on many work orders (work orders 001 and 004).

The matching fields in relationships do not need to have the same name, but they must contain the same data type of information.

If the data type of the common fields in a relationship is a Number field, they must have the same Field Size property (that is, Integer, Single, Double, Long integer, etc.).

The following illustration shows one-to-one relationships as well as on-to-many relationships:

 one-to-one relationship between parent tblCarID and parent tblCarDetail. The primary key in tblCarID is also the primary key in the tblCarDetail. A car has just one invoice number, one price.

 one-to many relationship between parent table (table with the primary key) tblCustomer and tblSales. Note that the field names are not the same.

 one-to-many relationship between parent table tblCustomerType and tblCustomer. tblCustomerType may have entries such as Leaser, Corporate, Rental Agency. tblCustomer is identified as either Leaser, Corporate, Rental Agency. Each entry in tblCustomerType can have several customers from the tblCustomer.

 one-to-many relationship between parent table tblPaymentType and tblPaymentDetail. A single payment type can be on several different invoices.

 one-to-many relationship between parent table tblSalesPeople and tblSales. A single sales person in tblSalesPeople may (and hopefully) have many sales in tblSales.

 one-to-many relationship between parent table tblSales and tblPayemtDetail. A particular sale (tblSales) may have different methods of payment (down payment, monthly billing - tblPaymentDetail).

 one-to-many relationship between parent table tblSales and tblCarDetail

Additional examples of 1-to-many relationships are:

 Customers and orders: Each customer (the one side) has placed several orders (the many side), but each order is sent to a single customer.

 Teacher and student: Each teacher has many students, but each student has a single teacher within a particular class.

 Employees and paychecks: Each employee has received several paychecks, but each paycheck is given to one and only one employee.

 Patients and treatments: Each patient receives none or more treatments for a disease.

In a many-to-many relationship, each record in both tables can be related to zero, one, or many records in the other table. Many records in one table have the same values in the key field as many records in the second table. For example: a student can belong to more than one club while each club has more than one member. This type of relationship cannot be directly modeled in relational database systems. Instead the relationship is broken into two separate one-to-many relationships, joined through a linking, join, or junction table. This new table has one -to -many relationships with both of tables involved.

Some examples of many-to-many relationships:

 Lawyers to clients: Each lawyer may be involved in several cases, while each client may be represented by more than one lawyer on each case.

 Patients and insurance coverage: Many people are covered by more than one insurance policy. For instance, you and your spouse are both provided medical insurance by your employers, you have multiple coverage.

 Video rentals and customers: Over a year's time, each video is rented by several people. While each customer rents several videos during the year.

 Magazine subscriptions: Most magazines have circulations measured in the thousands or millions. Most people subscribe to more than one magazine at a time.

Because of the additional complication of the join table, many-to-many relationships are often considered more difficult to establish and maintain. Access can do this as long as:

 The join table contains the primary keys of both tables joined by the relationship

 The join table can contain information regarding the joined data

The following illustration shows a Many-to-Many relationship. Each student can belong to many clubs and each club can have many students. The join table (tblStudentToClub) breaks up the many-to-many relationship into two 1-to-many relationships. Access is now able to handle queries between the tables. For example, one can find out all students who belong to the French Club as well as a query to find all clubs a student may belong to. The join table does not have a primary key but the contents of the table consist of the primary keys from each of the two tables.

You need to give a great deal of thought to the relationships in your database, because if you have the wrong type of relationship created between two tables or if you have related the tables on the wrong key fields, your data will not be displayed correctly.

Table relationships are created on the Relationships window, which is accessed by clicking the Relationships icon on the Database Tools tab.

If no relationships have been created for the current database, the Show Table dialog box is displayed so that you may specify which tables you want to place on the Relationships window.

If one or more relationships have already been defined for the current database, then Access does not automatically display the Show Table dialog box.

To add a table to the Relationships window, you can drag and drop the table from the Navigation pane, or select the Show Table icon in the Design / Relationships group.

Once the tables have been added to the Relationships window, drag the primary key field of one table to either the primary or the foreign key field of another table, to create either a One- to-One or a One-to-Many relationship, respectively. If you have defined your tables correctly, Access will interpret your intentions correctly and will indicate the appropriate relationship type at the bottom of the Edit Relationships dialog box. If you see Indeterminate specified as the Relationship Type instead of One-to-One or One-to-Many, you have not defined the key fields in your tables correctly or you have not connected the appropriate fields. As a result, Access doesn't know how you want to display your data. (In other words, the translation of Indeterminate as the Relationship Type is: Huh?) If this happens, go back and analyze your proposed table relationships and the fields that you're trying to use for joining the tables, to try and find the problem.

Hands-On Activity: Defining Relationships

Before beginning: Access is running, with your Home Tech Repair database file open.

1. On the Database / Tools tab, click the Relationships icon.

Since no relationships exist as yet for this database, the screen is blank. Note you may have 3 tables on the screen as remnants of the Northwind db that we first used. Move your cursor onto each table and hit the Delete key.

2. Click on the Show Table icon in the Design / Relationships group and the Show Table screen is displayed.

3. On the Show Table dialog box, leave tblBidData selected and click the Add button. A copy of the selected table is added to the Relationships window.

4. Alternatively, you can double-click the tblCustomer table name, to add it to the Relationships window or you can drag and drop the table into the Relationship window. In my opinion, double clicking is the fastest way to get the tables into the Relationships window.

5. Double-click on the tblEmployeeHRData object name, to add that table to the window.

6. Double-click on the tblEmployeePersonal object name, to add that table to the window.

[We are deliberately omitting tblWorkOrders. You'll see why in a moment.]

7. Click the Close button on the Show Table dialog box, to remove the dialog box from the screen.

8. The Relationships window now contains the field lists of four tables:

At this point, you look at the Relationships window and realize you forgot tblWorkOrders. Additional tables may be added to the Relationships window at any time, by clicking the Show Table icon on the Relationships area of the Design tab, or dragging the table from the Navigation pane.

9. Drag the tblWorkOrders from the Navigation pane onto the Relationship window. When a small datasheet view icon appears you can release the mouse.

Changing the Appearance of the Relationships Window

On the Relationships window, Field List windows may be resized to display fewer or more fields, and the windows may be moved.

To resize a Field List window, position the mouse pointer on a corner or edge of the window, so that the mouse pointer changes to a double-edged arrow. Then drag to the desired size.

To move a Field List window, position the mouse pointer in the Title Bar of the table, hold down the left mouse button, and drag to the desired location.

To delete a table from the Relationship window, highlight any field in the table and hit the Delete key.

If you make changes to the layout of the Relationships window, when you close the window Access will ask you if you want to save the changes.

Hands-On Activity: Moving and Resizing Field List Windows

Before beginning: Your Home Tech Repair database file is open, with the Relationships window active. There are 5 tables and their field lists in the window.

1. Position the mouse pointer on the lower right corner of tblBidData.

2. Hold down the left mouse button, and drag the Field List window down and to the right on the

Relationships screen. The mouse pointer should appear as a double-edged arrow .

3. Hold down the left mouse button and drag down to expand the Field List window, and display all of the fields in the list. You know when all of the fields are displayed when the vertical scroll bar disappears from the Field List window. Do the same for the other four field list windows. The position of the tables may be different on your screen.

4. If your tables are too close spread them out. To do this, position the mouse pointer in the Title Bar of the table, hold down the left mouse button and drag the table to the desired position. The window should now look similar to the following:

Figure 6: Tables Moved and all fields Displayed

Creating Relationships

Once you have the table field lists displayed on the Relationships window, you're ready to create the desired relationships for your tables. To do this, you will drag the key field of one table to the key field of another table. If you drag between two primary key fields, Access will create a One-to- One relationship; if you drag between a primary key field and a foreign key field, Access will create a One-to-Many relationship. If you drag between two foreign fields (many-to-many relationship) then Access will create an indeterminate relationship (which you do not want).

Hands-On Activity: Creating Relationships

Before beginning: Your Home Tech Repair database file is open, with the Relationships window active. The field lists have been expanded for each table so that all of the fields are fully visible.

Our objective is this: The first relationship that we'll create will be a One-to-Many relationship between the strCustomerID field of tblCustomer and the strCustomerID field of tblWorkOrders. This will enable us to display our customers with the work orders that are being processed for those customers.

1. Position the mouse pointer on the strCustomerID field in either the tblCustomer or the

tblWorkOrders tables, and drag to the strCustomerID field in the other table. Notice that you are dragging between the primary key field in tblCustomer, and the foreign key field of tblWorkOrders. This type of join always produces a One-to-Many relationship.

When you release the mouse, the Edit Relationships dialog box is displayed, with One-to- Many indicated as the Relationship Type.

We are not setting Referential Integrity for this relationship yet, so click Create to create the relationship. A black line joins the two fields. You may need to move your tables to get a better view of the relationship. To move the tables, refer to the Hands-On Activity: Moving and Resizing Field List Windows.

The reason why Access knew to create a One-to-Many relationship between these two tables is that you joined the primary key in one of the tables (tblCustomer) to a field that is not the primary key field in the other table (tblWorkOrders). Whenever you do this, you get a One-to- Many relationship.

Next we'll create a One-to-Many relationship between tblBidData and tblWorkOrders, based on the strBidNumber field. This relationship will enable us to list all work orders with the bids that were submitted for the work orders.

2. Drag the strBidNumber field of tblBidData to the strBidNumber field of tblWorkOrders. Ensure that the correct fields are being related and that the Relationship Type (One-to-Many) is correct. Once again, you have joined the primary key in one of the tables (tblBidData) to a field that is not the primary key field (that is, a foreign key field) in the other table (tblWorkOrders), so a One-to-Many relationship is proposed by Access.

3. Do not select any of the Referential Integrity options, and click Create.

Your Relationships window should now look similar to the following:

Now let's see what happens when you drag between two illogical fields to create a relationship that Access doesn't understand.

4. Drag the curHourlyRate field of tblEmployeeHRData to the memDescription field of tblWorkOrders.

5. Observe the Relationship Type on the Edit Relationships dialog box: Indeterminate.

Because you've tried to relate a Currency field with a Memo field, which are incompatible data types, Access doesn't know how to connect the data.

6. Click Cancel, to cancel the relationship.

Next, we'll create a One-to-Many relationship between tblEmployeeHRData and tblWorkOrders based on the strEmployeeID field (in tblEmployeeHRData) and the strSupervisor field (in tblWorkOrders). This will enable us to display all work orders and the employees who are supervising those work orders.

7. Drag the strEmployeeID field of tblEmployeeHRData to the strSupervisor field of tblWorkOrders.

Note: It doesn't matter in which direction you drag. Access will define the appropriate type of relationship if your fields have been defined correctly and if you are dragging between two appropriate fields. Also note that the field names do not have to be the same. The important thing is that the fields must be the same data type.

8. On the Edit Relationships dialog box, ensure that the correct fields are being related and that the Relationship Type (One-to-Many) is correct.

9. Do not select any Referential Integrity options.

10. Click Create, to define the relationship.

The Relationships window should now look similar to the following:

Finally, we need to create a One-to-One relationship between tblEmployeeHRData and tblEmployeePersonal so that we can list employees with their name and address along with their Date of Hire and Hourly Rate.

11. On the Relationships window, drag the strEmployeeID field of tblEmployeePersonal to the strEmployeeID field of tblEmployeeHRData. Because you have joined the primary key field in one of the tables to the primary key field in the other table, Access proposes a One-to-One relationship between the two tables.

12. Ensure that the Relationship Type (One-to-One) is correct.

13. Don't select any of the Referential Integrity options, and click Create to define the relationship.

Note: You could have combined the data from tblEmpolyeesHRData and tblEmployeePersonal into 1 table, but the company may not have the individual entering the data in the tblEmployeePersonal be the same person entering HR type of data. By separating out the data, security can be placed on the tables so that only certain personnel have access to particular data.

Your Relationships window should now look similar to the following:

14. Close the Relationships window. Access asks if you want to save the changes because you changed the position of the field lists.

15. Click Yes, to save the changes to the field list position and sizing.

Goals for this section:

 Typing data into a table

 Creating a new table in Design View

 Adding a Value List to a field

Entering Data into Fields

As we have seen, when you create an object in a database or make changes to the structure of an existing object and then close the object, Access always asks you if you want to save the changes to that object. However, you need to be aware of the fact that this only happens when changes are made to the object design, not when new records are entered. When you enter data into a record, you will notice that the icon in the Row Selector to the left of the record

changes to a pencil icon. This serves as a reminder to you that the entry has not been saved. As soon as you move the cursor out of the record, you are doing what's referred to as Moving off the focus, which causes the new entry to be saved. At that point, the Row Selector becomes blank. Once this has occurred, clicking Undo will reverse all

changes made to the fields in the record. The Undo button on the Quick Access Toolbar (QAT)

is often dimmed because there is nothing to undo. As soon as you begin editing a record, however, you can use this button to undo the typing in the current field. You can also undo a change with the Esc key; pressing Esc cancels either a changed value or the previously changed field. Pressing Esc twice reverses changes to the entire current record.

You can also undo all the changes to an unsaved current record by clicking the Undo button after you undo

a field. After you save a record, you can still undo the changes by clicking the Undo button. However, after the next record is edited, the undo button will not change the previous record. That does not mean the data cannot be changed, you just have to reedit the field. You should enter data into a table in Datasheet View, using the Tab key to move the cursor from field to field from left to right. To move the cursor to the left one cell at a time, hold down the Shift key and press Tab.

CAUTION: In Access, the Undo command operates very differently than it does in other Microsoft applications. Some actions that you'll be performing in Access cannot be undone, in which case Access will display a warning message. A word to the wise: Read all warning messages very carefully.

Hands-On Activity: Typing data into a table

Before beginning: Your Home Tech Repair database file is open.

1. Display the tblEmployeeHRData table in Datasheet View. The cursor should be flashing in the Emp ID field.

2. Notice that today's date appears in the Current Date field at the end of the table. Because we

defined this field as a Default Value Date/Time field using the =Now() function, the current date will be automatically entered as soon as you add a new record to the table.

3. Type 10 as the ID for the first employee.

4. Press Tab, to move the cursor to the Emp First Name field.

5. Enter Tony as the first name.

6. Press Tab, to move the cursor to the Emp MI field.

7. Enter V. as the middle initial.

8. Press Tab, to move the cursor to the Emp Last Name field.

9. Enter Wilson as the last name.

10. Press Tab to move the cursor to the SS# field.

11. Enter 304001369 as the Social Security number. The Input Mask that we defined for this field is controlling the display of the number in the field. The first three digits are followed by a hyphen, and another hyphen separates the second set of numbers from the third.

12. Press Tab, to move the cursor to the Specialty field.

13. Enter Engineering as the specialty.

14. Press Tab, to move the cursor to the Work Extension field.

15. Enter 2002 as the work extension.

16. Press Tab, to move the cursor to the Hourly Rate field.

17. Type 30.25. Notice that, while the cursor is in the field, the dollar sign isn't displayed. Once you move out of the field, it will appear.

18. Press Tab, to move the cursor to the Hours field. The dollar sign is now displayed on the Hourly Rate for record 1.

19. In the Hours field, enter 42.

20. Press Tab, to try to move to the next field. Because we applied a Validation Rule to the Hours field, Access has displayed an Alert Box that tells you that the number of hours may not exceed 40. What's displayed on the Alert Box is the Validation Text property that you assigned to this field when designing the table.

21. Click OK, to close the Alert Box.

22. Change the number of hours to 40, and press Tab. Because the value in the Hours field now satisfies the Validation Rule of <=40, we're able to move out of that field. Until all Validation Rules are satisfied, Access won't allow you to move out of the field.

23. In the Hired field, enter 3/5/95. [Be sure to enter the date with either hyphens or forward slashes. If you enter the date with hyphens, Access will automatically convert them to forward slashes.]

24. Press Tab, to move the cursor to the Insured field. Notice how the Hired date is now displayed: Because we chose the Short Date format for this field, the appearance of the year has changed from two digits to four.

25. Observe the Insured? field. The check box has a small border around it, to reflect the fact that it's the active field. This selection border is somewhat difficult to see at first, but you need to train your eye to notice it. Once you do that, you will be able to take full advantage of the keyboard shortcut that we're going to use right now to toggle the selection on and off.

26. Press the Spacebar. Notice how this de-selects the check box.

27. Press the Spacebar again, to check the box. This is a potentially valuable keyboard shortcut, because your hands will already be on the keyboard as you enter the data into the record. If you had to stop to use the mouse each time you wanted to select or deselect a checkbox, it would slow you down considerably. In my opinion, the keyboard is the only way to fly. We need every shortcut we can get in order to enter the data in the quickest way possible.

28. Notice the pencil icon in the Row Selector.

This reflects the fact that you haven't yet moved off the focus, and therefore the data in this record haven't been saved.

29. Click in any field of the blank record below record 1, to move off the focus. The data in record 1 are saved.

30. Click the Undo button on the Quick Access Toolbar (QAT). Access displays an Alert Box warning you that you're about to delete the last record.

Immediately after you have finished entering a record into a table, clicking Undo will remove the entire last record that was entered.

31. Click No, to cancel the Undo and return the record to the table.

32. Observe the plus sign to the left of the value in the first field of record 1: Because we created a One-to-Many relationship between this table and tblWorkOrders, Access automatically created a subdatasheet for this table.

33. Click the plus sign to the left of record 1, to display the subdatasheet.

Figure 19: Subdatasheet for Employee

The tblWorkOrders table is currently empty, so no records are displayed in the subdatasheet. However, once that table contains data you'll be able to view the work orders for each employee while you're viewing the HR data for that employee.

34. Click the minus sign to the left of record 1 in tblEmployeeHRData, to collapse the

subdatasheet.

35. Click in the Comments field for record 1.

36. Enter the following notes for this employee:

17+ years experience in the field.

37. Press Tab twice. The cursor is now in the first field of record 2, and you're ready to add the next record to the table.

This is the way you enter records into a table. You simply type, press the Tab key, keep typing and tabbing until you reach the end of the record, then Tab again to reach the first field of the next record.

Using Keyboard Shortcuts to Save Data-Entry Time Access provides several excellent keyboard shortcuts for entering data into a table quickly. And believe me, where data entry is concerned we can use all the timesaving features we can get!

If you want to enter the same value from the previous record into the same field of the current record, press Ctrl " to repeat that value. To enter the current date into a field, use the Ctrl ; keyboard shortcut. To enter the current time in a field, use the Shift Ctrl ; keyboard shortcut.

Hands-On Activity: Creating a New Table in Design View

Before beginning: The tblEmployeeHRData table is displayed in Datasheet View.

1. In the Emp ID field of record 2, enter 11.

2. Press Tab, to move the cursor to the Emp First Name field.

3. Enter Doug as the employee first name.

4. Press the Tab key, to advance the cursor to the Emp MI field.

5. Type M. as the employee middle initial.

6. Press the Tab key, to move the cursor to the Emp Last Name field.

7. Type Roberts as the last name, and press Tab to advance to the SS# field.

8. Type 338005944 as the Social Security number for this employee.

9. Press Tab, to move the cursor to the Specialty field. Suppose Doug Roberts is also in Engineering. Instead of re-entering the data, you can use the Ctrl " keyboard shortcut to copy the value from the previous record.

10. Press Ctrl ". Engineering is entered into the Specialty field for this record.

11. Press Tab, to move the cursor to the Work Extension field.

12. Enter 2003 as the work extension.

13. Press Tab, to move the cursor to the Hourly Rate field.

14. Enter 25 as the Hourly Rate and press Tab to move to the Hours field. Because we formatted the Hourly Rate field with 2 decimal places, $25.00 appears in that field.

15. Enter 32.5 as the number of hours for this employee.

16. Tab to the Hired field. Let's say Doug Roberts was just hired today. Rather than manually entering the current date (and running the risk of getting it wrong), we'll use a keyboard shortcut.

17. Press Ctrl ; to enter the system date into the field.

Note: This keyboard shortcut enters the system date of your computer into the field. If your system date is wrong, you need to change it through Windows.

18. Press the Tab key twice, to move the cursor to the Comments field.

19. Enter the following note for Doug Roberts:

New employee, but shows potential.

20. Press Tab twice, to move the cursor to the Emp ID field of record 3. Record 2 has been saved.

Display Controls: Combo Box, List Box, Text Box field

In the Hands on Exercise below, I have opted to create a Value List (List Box) but want you to know the pros and cons of all 3 options: List Box displays a specified list of choices to the user. The user can enter anything they want but list offers some guidance. To restrict the user to just entries on the list, you can enter a validation rule as well when you are designing the field. By default the list contains one column although you can set up multiple columns as you will see later in the course. This control can also be put on forms which we will see later in the course. Combo Box is a data capture control. The user does not have to remember the exact data to be entered thus reducing data entry errors. Alternatively, the user has the ability to add an item that is not on the list. This control can also be put on forms which we will see later in the course. Text Box is used to display information entered by the user at run time.

Row Source Type: Table/Query, Value List, Field List

Table/Query specifies that you want rows from a table or query to fill the list. If the Display control is a combo box, then the combo box is populated by a table or query. The value entered is the name of the table or query. Value List wants you (designer) to enter the values that you want to be displayed. Each value must be surrounded by quotation marks and separated by a semicolon. This is what we will do in the Hands on Exercise. Field List shows the names of the fields from the table or query you enter in the Row Source. If the Display control is a combo box, then the it is populated by the list of fields of the specified table or query.

Adding a Value List for Data Entry

You can streamline the data-entry process in order to provide a list of items in a field from which the user can simply choose (instead of typing). To do this, add a Value List to the field. This will not only save data-entry time, but it will also eliminate errors and as a result it will make your queries and reports more accurate.

The procedure for adding a Value List to a field is as follows:

1. Display the table for which the Value List is to be created, in Design View.

2. In the upper portion of the Table Design screen, place the cursor in the field for which the Value List is to be created.

3. In the lower Field Properties pane, select the Lookup tab of the Field properties window. The Display Control text box by default reflects Text Box.

Hands-On Activity: Adding a Value List to a field

Before beginning: Your Home Tech Repair database file is open, with the tblEmployeeHRData table displayed in Datasheet View. The first two records of the table have been entered.

1. Display the tblEmployeeHRData table in Design View.

2. In the upper pane, place the cursor in the strSpecialty field.

3. In the Field Properties window (in the lower portion of the screen), activate the Lookup tab. The Display Control lists Text Box, which is the normal display for data in Text fields.

4. Click to place the cursor in the Display Control line. When you do, a appears to the right of the line.

5. Click the to display the drop-down menu.

6. Select the List Box option from the drop-down menu.

When you do, the options on the Field Properties window change so that you may indicate the source of the data to be reflected in the List Box as well as how you want the List Box to look.

7. Position the cursor in the Row Source Type line.

8. Click the to display the Row Source Type drop-down menu.

9. From the drop-down menu, choose Value List.

10. Place the cursor in the Row Source box. It's here that you'll type the entries for the Value List, encasing each entry in double quotation marks and separating each entry from the next with a semi-colon (;).

11. Type the following entries in the Row Source box:

"Admin. Asst."; "Administration"; "Carpentry"; "Electrical"

Notice that, as you type the entries into the Row Source line, your input will scroll to the left to allow additional input.

12. With the cursor still in the Row Source line, press Shift F2, to display the Zoom window. This

window makes typing long strings of data easier.

Note: The first time you do this, there may be a slight delay as Access goes about setting up the wizards. After this initial setup, the procedure of displaying the Zoom window will be faster.

13. Place the cursor after the last character.

14. Type the remaining entries for the Value List, beginning with a semi-colon to end the last entry that was typed:

;"Engineering";"Finishing";"Labor/Driver";"Masonry";"Plumbing"

Note: Make sure you have quotation marks around each of the entries, and that each one is separated from the next one with a semi-colon. There should be no semi-colon after the last entry in the list.

15. Once all of the entries for the Value List have been typed, click OK to record the changes and close the Zoom window.

16. Re-save the table design.

17. Switch to Datasheet View.

18. Click in the Specialty field of record 1. A appears, to reflect the presence of a drop-down menu.

19. Click the to display the drop-down menu.

20. From the Value List, choose Electrical to change the specialty for Tony Wilson.

21. Move off the focus to save the record. [Leave the table open.]

Goals for this section:

 Inserting hyperlinks into fields

 Rotating a hyperlink image

Adding Hyperlinks to Fields

A hyperlink is a pointer from one object to another. The Hyperlink data type lets you store a simple or complex link to a file or document outside your database. This link pointer can contain a URL that points to a location on the World Wide Web or on a local intranet. The destination object can be a Web page, a picture, an e-mail address, a document, or a different application. A Hyperlink data type is actually a memo field that can contain virtually unlimited number of characters. The link itself can contain up to four parts:

 An optional descriptor that Access displays in the field when you are not editing the link. The descriptor can start with any character other than a pound sign (#) and must have a pound sign as its ending delimiter. If you do not include the descriptor then you must start the link address with a pound sign. For example an optional descriptor: U Mass Lowell ISIS#.

 The link address expressed as either a URL (beginning with a recognized internet protocol name such as http: or ftp: ) or in UNC (Universal Naming Convention) format - a file location expressed as \\server\share\path\file name). If you do not specify the optional descriptor field then Access displays the link address in the field. You must terminate the link address with a pound sign (#). For example: http://www.uml.edu/enrollment/isis/default.aspx#.

 An optional subaddress that specifies a named location (such as a cell range in a Microsoft Excel spreadsheet or a bookmark in a Microsoft Word document) within the file. Separate the subaddress from the Screen tip with a pound sign. If you entered no subaddress, you still must enter the pound sign delimiter if you want to define a Screen Tip. For example: U Mass Lowell ISIS# http://www.uml.edu/enrollment/isis/default.aspx#Course Catalog#

 An optional Screen Tip that appears when you move your mouse pointer over the hyperlink.

When you insert a hyperlink into a field in a table, the hyperlink field will contain the address of the destination object, so that when you click the hyperlink you jump to that address. If the hyperlink object was created in another application that application will automatically be started when you click the hyperlink.

In order to be able to insert a hyperlink into a field, the field must be defined as a Hyperlink Data Type on the Table Design window.

To insert a hyperlink into a field, you do as follows:

1. In Design View, verify that the Data Type for the field is Hyperlink

2. In Datasheet View, display the table in which you wish to insert the hyperlink.

3. Position the cursor in the field in which you want to insert the hyperlink.

4. Right-click and select the Hyperlink option from the shortcut menu, and then the Edit Hyperlink from the next shortcut menu.

5. In the Address: box, specify the path of the file (that is, the drive and folder where the file is located).

6. Click OK. The full path (name and location) of the hyperlink file is displayed in the field.

After the hyperlink has been inserted, if you decide you don't want the full path of the file to be reflected in the field, you can edit the hyperlink text. The procedure for changing the hyperlink label is as follows:

1. Right-click on the hyperlink, to display the shortcut menu.

2. On the Shortcut menu, choose the Hyperlink option.

3. On the Hyperlink secondary menu, select Edit Hyperlink…:

When the Edit Hyperlink window opens, you'll be able to change the contents of the Text to display box.

4. Click to place the cursor in the Text to display: box, and edit the contents to suit your

preferences.

5. Click OK.

For the Home Tech Repair database, we have three scanned images of fireplace plans and bay windows. All of these images have been created as .gif files, and can be added to the Drawing field of tblWorkOrders for those records that have to do with fireplace and bay window work. The gif files are found in the Blackboard Data Class Data File /Class gif files. You will want to download these files to your Data folder.

Hands-On Activity: Inserting Hyperlinks into Fields

Before beginning: The Home Tech Repair database file is open with the tblEmployeeHRData table displayed in Datasheet View. Copy the three gif files from the Class gif files folder in the Class Data Files folder to your computer.

1. Close the tblEmployeeHRData table.

2. Display the tblWorkOrders table in Datasheet View. Currently the table has no records.

3. Enter the following data into the table for record 1, using the Tab key to move the cursor from field to field in the table:

Field Data for Record 1

Work Order # 001

Customer ID 1032

Bid Number 98-101

Start Date 5/28/04

Completion Date 5/30/04

Supervisor 10

Helper 17

Material Cost 500.50

Labor Cost 750

Description New fireplace

Drawing

4. Move the cursor to the Drawing field.

5. Right click and choose Hyperlink followed by Edit Hyperlink from the shortcut menu. The Insert Hyperlink dialog box is displayed so that you may indicate the full path of the file being linked to this field.

6. In the Look in: text box, specify the folder where you're storing your class data files.

7. Select the file named Fireplace.gif.

8. Click OK. Initially, the complete path of the hyperlink is displayed in the field. However, we

will be able to edit this label to make it more readable and user-friendly.

9. Press the Tab key, to move off the focus and save the record.

10. Put the mouse pointer on the hyperlink in the Drawing field. The mouse pointer appears as a

hand with a pointing finger, which is indicative of hyperlinks.

11. Click on the hyperlink, to open it. Access starts by displaying an Alert Box warning you that opening hyperlinks might harm your computer, particularly if you don't know where they're from, and asks you if you want to continue.

12. Click Yes, to continue.

The fireplace drawing is displayed in the Microsoft Photo Editor window or your Internet Explorer window.

13. Close the window.

14. Right-click on the hyperlink in record 1, to display the shortcut menu.

15. On the shortcut menu, select Hyperlink. When you do, a secondary menu of options is displayed. These options will enable you to do such things as Open, Copy, Add the address to your Favorites, or Remove the hyperlink.

Figure 34: Secondary Shortcut Menu

16. On the Hyperlink secondary menu, observe the Edit Hyperlink… option.

This option enables you to edit the path of the hyperlink so that another file is accessed instead of the one that is currently opened when the hyperlink is clicked, or to edit just the label that appears in the field. I think you will agree that displaying the full path of the hyperlink in the field isn't very attractive, nor is it very user-friendly, since usually all you will see will be the folder hierarchy in which the file is stored, not the filename itself. As a result, it will be difficult for users of this table to know at a glance what's in the hyperlink field for each record. We're going to edit the label to eliminate the path (which isn't useful or necessary) and make the field display in a cleaner form.

17. With the Edit Hyperlink window displayed, click to place the cursor in the Text to display:

box. Your objective is to place the cursor inside the current path, so that you're able to edit its contents. Once the cursor is in the text box, you can use the Home key to move it to the beginning of the line, or End to move to the end.

18. Press the Home key to move the cursor to the beginning of the line. Then use the Delete key to delete all characters except the name (Fireplace.gif).

19. Click OK. Now you should be viewing only Fireplace.gif in the hyperlink field of record 1.

20. Click in the hyperlink to display the fireplace graphic, clicking Yes to confirm that you want to continue. The hyperlink should open. Editing the label of a hyperlink has no bearing whatsoever on the functioning of the link.

21. Close the window.

22. Using the same methods described above, add the next two records to tblWorkOrders:

Field Record 2 Record 3

Work Order # 002 003

Customer ID 1033 1034

Bid Number 98-103 98-105

Start Date 9/15/04 1/27/04

Completion Date 9/18/04 2/2/04

Supervisor 12 14

Helper 18 19

Material Cost 1,155.00 850.50

Labor Cost 840.00 126.00

Description Bay window Bay window 2

Drawing (Leave blank) (Leave blank)

23. Place the cursor in the Drawing field for record 2, and insert a hyperlink to the .gif file named Bay window.

24. In the Drawing field of record 3, insert a hyperlink to the .gif file named bay 2.

25. For both hyperlinks, change the labels so that only the file name (and not the full path) is reflected.

Notice the difference in the color-coding of the three hyperlinks. Hyperlinks display in blue until they have been opened, at which point they display in purple.

26. Test the Bay Window and Bay 2 hyperlinks, to make sure they work. Once you have looked at all of the graphics, close all of the windows in which the graphics are displayed but leave the tblWorkOrders table open.

You have undoubtedly noticed that the Bay 2 hyperlink displays the image upside down. This was done deliberately so we can talk about how to rotate images, which is next on our agenda.

Rotating a Graphic

If a hyperlink graphic doesn't display correctly, it may have been scanned incorrectly and therefore need to be rotated or inverted.

One way to rotate a graphic is:

1. Display the graphic in the Microsoft Photo Editor window.

2. Click either the or icon until you see the desired orientation.

3. Click File…Save.

Another way is to:

1. Click on the Edit Pictures… option.

2. Select the Rotate & Flip option.

3. Select desired action.

Note: If you don't have the Microsoft Photo Editor installed on your computer, the following won't be available to you. If that's the case, don't worry about it. Just skip these steps related to rotating a hyperlink image.

Hands-On Activity: Rotating a Hyperlink Image

Before beginning: The tblWorkOrders table is displayed in Datasheet View. Three hyperlinks have been created in the Drawing field of records 1, 2 and 3. The Fireplace.gif file is reflected as a hyperlink for record 1, and bay window.gif is the hyperlink for record 2. The Bay 2.gif file is reflected as the hyperlink in the Drawing field of record 3.

1. Click on the Bay 2 hyperlink in record 3, to display the drawing in the Microsoft Photo Editor

application. (Click Yes to confirm that you want to continue.)

2. Click on the Edit Pictures… option.

3. Select the Rotate & Flip option.

When you do, the Rotate dialog box is displayed. Here, you can choose the direction and degree of the rotation of the image that's displayed in the currently active window.

4. Rotate until the picture is in the desired position.

5. Click the Save button on the toolbar, to re-save the image (or use the Ctrl S keyboard

shortcut).

6. Close the Microsoft Photo Editor window.

7. Re-save and close tblWorkOrders.

Assignment for Week 3

This week you'll have a few things to do. Review chapters 1-3 of the textbook. Analyze 2 database design situations This week you're going to start analyzing some database situations, to make decisions as to how you would set them up. This is the type of work that we need to do all the time in Access looking at a situation that demands a database, and then deciding what needs to go into the database and how the fields need to be distributed in order to accomplish the objectives of the database. Take a look at the following two situations, and decide how you would set these databases up. What would your tables be called, and which fields would go into each table? Use the Leszynski Naming Convention for all object names. Which fields would be defined as the Primary key field in each table? Which fields would be defined as Foreign Key fields? What type of relationship would you create between the tables? Use either Microsoft Word or Excel to indicate how the different tables would be defined.For example, you could do something like the following:

tblEmployees

*strEmpID (primary key field – used in One-to-Many relationship with tblProjects, connecting on the strEmpID foreign key field of tblProjects.) strEmpFirstName strEmpLastName sngHours curPayRate dtmDateOfHire strDept

tblProjects *strProjectNumber (primary key field connecting to the strProjectID foreign key field in tblProjectDetails.) strEmpID (foreign key field connecting to the strEmpID field in tblEmployees.) dtmProjectStartDate dtmProjectEndDate ysnCompleted sngHoursSpent curCostOfProject

Or, you could do it in graphic format, as in the following example that was prepared in Excel:

Regardless of the method you choose to present your database design, you need to focus primarily on two things:

1. How you would divide the fields into different tables so that all tables are fully normalized. This means no redundancy. Think about this one.

2. How you would join the various tables, through relationships, and what type of relationship you propose between tables. Keep in mind the important fact that no single table can (or should) be left unrelated to other tables.

You want to avoid redundancy here, so keep that in mind as you consider the division and distribution of fields.

Database Design Situations

How Would You Set These Databases Up?

Example #1 Situation

You want to keep track of the following employee information:

 Name

 ID#

 Address

 City

 State

 ZIP

 Home phone

 Hire date

 Department code

 Number of hours they work

 Hourly pay rate

 Health Plan (1 = Insurance, 2 = Insurance with Dental, 3 = Single HMO, and 4 = Family HMO)

 Weekly cost of health plan

 Parking lot to which the employee is assigned

 Parking lot rate

What Would You Do?

1. How would you divide these fields in order to avoid redundancy? Lay out the different tables that you would propose creating, with the fields that would be in each of the tables. Be sure to use the Leszynski Naming Convention for all object names.

2. Which fields would be defined as the Primary and Foreign key fields in each table?

3. What type of relationship would you create between the tables? Which fields would be joined to which other fields?

Example #2

Situation You work in a Veterinarian's office and need to organize the data on pets that you treat. In your database, you want to keep track of the following information: Customer information:

 Name

 Address

 City

 State

 ZIP

 Phone #

 Year of first visit

 Date of last visit

 Discount

 Outstanding balance

Pet information:

 Name

 Type of animal

 Breed

 Date of birth

 Gender

 Neutered/Spayed?

 Length of animal

 Weight of animal

 Last visit date

 Current vaccinations

 Picture of animal

 Comments on animal

 Date of visit

 Follow-up visit date

 Total amount billed for visit

 Payment type

 Date paid

 Amount paid

 Taxable?

 Tax Rate

 Invoiced?

Details on the individual visits:

 Type of visit

 Treatment code

 Medication code and price

Details on all medications and visits:

 Medication name

 Medication price

 Treatment name

 Treatment price

What Would You Do?

1. How would you divide the data? Again, lay out the different tables that you would propose creating, with the fields that would be in each of the tables. Be sure to use the Leszynski Naming Convention for all object names.

2. Which fields would be defined as the Primary and Foreign key fields in each table?

3. What type of relationship would you create between the tables? Which fields would be joined to which other fields?

What to Do With Your Answers: Now that you've thought about how you would design these databases and you've addressed the issues of data division and distribution, normalization of tables, key fields, and relationships, I'd like you to provide me with a summary of your decisions. Lay it all out using either of the two methods described above (i.e., text or graphic, Word or Excel) and put your finished assignment in the Assignment Link in Blackboard. (I do not want this done in Access).Just make sure it is crystal clear to me how you would divide the data for these databases.

This assignment is due by midnight Eastern Standard Time on Wednesday, September 23, 2015.