ACCESS DB homework

week_4_pdf1.pdf

Home >Computer Science homework help >ACCESS DB homework

Week 4 What We'll Be Working on This Week Besides typing data into fields, there are a number of excellent ways in which you can populate your Access tables. You can use the Paste Append command to:

 copy records from one table to another, either within a single database file or from one database to another

 copy records within the same table You can also import Excel data into an Access table, The techniques for transferring data from one table to another or from one database to another will be invaluable to you as you go about creating your database files, because they will represent tremendous time savings. Two other excellent options that are available to you in Access are the ability to create drop-down lists from which the user may simply select an item rather than typing. In this way, many typing errors are avoided, not to mention the added benefit of tremendous time savings.

 Value Lists are extremely easy to create, and display static drop-down lists of items for the user to select.

 Lookup Lists display a drop-down list where the contents of the list are looked up in another table and change when the source list changes.

Goals:  Using the Paste Append command to copy records from one table to another page 2

 Using the Paste Append command to copy records within a table page 7

 Importing data from Excel into an Access table page 12

 Copying the Excel data into an Access table page 21

 Creating a Value List with the Wizard page 24

 Creating a Lookup List with the Wizard page 33

 Modifying a Lookup List page 43

 Changing Relationships page 48

 Changing the data to test the Lookup field page 54

 Testing Referential Integrity page 56 This is where we're headed this week. So here we go.

Goals for this section:

 Using the Paste Append command to copy records from one table to another

 Using the Paste Append command to copy records within a table

Using the Paste Append Command to Copy Records between Tables

So far, we've seen how you can copy the structure of a table and use it as the basis for another table. But you can also copy the records from one table to another. This could be especially valuable if you have several people creating tables with the same fields and different records, because it would make much more sense to keep all the records in a single table. This procedure can also be extremely beneficial for archiving old or inactive records.

The Paste Append command is an invaluable tool for copying records in the following three ways:

 Within a table

 From one table to another in the same database

 From one table in one database to another table in a different database

When you paste rows into another table, the rows you are adding must satisfy the validation rules of the receiving table, and the primary key values (if any) must be unique. If any validation fails, Access shows you an error message and cancels the paste. You cannot paste copies of entire records into the same table if the table has a primary key other than AutoNumber data type. You will get a duplicate primary key value error if you try to do this. When the primary key is AutoNumber, Access generates new primary key values for you. A word of caution: Before you use this command to copy records from one table to another, make absolutely certain that the fields in the target table have the same properties as the source fields, and that they're arranged in the same order. For example, if the fields are out of order, you may end up trying to paste Text or Yes/No data into a Date/Time field, which will result in errors. Also, before you can import data into an Access table you will need to remove Input Masks. Once they have been removed and the data have been imported, you can replace the Input Masks on the fields.

The procedure for copying records from one table to another is as follows: 1. In the source database, open the table from which you want to copy records.

2. Select the records to be copied. In the case of a contiguous group of records, click the Row Selector to the left of the first record in the group, to select the row. Hold down the Shift key and click the Row Selector to the left of the last record in the group. All of the records between those two points will be selected.

To select all of the records, choose the Select /Select All in the Find group

or use the Ctrl a keyboard shortcut.

3. Choose the Copy command or use the Ctrl c keyboard shortcut to copy the contents of the rows to the Clipboard. You can also click the Cut icon or press Ctrl x to delete the rows and copy them to the Clipboard.

4. Open the target database file. Access will usually ask you, in closing the previously open database, if you want to save the data on the Clipboard for subsequent use. You must click Yes in answer to this question, so the copied records aren't removed from the Clipboard.

5. Open the table into which you want to paste the copied records.

6. Choose the Paste Append command. Access tells you how many records you're about to paste, and asks if you're sure.

7. Click Yes, to paste the records.

Hands-On Activity: Using the Paste Append command to copy records from one table to another

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

1. In the Home Tech Repair database, open the tblEmployeeHRData table (in Design View), and observe the field order. We need to make absolutely certain that the records we're copying have the same Field Properties and that the fields are arranged in the same order. The fields in this table should be in the following order:

If any of the fields in your table are not in this order, move them around now. When you transfer data from one object to another, it will be imperative that you keep close tabs on which fields are in the source and target tables, as well as the order in which the fields are listed. The fields have got to match not only in order, but above all in Data Type. If you try to import the data from one field into another field that has an incompatible data type, you will hit a wall. For instance, trying to import Text into a Date/Time field will not work, and Access won't let you do it.

2. Open the SOURCE database from your HomeTechRepair.accdb by either clicking the File tab and selecting the SOURCE.accdb or browse to where you are keeping your data files and select SOURCE.accdb. When you open SOURCE.accdb this way, you will have closed your HomeTechRepair.accdb. However, you can keep your HomeTechRepair.accdb open and using Windows Explorer locate SOURCE.accdb and open it. If you do it this way, you will have 2 instances of Access running.

3. In the SOURCE.accdb database file, open the table named tblEmployeeHRSource in Datasheet View.

4. Observe the fields: All of the fields are in the correct order, with the exception of the Pager field. This field is not in our target table, so we have to make a decision here: Either we add the Pager field to tblEmployeeHRData in our Home Tech Repair database, or we eliminate the field from our SOURCE database.

We'll go with the second option, since only a few records have data in the Pager field.

5. Display the tblEmployeeHRSource table (in the SOURCE.accdb database file) in Design View.

6. Click the Row Selector to the left of the strPager field, to select the field.

7. On the Home / Records toolbar, click Delete.

Because this table contains data, Access displays an Alert Box.

8. Click Yes, to continue with the deletion.

9. Re-save the table.

10. Switch to Datasheet View.

11. Press Ctrl a, to select all of the records in the table.

12. Choose either the Copy toolbar button or the Ctrl c keyboard shortcut.

13. Close the table (Access displays another Alert Box, asking you if you want the copied data to remain on the Clipboard).

You must click Yes in answer to this question in order to be able to paste the records into the target table.

14. Click Yes to tell Access that the Clipboard is to remain intact.

15. Open your Home Tech Repair database file.

16. Open the tblEmployeeHRData table in Datasheet View. This table currently contains just the two records that we entered previously.

17. Choose the Paste Append command from the Home / Clipboard / Paste / Paste Append

Access loads the copied records into the table, and displays an Alert Box telling you that 22 records are about to be pasted.

18. Click Yes, to paste the copied records into the table. The tblEmployeeHRData table now

contains 24 records. Note: The Source database was created in Access 2010. There was no problem when copying the records even though the Text Data Type fields were defined as Text data instead of Short Text as is now the 2013 Data Type.

On Your Own

In your Home Tech Repair database, the tblWorkOrders table currently has only three records. Use the technique indicated above to append the 12 records in the tblMoreWorkOrders in the SOURCE.accdb database file to the tblWorkOrders in your Home Tech Repair db.

When finished, tblWorkOrders should have 15 records.

Close tblWorkOrders when you have finished copying the records into the table.

Using the Paste Append command to copy data within a table

If you need to enter a record in a table that has similar data to that of another record, you can copy the record and then change a limited number of fields instead of re-entering the same data again. If you use this technique to copy records, make sure the data in the primary key field(s) of the table remain unique.

To copy a record within the same table, simply select the record to be copied, choose the Copy command, and then choose Paste Append command. Because the Paste Append command adds

the copied record to the end of the table, it doesn't matter which record is active when you choose this command.

Hands-On Activity: Using the Paste Append command to copy records within a table Before beginning: Your Home Tech Repair database file is open.

Our hypothetical situation is this: Tony Wilson's twin brother, Terry, has been hired today. He has a different employee ID number, Social Security number, and hire date than Tony, but all the other information is the same as in Tony's record (record 1). Rather than creating the new record and duplicating many pieces of data, we'll copy Tony's record and then make the necessary changes to the new record.

1. Open the tblEmployeeHRData table in Datasheet View.

2. Click the Row Selector for record 1, to select the record.

3. Choose the toolbar Copy command (or use the keyboard shortcut).

4. Choose the Paste Append command. Access adds the copied record to the end of the table.

5. For the time being, leave the contents of the Emp ID field unchanged.

6. Press Tab, to move to the Emp First Name field and change the name to Terry.

7. Press Tab three times to move to the SS# field, and change the number to 304-00-1368.

8. Press Tab five times to move to the Hired field, and press Ctrl ; to enter today's date in the field.

9. Move the cursor off the focus, to try to save the new record. Access displays an Alert Box telling you that you can't do this because you have a duplicate value in the primary key field.

10. Click OK, to close the Alert Box.

11. Change the Emp ID for Terry from 10 to 34.

12. Move off the focus, to save the record. Because you've entered a unique value in the primary key field, you're able to save the record.

13. Close the tblEmployeeHRData table.

14. Using the same methods explained above, Copy and Paste Append the 25 records from tblEmployeePersonalSource in SOURCE.accdb to tblEmployeePersonal in your Home Tech Repair file.

[Be sure to say Yes to the question regarding saving the data to the Clipboard.]

Hint: Depending on how you created your fields in your Home Tech Repair file, you may have to do some trouble shooting with the database so that it conforms to the records in the SOURCE.accdb. It is easier to change fields in the database when there are no records.

15. Once the data have been appended to the tblEmployeePersonal table, add the following formatting:

 To the Zip field, add 00000 as the Format Property. This will ensure that all Zip codes contain 5 digits.

 To the Home Phone field, add an Input Mask so the phone numbers are correctly displayed.

Your finished tblEmployeePersonal table should look like the following. Note: Your Zip code field may be different depending on your trouble shooting tactics.

[Note: Your Record Entry Dates will, of course, be different from the ones in this illustration.]

16. Once the 25 records have been added to tblEmployeePersonal, re-save and close the table.

Note: I would just like to stop for a moment to provide some insight as to what just happened with the transfer of the 25 records from the table in the SOURCE database into the tblEmployeePersonal table of your Home Tech Repair database. I have, obviously, set these files up for you. I made sure the fields in the two tables were in the same order, but you must verify that each of the sets of matching fields has the same Data Type and Field Size property. This is essential. If you have fields in different orders, or if you're trying to Copy and Paste Append Text data into a Number field, you will encounter problems. So you have got to check this out thoroughly before you try to do this. If down the road you attempt to do a Paste Append and you get an Alert Box indicating that errors have occurred, cancel the procedure and go back to examine the fields in the two tables again. You have got to straighten out these issues before such a transfer will work properly.

So Where Are We?

Before we move on to the next lessons, I would just like to take a brief moment to take stock of what we currently have in our database.

The two tables that contain employee information (tblEmployeeHRData and tblEmployeePersonal) both have 25 records in them. This is going to be important when it comes to defining the One-to-One relationship between these two tables, because in that type of relationship the two tables use the same primary key. Exactly one record exists in the second table for each record in the first table.

Sometimes security reasons require a table to be split into two related tables. For example, anyone in the company can look in the Employee table and find the employee’s office number, department assignment, or telephone extension. However, only a few people need to have access to the employee’s salary, Social Security Number, performance review, marital status. Both tables use the same unique identifier to identify each employee.

Our tblBidData table currently has 25 records in it, and tblWorkOrders has 15 records.

The tblCustomer table, which we created with the Table Wizard, has no records in it yet.

Please make sure your tables agree with these descriptions. If they don't, you will potentially have difficulty with subsequent lessons that will assume you have all of the necessary fields and records in all of the tables, and that all of the properties have been set correctly.

Goals for this section:

 Importing data from Excel into an Access table

 Transferring the Excel data to tblCustomer

Importing Excel Data into an Access Table

A lot of foreign data comes in the form of an Excel file. Unfortunately, Excel and Access do not store data in the same way. That is why importing Excel data into Access seldom produces the exact table structure you ultimately need. Still, it is a good idea to let the import process properly arrange as much of the data as possible. If you or others already have your lists started in Excel, you can import the data from your worksheets into an Access database, rather than starting from scratch. You can specify a portion of a spreadsheet or the entire spreadsheet to import onto a new table or to append to an existing table. Importing a single sheet or range of data is straight forward. You import the entire sheet or range into a single table and then normalize (if necessary). If the Excel file contains more than one sheet, you must decide whether all that data belongs in separate tables or in one inclusive table. When you import data into Access from Excel, you have the option of linking the data back to the source application so that any changes that occur there will automatically be reflected in the database. You can also do a static copy, whereby changes made to the Excel document will not be reflected in the Access table. This is the way we're going to do it. Access does a good job of interpreting Excel data that originates in Excel. However, data that originates somewhere else and subsequently finds its way into an Excel file often causes trouble. That is because the data's original format is (most likely) unknown to Access. Access will do its best, but it often fails. A failure is defined as a data type that is converted incorrectly or a value that isn't imported. Incorrectly converted data types are a problem that Access won't identify, so they can be troublesome. Import errors - values that Access can't import- are logged in a special table during the import process. Use this table to find those errors. If there are numerous errors you may have to tweak the Excel data and import again. Or you may choose to manually enter the values yourself, if there are only a few. Before you import from Excel into Access, you need to make sure of the following:

 While the data is still in Excel, delete any empty rows. This may seem unnecessary, but an empty record may contain phantom characters that confuse Access and generate an import error.

 That the data are arranged in the worksheet in a tabular format - that is, columns have column headings that will translate into field names, all data are together in a contiguous range of cells, etc.

 That the fields (that is, the columns) of the Excel worksheet are in the same order as the fields in the Access table.

 That the data in each cell of the worksheet match the data type in the Access table. Remember, you can't import data into a field with which they're not compatible, as in the case of Text data into a Date/Time or Hyperlink field.

 That all Input Masks have been removed from the Access tables. (If you have Input Masks defined for Phone or ZIP Code fields, in particular, this can potentially cause problems during the Import process.)

Access determines the data type for the fields in a new table based on the values it finds in the first few rows of data being imported (excluding the first row if that row contains field names). When you

import a spreadsheet into a new table, Access stores alphanumeric data as the Short Text Data Type with an entry of 255 characters, numeric data as the Number Data Type with the Field Size property set to Double, numeric data with currency formatting as the Currency Data Type, and any date or time data as the Date/Time Data Type. Note: If Access finds a mixture of data in any column in the first few rows, it imports that column as the Short Text data type. In addition, you have the option of creating a new table from the imported data, or appending the data to an existing table. In order to append to an existing Access table, the spreadsheet column headings must be the same as the table field names. As a result, you may find it preferable (and safer) to append to a new table, so that you can check the data before transferring it into the table. Then simply Copy and Paste Append the records from the newly-created table (that is, the Excel data) into your target Access table. If you import data into a new table, Access assigns what it thinks are appropriate data types to those

imported fields. However, it doesn't always make the best decisions. Therefore, you'll need to look at the field properties once the new table has been created, and make the necessary changes.

Tip: I prefer to import Excel data into Access in this way (that is, into a new table), rather than importing directly into an existing table. Because of the requirement that the field names be exactly the same in the Excel worksheet as in the target table, it will take you a very long time to change all of the field names so that they match. And if you miss something, the Import won't work. I have found that it's easiest to just get the Excel data into a new Access table, and then transfer it to another table from within the file using the Paste Append command. This will always work, assuming the fields are in the correct order and the Data Types match.

To import data from an Excel file, you'll use the External Data / Import / Excel. You can also link tables from other Access databases – whether the other databases are local or on a network- and work with the data as if these tables were defined in your current Access database. If you want to work with data stored in another database format supported by Access (dBASE, Paradox, or any SQL database that supports ODBC, including Visual FoxPro), you can link the data instead of importing it.

Hands-On Activity: Importing data from Excel into an Access table

Before beginning: Your Home Tech Repair database file is open. The file currently contains 5 tables: tblBidData, tblCustomer, tblEmployeeHRData, tblEmployeePersonal, and tblWorkOrders. There is also a link to tblEmployees in the Source database. Copy Home Tech Import Customers.xls from the Class Data files to your computer.

1. Launch Microsoft Excel and open the file named Home Tech Import Customers.xls

This file contains only one sheet, which has 39 rows of data. The first row contains labels that will translate into field names in an Access table when we import the contents of the worksheet.

2. Notice the columns in the worksheet:

 Column D contains contact MI information, but that field doesn't exist in our tblCustomer table. The field will have to be added to tblCustomer (between the Contact First Name and the Contact Last Name fields) before we do the importing. Remember, whenever you are transferring data from one table to another, you have got to make absolutely certain that the two locations are compatible with one another. Yes, this takes time, but it is necessary.

 The Contact Title field in the Excel spreadsheet appears between the Contact Last Name and the Billing Address fields, whereas in tblCustomer it is after the First Name field. We'll need to move the field in tblCustomer.

 In tblCustomer, strLastName precedes strFirstName whereas in Excel, Contact First Name appears to the left of Contact Last Name. The fields in tblCustomer will need to be moved.

 Note that both in the Customer ID and Zip columns there is a green triangle in the upper left corner. A green triangle in the upper-left corner of a cell indicates an error in the formula in the cell.

 Note that when you click on a ZIP code in the Excel spreadsheet, you

see . When your mouse hovers over the exclamation point you will see a message that tells you the number in the field is formatted as text. Be sure the field in your Access database is a Short Text Data type. You can change the data type later (you will get a warning message)

 The Excel spreadsheet has a blank column between the ZIP and Phone columns. That blank column will have to be deleted. Access will bring the data into the table in exactly the same order in which the fields are listed in the source. Therefore, if there's a blank column in the source (as there is in this worksheet), the field data will be shifted upon transfer, and you will probably hit a wall when there are incompatible data types. Taking the time to scrutinize your source and target objects beforehand will eliminate a lot of heartache and minimize your frustration later on.

 The tblCustomer table has a Fax field, which is missing in the Excel file. Since it's missing in the Excel worksheet, there probably aren't any data for the field, so we'll delete it from tblCustomer.

3. In the Excel worksheet, select column K and delete it. (Click the letter K in the column heading to

select the column, and then choose the Home / Cells / Delete Column icon. You can also right- click on the heading of the selected column and choose the Delete command from the shortcut menu.)

4. Re-save and close the Excel file. 5. In your Home Tech Repair file, display the tblCustomer table in Design View. We now need to

get this table into shape so it can receive the Excel data successfully. 6. Move strFirstName between strCompanyName and strLastName. 7. Insert a new field named strMI between the strFirstName and the strLastName fields.

8. Assign a Field Size of 2 to the field, and add a caption of MI.

9. Make sure all fields have captions.

10. Be sure that the ZipCode field is a Short Text Data Type. This is because the Zip code field in the Excel spreadsheet was text.

11. Be sure that the Zip Code field is between the State and Contact Phone fields.

12. Decrease the size of some of the Short Text fields so that they're not excessively large. If you leave 50 as the field size of a field that will never hold more than 5 or 10 characters, for example, you will unnecessarily bloat your database. Here, the City field definitely doesn't require a size of 255, and can comfortably be changed to 20. Likewise, the Title field will only hold a maximum of three or four characters (for the titles of Mr., Ms., etc.), and therefore can be reduced substantially in size.

13. Delete the strContactFax field.

14. Move the strContactTitle field to between the strLastName and the strBillingAddress fields.

15. Re-save the table and close it.

At this point we're ready to import the Excel data. We'll import them to a new table, look at the fields in the new table to make sure they match those of our tblCustomer table, and then copy the records from the new table into tblCustomer.

16. Select the Excel icon from External Data / Import & Link.

17. Browse to the location of the Excel file Home Tech Import.xls. Be sure that Import the source data

into a new table in the current database is selected and click OK.

18. The Import Spreadsheet Wizard screen is displayed.

19. On the first screen of the Import Spreadsheet Wizard, select the First Row Contains Column

Headings option. Access needs to know that the data in row 1 are the field names.

20. Click Next>, to move to the second screen of the Wizard.

Here, you need to specify information about each field. The first field is the Customer ID. We want this to be the primary key field so be sure that you change the Indexed Option to Yes (No Duplicates). Click Next.

21. The next screen has inserted a new field (ID) that can be used as the primary key field, but you

want to use the Customer ID field as the primary key field. Select Choose my own primary key option. If you know you have a unique identifier in the table, you should use it as the primary key field rather than allowing Access to do it for you. When Access assigns the primary key field in a table, it adds a new field to the table and makes the field an AutoNumber field with sequential values starting at 1. On the drop-down list to the right of the Choose my own primary key option, ensure that the Customer ID field is specified as the primary key field. This field contains the unique value that identifies each customer.

Note: This field is displayed in the Choose my own primary key text box only because it's the first field in the table. Access hasn't made any decisions here regarding which field is the most appropriate one to serve as the primary key. Make sure you always check what's listed here before moving on, because if you have selected the wrong field as the primary key, your relationships won't be able to be established correctly.

22. Click Next.

23. On the final Wizard screen, specify as the name of the new table tblExcelCustomers,

You can import directly into an existing table, but my experience is that bringing the Excel data into a new table and then transferring it into an existing table works with fewer glitches. We'll

transfer the worksheet data into a new table and then, once we're sure the fields are as we want them, we'll Copy and Paste Append the data from that table into our tblCustomer table. Or, we can simply rename the imported table to tblCustomer, and delete the previous empty table. The disadvantage of this method, however, is that if you have already set field properties (captions, formatting, etc.) you will lose all of that. But you will need to judge for yourself with your own database which method will work best for you.

24. Click Finish. The Import Spreadsheet Wizard tells you it has finished importing the data.

25. Access displays an option to save your import steps in case you want to perform the exact import

procedure again in the future. You can execute saved imports by clicking the Saved Imports button in the Import group of the External Data tab on the Ribbon. Click Close.

Copying the Excel Data into the Access Table

Once the Excel data have been imported, you should open and look at the table, making all necessary changes to the Data Type of the fields. The Import Spreadsheet Wizard will have made decisions for you regarding field size and data type, so you may need to make adjustments to the properties of the imported data, as well. Once you're sure things match, you can use the Copy and Paste Append commands to transfer in the data.

You may be wondering, at this point, why we can't just use the new table that was created with the Import Spreadsheet Wizard rather than the tblCustomer table. The answer: You could do this, since there aren't any records in tblCustomer at this point. However, by so doing you would lose all of the Field properties that you have in the tblCustomer table – all of the field names and sizes, captions, input masks, etc. would be lost. With your own database you'll have to weigh all of these issues in order to decide the optimal way to do things.

Hands-On Activity: Transferring the Excel Data to tblCustomer

Before beginning: Your Home Tech Repair database file is open, with the Database window active. The tblExcelCustomers table has been copied into the database, so that you currently have 6 table objects plus a link to a table in the SOURCE database in the file.

1. Open the tblExcelCustomers table in Datasheet View. 2. Notice the phone numbers. They don't have parentheses, which makes these numbers difficult to

read. This isn't a problem, since if there's an Input Mask applied to the field in tblCustomer, the data will be consistently displayed and very readable. Input Masks may be applied to fields at any time.

3. Switch to Design View. 4. Observe the Field Properties:

 The field names do not follow the Leszynski Naming Convention. Instead, the worksheet column headings were used as the field names.

 All of the Excel fields were defined as Short Text fields, including the Comments field. (You may remember that our Comments field in tblCustomer was defined as a Memo field.) This isn't a problem – Text data can be transferred into Memo fields.

 All of the Short Text fields have a Field Size of 255 assigned to them. This would unnecessarily bloat the database, and if we were to continue using this table we would need to decrease the size of these fields.

 No captions have been assigned to the fields.

 The Format is specified as @. This indicates that the field has been imported.

5. Switch back to Datasheet View.

6. Press Ctrl a to select all of the records. 7. Choose the Copy command.

8. Close the table.

9. Click Yes in answer to Access' question regarding saving the copied data on the Clipboard.

10. Open tblCustomer in Datasheet View.

11. Choose the Home / Paste / Paste Append command. You're told that you're about to paste 39 records.

Note: If there were any problems with the importing, this Information Window would have told you that not all of the data were transferred, and a Paste Errors table would have been created for you to show you exactly which fields caused the problem. Hopefully you haven't encountered any errors.

You may get the following message as a result of some blank records being imported. This can be avoided by just selecting the last record that contains data.

12. Click Yes, to confirm the action.

13. Switch to Design View and observe the field properties:

 The field sizes of the target table, rather than of the source, have been used for each of the fields.

 The contents of the memNotes field (which was defined as a Text field in the source table) have been imported appropriately into the Memo field

14. Apply an Input Mask to the strContactPhone field, so the phone numbers are consistently displayed in the field.

15. Re-save and close the table. Now we can delete the tblExcelCustomers table from the database, since it has served its purpose and is no longer needed.

16. On the Navigation pane, single-click on the tblExcelCustomers table object, to select it.

17. Press the Delete key on the keyboard (or click the Delete button on the toolbar). Access asks if you're sure.

18. Click Yes, to confirm the action.

At this point, you should have five tables in your Home Tech Repair database plus a link to a tblEmployees in the Source database:

tblBidData (with 25 records) tblCustomer (with 39 records) tblEmployeeHRData (with 25 records) tblEmployeePersonal (with 25 records) tblWorkOrders (with 15 records)

If any one of your tables does not have the indicated number of records, go back and get the records into the table. From this point on, all exercises in the lessons will presume that you have the correct number of records. If you don't, your results won't be correct.

Goal for this section:

 Creating a Value List with the Wizard

Overview of Lookup Fields

Lookup fields are an excellent idea for streamlining the database. Because they provide lists of values from which to choose, they will facilitate the data-entry process by saving time and eliminating errors. The lookup feature lets you provide a list of acceptable values for the particular field. When the user selects the value from the list, the lookup feature automatically enters the value in the field of the current record.

There are two types of Lookup fields that you can create in Access:

 Value Lists

This is the type of Lookup field we created earlier with the Specialty field of the tblEmployeeHRData table. You may remember that we typed the entries for the list into the Display Control box on the Field Properties window. The field got its value from the list that you created. A Value List is best used when the list is short and when the contents of the list won't change very often. Later on we will be creating a Value List using the Wizard, so you can see another method for doing it.

 Lookup Lists

These Lookup fields get their values from an existing table or query. The main advantage is that the two objects (that is, the source and the target) are related, so as the source list changes, the values in the Lookup List change, as well. The important thing to keep in mind about this type of lookup is that the source table must reside in the same database as the target in order to work correctly. If Access is unable to find the source object, it will generate error messages.

Creating Value Lists with the Wizard

Although you can type the values for a Value List directly into the Field Properties window, as we did previously with the Specialty field of tblEmployeeHRData, the Wizard can also help you create a Value List for a field.

The procedure is as follows:

1. Open the table containing the field for which you want to create the Value List, and display

the table in Design View.

2. Display the contents of the Data Type drop-down menu for the field, and choose Lookup Wizard… as the type.

3. On the first Lookup Wizard window, choose I will type in the values that I want option.

Figure 19: Lookup Wizard Screen

4. Click Next>.

Note: If you press Enter, you will be selecting the Next> button and will therefore move to the next Wizard screen. If this happens, click the <Back button to return to your list, and continue entering items.

5. Once all of the items have been added to the list, click Next>.

6. On the final Wizard screen, specify the label you want for the lookup column, or leave the field name as the label.

Note: You are actually changing the field name here, not just attaching a label to the field.

7. Click Finish.

Hands-On Activity: Creating a Value List with the Wizard

Before beginning: Your Home Tech Repair database file is open. No objects are currently open.

We'll create a Value List for the strTitle field of the tblCustomer table, so users can select Mr., Ms., Dr., etc. from the list instead of typing in each entry.

1. Open the tblCustomer table in Design View.

2. Click in the strContactTitle field, to activate that field and make the field properties available.

3. Display the Data Type drop-down menu for the strContactTitle field and choose Lookup Wizard…

When you do, the Lookup Wizard starts automatically. You will simply move through the screens that the Wizard makes available to you, responding to each of the requests for information, and you will end up with the desired Value List.

4. On the first Wizard screen, choose I will type in the values that I want option.

5. Click Next>. On the next Wizard screen, you need to enter the values you want displayed on the

list. This screen is displayed because of the option you selected on the previous screen. Had you chosen the I want the lookup column to look up the values in a table or query option, you would have been presented with all of the existing objects in the database, so that you could select the source object for the lookup. In this case, we're going to enter the data for the Value List, and therefore need to be on this screen.

6. Leave 1 indicated as the number of columns.

7. Click to place the cursor in the first cell of the Col1 column.

8. Type Mr. as the first entry for the Value List. The Pencil icon appears next to the row, to indicate that the entry hasn't been saved.

9. Press Tab, to save the first entry and move the cursor to the next cell.

10. Type Ms. as the next entry in the list, and press Tab.

11. Press Enter. This does the same thing as clicking the Next> button. It does not move the cursor down, as you might expect (particularly if you are an Excel user), but rather moves to the next screen.

12. Click the <Back button, to return to the list of titles.

13. Click to place the cursor in the next empty field cell, and enter Dr. as the next entry in the list. Your Value List should look similar to the following:

14. Click Next>, to advance to the final Wizard screen.

15. On the final Wizard screen, you're asked what label you want for the lookup column. We'll leave the field name as the label.

16. Click Finish.

17. Leave the strContactTitle field selected and activate the Lookup tab of the Field Properties pane.

18. Observe the Row Source Type and Row Source settings: The Wizard has selected Value List as the Row Source Type and it entered the quotation marks and semi-colons between each item in the list (which you manually did last week).

19. Re-save the table.

20. Switch to Datasheet View and click in any record of the Job Title field, to see the Value List.

21. Close the table.

On Your Own

In the tblWorkOrders table, create a Value List for the strHelper field. Since this is not a Lookup, you can simply modify the strHelper field rather than adding a new field to the table. Use the Wizard to create the value list, and include the following in the list:

Dobbins, David Culhaney, Dawn Hobbs, Calvin Chu, Mary Jamison, George Tomaszewski, John

Once the list has been created, assign the following people to the first few records of the table:

Records 1, 9, and 13  Culhaney, Dawn Records 2, 4, 8, 14, & 15  Hobbs, Calvin Record 3 & 5  Tomaszewski, John Records 6, 7, & 10  Chu, Mary Record 11  Dobbins, David Record 12  Jamison, George

Your finished table should look as follows:

When you have finished entering the names into the Helper field using the Value List, re-save and close the tblWorkOrders table.

Goals for this section:  Creating a Lookup List

 Modifying a Lookup List

How Can Lookup Lists Help Us in Our Home Tech Repair Database?

Unlike Value Lists, whose contents are stored in the table in which the value list is displayed, Lookup Lists get their data from another table or query, and will be dynamically linked to that source object. By doing this, any changes made to the source will automatically be reflected in the target. As a result of the fact that the data will be looked up, you need to decide up-front what the parent table will be for the Lookup - that is, where the data will reside and be looked up. Let's talk about our needs in the Home Tech Repair database. In tblWorkOrders, we have a series of ID numbers entered in the Supervisor field, corresponding to the ID numbers of the employees who are supervising those work orders. It's unrealistic to expect that users are going to remember that ID 12 corresponds to Rick O'Brien and ID 10 belongs to Tony Wilson. And the last thing you want users to have to do is reference a printed list of employee names and ID numbers, just to know which number needs to be entered as the supervisor for which work order. The solution is to create a Lookup List in a Supervisor Lookup field in tblWorkOrders. Although you could convert your existing strSupervisor field to a Lookup List, I suggest you create a new field with the ID number and name listed in the drop-down list of available values in the field. When you first create the field, it will be empty. But you'll be able to see the employee ID in the strSupervisor field and then choose the correct value for the Supervisor Lookup field for each record. Otherwise, you might not remember which employees have already been assigned to the existing work orders. Obviously, if this had been done before any data were entered in the table, we wouldn't need to lay out our safety net here and we wouldn't have multiple fields for the Supervisor information.

Remember my statement about the need to plan, plan, plan? This is an example of how important that can be. We're going to be back-peddling a little bit here to get these names into the table. Had we had the table completely set up from the start, it would have been much easier getting the data into the records.

So this is what's going to happen in our Supervisor Lookup field:

 In the field there will be a drop-down list containing the Employee ID and the Employee Name. This list of ID numbers and names will be looked up by Access in tblEmployeeHRData.

 When users want to enter the Supervisor for a work order, they'll display the drop-down list and select a name and ID from the list.

Access will store just the ID number in the field, however, while displaying the ID number with the name in the list if you do not hide the primary key field.

 The lookup table (in this case, tblEmployeeHRData) will be the parent table of the Lookup. Its primary key field will be the linking field.

 The lookup field of tblWorkOrders will become the foreign key for the lookup relationship.

Creating Lookup Lists with the Wizard

The procedure for creating a Lookup List is as follows:

1. In the table where you want to access the list, add a field with Lookup Wizard as the Data Type.

2. On the first screen of the Lookup Wizard, leave the I want the lookup column to look up the values in a table or query option selected.

3. Click Next>

4. On the second screen of the Lookup Wizard, select the name of the table or query where the data for the list reside.

5. Click Next>.

6. On the third screen of the Lookup Wizard, you need to select the fields that contain the values to be listed in the lookup column, and place them in the Selected Fields list.

For example, if you wanted the employee ID number and last name to be listed, you would select the strEmployeeID and strLastName fields.

You can either select the field and click the > button to move the field to the Selected Fields list, or double-click the field.

7. Once all of the desired fields have been placed in the Selected Fields list, click Next>.

The next screen shows you how the field values will look. Scroll down the list and make sure the column is wide enough to display all of the entries. If not, widen the column by dragging the right edge of the column heading area, as you would do in Excel to change column width.

8. Decide whether or not you want to see the contents of the key field in the lookup List.

9. Click Next>.

10. Select the name of the field that will store the value for the field.

11. Click Next>.

12. If desired, change the label of the Lookup field.

13. Click Finish. Access will ask you if you want to save the table.

14. Click Yes.

Hands-On Activity: Creating a Lookup List for the Supervisor field

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

1. Display the tblWorkOrders table in Design View.

2. Insert a new field after the dtmCompletionDate field, called strSupervisorLookup.

3. Choose Lookup Wizard… as the Data Type.

The Lookup Wizard automatically starts.

4. Leave I want the lookup column to look up the values in a table or query option selected.

5. Click Next>.

Because you indicated that the Value List would come from a lookup, the Lookup Wizard asks you to indicate the name of the table or query where the values are located.

6. Choose tblEmployeeHRData as the name of the source object. This is where Access will look for the data to display on this list. Obviously, you have to make absolutely certain that you're selecting the correct object here.

7. Click Next>.

8. On the next screen of the Lookup Wizard, you need to select the fields that contain the values to be listed in the Lookup column. Your objective is to place the fields you want included in the list into the Selected Fields window.

Leave the strEmployeeID field selected, and click the > button. This moves the field to the Selected Fields pane.

9. Double-click the strLastName field, to move it to the Selected Fields pane.

Note: You need to list the fields here in the order in which you want them to be displayed in the drop-down Value List when it's displayed in the field of a given record.

10. Click Next>. On the next Wizard screen, you're given the opportunity to sort the contents of the list. It might make it easier to locate a supervisor if the names are sorted alphabetically.

11. Click to place the cursor in the first Sort text box, so that the  is displayed for that option.

12. From the drop-down list of field options, choose strLastName.

13. Click Next>, to move to the next Wizard screen.

You now see how the Lookup List is going to look in the field.

14. Deselect the Hide key column (recommended) option, to display the Emp ID's in the field.

Access recommends that we leave this field hidden, and if there weren't any data in this field, this would make sense. But because we already have 15 records in this table, we need to make sure we're selecting the correct person as the Supervisor for each work order. Again, this is a safety net that wouldn't be necessary if we had planned the table correctly from the beginning.

15. Narrow the Emp ID column, to eliminate some of the unnecessary space.

[Position the mouse pointer on the column heading separator between the Emp ID and Emp Last Name headings, so that the mouse pointer changes to a horizontal double-edged arrow with a vertical line running through it. Then drag to the left, to narrow the Emp ID column. This is the same technique we use in Excel for changing column width.]

16. Once the column width has been changed, click Next>.

17. On the next Wizard screen, you're being asked to choose the field that uniquely identifies the data in the list. This is the field that contains the value that will be stored in the Value List.

Therefore, leave the strEmployeeID field selected on this screen.

Note: This is important. You need this value to be stored so it can continue to match the Emp ID field in tblEmployeeHRData, thereby preserving the relationship between the two tables.

18. Click Next>.

19. Change the proposed label to strSupervisorIDLookup.

20. Click Finish. Access tells you that the table needs to be saved.

21. Click Yes, to save the table. The new strSupervisorIDLookup field is created as a Short Text field with the same field size as strEmployeeID in the tblEmployeesHRData. There is no caption.

22. Add the caption of Supervisor Lookup to the field.

23. Re-save the table.

25. Switch to Datasheet View and observe the new field: It's currently empty, but we will be able to cross-reference the contents of the Supervisor field in order to determine the correct name for the Supervisor for each of the existing records. Then, once the supervisor names have been entered for each of the existing records, we will be able to delete the Supervisor field and from that point on just use the Supervisor Lookup field for entering the people who will be supervising each project.

26. Click in the Supervisor Lookup field of record 1.

26. Click the  to display the drop-down Value List, and choose 10 Wilson. The Emp ID alone is stored in the field, but the name is there (on the drop-down list) as well, to help you choose the correct employee as the supervisor of the work order.

Notice that there are three employees with the same last name of Wilson in our Value List – Terry, Tony, and Andy. There could, obviously, be a problem distinguishing one from the other if you don't know which ID number belongs to whom. Once we have entered a few of the supervisors, we will go back and add the first name column to the Value List so that we know who's who.

27. For the remaining 14 records of the table, select the employee name and ID number corresponding to what you see in the Supervisor field.

Your finished table should look like the following:

28. When you have the supervisor information entered, re-save the tblWorkOrders table. Leave the table open.

Modifying a Lookup List

Once you have a Lookup Value List created, you can modify it to add other columns to it, or remove unwanted columns from it. You can also change the data themselves. Any and all changes made to the Lookup List at this point, once it has been linked to another table, will automatically be reflected in

the target object.

Hands-On Activity: Modifying a Lookup List after it has been created

Before beginning: Your Home Tech Repair database, with the tblWorkOrders table open in Datasheet View.

1. Display the tblWorkOrders table in Design View.

2. Place the cursor in the strSupervisorIDLookup field, to activate that field and make the Field Properties for the field accessible.

3. In the Field Properties window (in the lower portion of the screen), activate the Lookup tab. In the Row Source line, you should now be seeing the beginning of the definition of the tables and fields that are serving as the lookup source for the Value List.

4. Click to place the cursor in the Row Source line of the Field Properties window, and press Shift

F2 to display the Zoom window. Zooming is always the best way to enter long strings of information into a text box, because you will be able to see what you're entering.

5. Click in the contents of the Zoom window, then highlight just the second entry in the list, which

includes the reference to the tblEmployeeHRData table and the strLastName field.

Note: Be sure to enter a comma between each table object.

Your objective here is that you will select one of the references to the source table (tblEmployeeHRData) and the source field (strLastName), copy them, and then paste them at the end of the list (between the last source reference and the word FROM). You will then change strLastName to strFirstName, and the editing change will be complete.

Note: Be sure to enter a comma between each table object.

6. Copy (Ctrl c) the references to the table and field source selected and position the cursor

between the strLastName reference and the word FROM.

7. Position the cursor between the strLastName reference and the word FROM.

8. Type a comma, to separate the last entry from the one you're about to paste.

9. Paste (Ctrl v), to paste the copied entry.

10. Edit the second reference to the strLastName field so that it references the strFirstName field.

The contents of the Zoom window should now be as follows:

11. Click OK, to record the change and close the Zoom window.

12. Re-save the table, and switch to Datasheet View.

13. Observe the contents of the Supervisor Lookup field: You are still seeing only two columns, for the ID and Last Name, in the drop-down list. But the First Name field, which you just told Access you wanted to see in the Lookup Value List, isn't displayed. The reason is that you initially told the Lookup Wizard that you only wanted two columns for the list, and that's what you still have. You will need to adjust the properties for the Supervisor Lookup field so that three columns are displayed. You will also need to widen the column so that all of the columns in the list are displayed.

Note: If you increase the number of visible columns without increasing the width of the column, Access will add horizontal scroll bars to the value list. This is cumbersome, so you will probably want to make the column wide enough to accommodate the new entry.

14. Switch to Design View.

15. With the strSupervisorIDLookup field still active and the Lookup tab still activated on the Field Properties window, click in the Column Count line and change the number from 2 to 3.

16. Re-save the table design, and switch to Datasheet View.

17. Widen the Supervisor Lookup column, so that a greater amount of information will be visible in the field when you display the drop-down list.

18. Display the contents of the Lookup List, and notice that three columns are now displayed.

19. Re-save and close the table.

Goals for this section:

 Changing Relationships

 Changing the data to test the Lookup field

 Testing Referential Integrity

A Look at Our Relationships Now that we've added Lookup fields to our tblWorkOrders table, we need to think about how that table is related to the other tables in the database. If the strSupervisor field of tblWorkOrders is currently related to other fields, the relationship will need to be redirected to the new Supervisor Lookup field. Once again, had this been done before we populated the tables with data and created the relationships, we wouldn't need to worry about it now. But given the fact that we're back-peddling here, we now need to adjust our design.

Hands-On Activity: Changing relationships

Before beginning: Your Home Tech Repair database file is open and there are no objects currently open.

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

Figure 48: Database Relationship icon

2. Observe the relationship between tblEmployeeHRData and tblWorkOrders: Access has left the

strEmployeeID field in tblEmployeeHRData related to the strSupervisor field of tblWorkOrders. That is, it did not automatically switch the relationship to the new Lookup field. (And why should it have done that? You really don't want the program to be making mega- decisions like that for you.)

3. Delete the relationship between strEmployeeID (in tblEmployeeHRData) and strSupervisor (in tblWorkOrders), by first clicking on the relationship line to select it, and then pressing the Delete key on the keyboard or selecting the Delete option on the popup menu.

4. When you select the join line and press Delete, Access displays an Alert Box, asking if you're

sure you want to delete the relationship.

5. Click Yes, to confirm the deletion.

6. Drag strEmployeeID (in tblEmployeeHRData) to strSupervisorIDLookup (in tblWorkOrders),

to create a new One-to-Many relationship between the two tables. 7. On the Edit Relationships dialog box, select the Enforce Referential Integrity option. 8. Select the Cascade Update Related Fields option. If we make changes to the fields in one table,

we want the fields in the corresponding table to change, as well.

9. Click the Create button, to create the relationship. Now you see the symbols 1 and on the join line, to indicate that Referential Integrity options have been set for this relationship.

Now let's make changes to the Referential Integrity settings of the other relationships. Since we have data in the tables, we can do this.

10. Right-click on the relationship line between tblCustomer and tblWorkOrders, and choose the

Edit Relationship… command.

11. On the Edit Relationships dialog box, select the Enforce Referential Integrity and the Cascade

Update Related Fields options.

Again, if you make a change to the Customer information in one of the tables, you want it to be automatically changed in the other.

You don't want to select the Cascade Delete Related Records option because if a work order for a customer were deleted in tblWorkOrders, the related customer would be deleted from tblCustomer. This definitely would not be good, since just because a customer cancels an order doesn't necessarily mean that the customer will never do business with you again.

12. Click OK, to edit the relationship. Because we set Referential Integrity for this join, you now see

the 1 and  symbols on the join line. 13. Next we need to consider the relationship between tblEmployeeHRData and

tblEmployeePersonal. Here, we have a One-to-One relationship. We need to ask ourselves two questions in order to determine how to set Referential Integrity for this relationship:

 If we change the employee information in one of the tables, do we want it to change in the other?

Answer: Yes. Therefore, we need to select the Cascade Update Related Fields option.

 If we delete a record from one of the tables, do we want that record to be deleted from the other?

Answer: Yes. Therefore, we need to select the Cascade Delete Related Records option.

14. Right-click on the relationship line between tblEmployeeHRData and tblEmployeePersonal. 15. From the shortcut menu, choose the Edit Relationship… option.

16. On the Edit Relationships dialog box, select the Enforce Referential Integrity option, and

both of the Cascade Update Related Fields and the Cascade Delete Related Records options.

17. Click OK, to create the One-to-One relationship between the two tables. Your finished

Relationships window should look similar to the following:

Note: We're not going to set any Referential Integrity options between tblBidData and tblWorkOrders because if we change one of the tables we don't necessarily want the other one to change. Also notice that Access is showing another tblEmployeeHRData table. This is because Access seems to feel that if a table has more than 1 relationship, showing that table in the Relationship View is helpful for the user.

18. Re-save and close the Relationships window.

Testing the Lookup Fields Now that you've created Lookup fields, let's take a look at what's going to happen in your database when you make changes to the different tables. Let's consider what's going to happen to the contents of your Lookup Field list in tblWorkOrders if you change the data in one of the Employee tables. For example, if you change the spelling of an employee's name in the tblEmployeeHRData table, you

want it to be automatically reflected in tblWorkOrders. Let's see how this is going to work.

Hands-On Activity: Changing the Data to Test the Lookup Field

Before beginning: Your Home Tech Repair database file is open, but no objects are currently open.

1. Open tblWorkOrders in Datasheet View. 2. Display the drop-down list of Supervisor ID's and names in the Supervisor Lookup field.

3. Notice the spelling of Barbara Benson's last name. We're going to change the spelling of her

name in the tblEmployeeHRData table, which is the source object for the Lookup Value List. When we do that, the spelling of the name will automatically change in this list, as well.

4. Open tblEmployeeHRData in Datasheet View.

5. In the Emp Last Name field of record 4, change the spelling of Benson to Bensen.

6. Move off the focus, to save the record.

7. Open tblWorkOrders in Datasheet View and display the drop-down list in the Supervisor Lookup field.

The spelling has automatically changed here because the contents of the field are being looked up in the tblEmployeeHRData table.

Note: Be careful with this. If you create a Lookup List in a database, that lookup has to be physically located in the database file in order to work properly. If you create a lookup and then move the source object to another location, when you try to open the Value List an error will be displayed.

8. Close all open objects.

Testing the Relationships with Referential Integrity Applied Next, let's take a look at the relationship between tblEmployeeHRData and tblEmployeePersonal. If the fields are changed in one of the tables or if an employee's record is deleted from one of the tables, you want those changes to be automatically reflected in the other table. Because we applied Referential Integrity (with both the Cascade Update Related Fields and the Cascade Delete

Related Records options selected) to the relationship between these two tables, this will happen automatically.

Hands-On Activity 1: Testing Referential Integrity

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Open the tblEmployeeHRData table in Datasheet View.

2. Open the tblEmployeePersonal table in Datasheet View.

3. Activate the tblEmployeeHRData window, by clicking on that tab.

Note: Make sure you have tblEmployeeHRData selected, and not tblEmployeePersonal. Because tblEmployeeHRData is related to another table (tblWorkOrders), this is where you need to make the changes that will impact tblEmployeePersonal, not the other way around.

4. Select record 25 (for employee ID 34, Terry Wilson).

5. With the record selected, choose the Home / Records / Delete Record icon. Access displays a

serious warning message:

If you happen to get the following message, then you did not save the changed relationship:

6. Click Yes, to proceed with the deletion. Record 25 for Terry Wilson has been deleted from both

the tblEmployeeHRData and the tblEmployeePersonal tables. In the tblEmployeePersonal table, we see #Deleted messages in each field of the deleted record.

7. Notice that the Undo command is not available. Remember that many things that you do in a

database file will not be reversible. The sooner you get used to this fact, the better.

8. Activate the tblEmployeePersonal window.

9. Place the cursor in any one of the columns containing #Deleted

10. From the Home / Sort & Filter group, choose Selection /’Contains #Deleted’ icon, to remove the #Deleted entries from the record.

There are now 24 records in each of these two tables. Because there is a One-to-One relationship joining the two tables, Access will be happy.

11. Close both of the tables, saying Yes if you're asked about saving the changes.

Hands-On Activity 2: More Testing of Referential Integrity Finally, we'll see what Access does when you try to delete a record from one table when the matching record is still present in its related table. Consider the situation with tblWorkOrders and tblCustomer. Every work order should have a reference to a customer. You can't have a work order

without a customer, and you don't want to be deleting a customer if there are still work orders in progress for that customer.

Before beginning: Your Home Tech Repair database file is open, and no objects are currently open.

1. Open tblCustomer in Datasheet View. 2. Select record 39, for Customer ID 1070. (The company name is Warner, Sally S.).

3. Choose the Home / Records / Delete icon. Access displays a message telling you that you're

about to delete 1 record and asking you to confirm the deletion.

Sally Warner doesn't have any work orders assigned to her, so you would be able to delete this record from the table.

4. Click No, to cancel the deletion.

5. Select record 1, for Customer ID 1032 (Boston Construction Co.). This company currently has

three work orders assigned to it. (You can see them by clicking the plus sign to the left of the row, to display the Subdatasheet.)

Therefore you shouldn't be deleting the record for this customer from the table, and Access will prevent you from doing that.

6. Choose the Home / Records / Delete icon. A different Alert Box is displayed. Now Access tells

you that because this table includes related records in tblWorkOrders, it cannot be deleted.

7. Click OK, to close the Alert Box. The table remains intact. 8. Close the tblCustomer table. If asked about saving changes to the table, click Yes.

Week 4 The Final Database Project The following is an overview and specific description of the database that you're going to be designing for the final project. Read through these specs to get an idea of what will be needed and how you need to be planning the tables. As indicated in the "The Data" section, all of the information you'll need for this project is in an Excel file named Final ProjectData.xls. If you haven't already downloaded this file from the from the Class Data folder, you need to do that now. I suggest you print this description out, and keep it handy as you go about planning your database.

Overview You work for a company named World Wide Imports, which has customers throughout the U.S. You want to develop a database that keeps track of the following:

 Customer names and addresses

 Contacts for each customer

 Sales reps within your company who handle customer accounts

 Territories within which the sales reps operate

 The orders placed by customers

 A list of the different products your company sells to the different customers

 Prices and descriptions of the products that you sell and that have been sold You ultimately will be doing numerous queries, to extract different pieces of information, including the following:

 Customers listed with contacts and the sales reps who handle the account

 Customers listed with the products they have ordered

 The total cost of each product that you sell (calculated by multiplying the List Price by the Discount Percentage, and then subtracting that result from the List Price)

 The orders that have been placed within a range of dates

 Customers within a particular territory who have bought specific products

 A list of customers who are inactive, so that they may be archived

 A list of the completed orders

 The total amount ordered by each customer You plan to send letters to the contacts in all of the companies who order from you, with information about new products. Therefore, you know that you will need all the necessary fields for a form letter mail merge. You want subdatasheets in your tables that will display the following:

 Customers with contacts

 Sales reps with customers and contacts within each company

 Products with the orders that have been placed for that product

 Territories with the customers in that territory, then with the contacts for each customer. [Each customer will potentially have multiple contacts – President, Vice President, CEO, Marketing Manager, etc.]

In the subdatasheet for a given table you will also, from time to time, want to see different pieces of information displayed for the records, and will therefore need to change the contents of a subdatasheet.

In order to make the data-entry process easier, you will create the following:

 Input masks for Phone fields

 Value lists whenever there is a finite number of options from which the end-user can (or should) choose

 Validation Rules to control the data that are being entered into fields

 Case control for the State field so that it's always displayed in uppercase regardless of how the data are entered

 Required fields whenever certain information should not be omitted

Specifics 1. There are 20 customers with whom you work. You want to keep track of the following information

for each customer:

 Customer ID#

 Customer Name

 Customer Address

 Customer City

 Customer State

 Customer ZIP

 Customer Territory #

 The ID# of the Sales Rep who handles the account

 Whether the customer is active or inactive 2. There are 35 contacts for the different companies. Some customers have one contact, while

others have as many as three. The contacts need to be tied to the company, so that you will be able to display the contacts for each company in a table subdatasheet and in queries.

3. You want to keep track of the following information for each contact:

 Contact ID#

 Contact First Name

 Contact Last Name

 Contact Position

 Contact Phone

 Contact Title (Mr., Ms., etc.)

 The company to which the contact pertains 4. There are 8 territories in which the sales reps operate. Each territory has a code number (1 – 8)

and a full name.

 The territories will need to be tied to the sales rep, so that a given territory may be displayed with its sales rep in a table subdatasheet and in queries.

 There are currently 8 sales reps, one for each territory. The sales reps need to be tied to the customers that they handle, so that a sales rep may be displayed with his/her customers in a table subdatasheet and in queries.

 Currently each territory only has one rep, but it's conceivable that others will be added in the future for a given customer, so you need to accommodate the potential for expansion.

5. You want to keep track of the following information for each sales rep:

 ID #

 Last Name

 First Name

 Territory in which they operate

 Phone

 The number of hours they work

 The date they were hired 6. You need to maintain a list of the 49 products you sell. You want to keep track of the following

information for the products:

 Product Code

 Description of the product

 Category

 Size

 Finish

 List Price

 Discount Percent 7. You also need to keep track of the orders placed by customers, including the following

information:

 Product code

 ID# of the customer who purchased the product

 ID# of the sales rep who handles the account

 Quantity ordered

 Date the order was placed

 Date the order was filled

 Whether or not the order is complete

The Data All of the data you need for this project have been created for you, in an Excel workbook file named FINAL PROJECT DATA.XLS. This Excel file is one of the files you have downloaded. There are four worksheets in the workbook file:

Customers, Contacts & Reps Territories Products Orders

But be careful: The layout of the sheets of data in the Excel file is not necessarily the organization that will work for your tables. In fact, you can bet it isn't. (Things don't work that easily when we're designing databases). You'll need to look at the data carefully and decide how they should be separated out for your tables. Spend some time on this. As you begin this project, your first step (and this is a critical step) is to decide how your tables are going to be organized. Specifically:

 Which fields will go in which table?

 What will be the primary key field in each table?

 What will be the foreign key field in each table?

 How will each table be related to the other tables in the database, and what type of relationship will it be?

Please take the time to plan this out, based on what you have to work with (that is, the data in the Excel file).

Assignment for Week 4

This week you'll have a few things to do.

1. Review chapters 6 & 7 in the textbook.

2. Do the Final Database Project Assignment #1. Due by midnight Eastern Standard Time on Sunday February 14, 2016.

This will be the first of 8 individual assignments that you'll be doing throughout the semester to create your own database. These assignments will be handed in and graded, as will the final version of the database.

This assignment is extremely important; because you need to make sure your database is designed correctly before you start actually creating it and populating it with data. So please spend an adequate amount of time on this.

Note: This is the first Final Database assignment, but it's the second assignment of this semester. Therefore, the name that you will need to attach to the file will reflect the number of the week. See below for the precise naming method I would like you to use.

Here's the assignment:

1. Read through the contents of the Word document named Final DB Project Description. (It's

located in the Class Data Files sub-folder, and it is also provided as the last lesson this week page 61.) This file will provide you with all the information you need to start laying out your final database.

2. Based on the objectives described in the Final DB Project Description, plan out your database.

 Decide how many tables you will need, and which fields will be in each one of the tables. Make absolutely sure your tables are fully normalized, so that there is no redundancy.

 Specify the data type for all fields. Use the Leszynski Naming Convention for all table and field names.

 Determine which relationships will need to be created, as well as the type of relationship. Clearly indicate the type (i.e., One-to-One, One-to-Many) for all proposed relationships. You should avoid Many-to-Many relationships for this database. That type of relationship falls outside the parameters of this class and isn't necessary in this file.

 Decide which fields will be the primary key fields and which ones will be the foreign key fields of the different proposed tables.

 Indicate which fields will be connected to which fields in the relationships. Make sure it is clear to me when I look at your tables which fields will be connected to which other fields, as well as what you are proposing as the type of join.

The best way to do this would be by drawing lines to connect the fields. If you do this in Excel or Word, you can use the Line tool (available on the Drawing toolbar) or one of the AutoShapes (also on the Drawing toolbar), or you can simply write out a description of the different relationships. Regardless of your method, make sure I understand your objectives with regard to the different relationships that you're proposing.

The following sample, which I did in Excel, will give you an idea of what I would consider to be a clear idea of how you would design the database. This example deals with a project database that keeps track of employees and the projects they're working on. Notice how the following items are clear from this description:

 The name of each table, with the Leszynski prefix.

 The name of each field, with the Leszynski prefix. (The field name prefix will tell me what you're proposing for the field type - Text, Currency, Date/Time, Number, etc.) (Remember that num is not the prefix we use for Number fields.)

 The proposed primary and foreign key fields in each of the tables.

 The type of relationship that will join each set of tables.

3. Hand in your table designs in either a Word or Excel document. Name the file <Your First Name> Week 4 Final DB.

I should warn you here: I really need you to name your files using this naming convention. If your files aren't clearly named, I will keep bugging you about it (as some of you have witnessed with last week’s assignment).