In this assignment you are going to make sure that your relationships are set correctly so that the information in the subdatasheets is displayed as desired. In addition you will define properties for the fields in the tables and you will create some Value Vists to facilitate the data entry process.
Assignment for Week 6
1. Review chapters 8, 9, & 10 of the textbook.
2. Do Final Database Project Assignment #3. Due by midnight Eastern Standard Time on Sunday February 28, 2016.
This is the third of the 8 individual assignments that you’ll be doing to create your own database.
In this assignment, you’re going to make sure your relationships are set correctly so that the information in the Subdatasheets is displayed as desired. In addition, you will define properties for the fields in the tables and you will create some Value Lists to facilitate the data entry process.
Here are the specifics of the assignment:
- All data MUST be fully displayed.
- Make sure your relationships are set correctly, so that when you click the plus sign to the left of a record in a table you are able to see the desired data in theSubdatasheet. I would like to see the following datasheet/subdatasheet combinations of information:
- The contacts for the different customers in the tblCustomers table. When you click the plus sign next to a customer, you should be able to view the contacts for that customer.
- The customers for each sales rep and the contacts for the displayed customer.
- The orders that have been placed for the different products.
- The territories with the customers and contacts for each of the territories.
- Define the following Properties:
- Assign Input Masks for all Phone fields.
- Define all Zip Code fields to ensure that they all are displayed with five digits.
- The State field data should automatically appear in uppercase.
- A maximum of 40 hours should be allowed in the Hours field for sales reps. If the end user enters a value that is greater than 40, the following message should be displayed:
The number of hours must not exceed 40.
A required Sales Rep ID in the Customers table.
Dates entered into the Hire Date field must be today’s date or earlier. If the user enters an invalid date, and Alert Box should display an appropriate message.
The Active Yes/No field in tblCustomers should default to Yes.
The Discount values (for the products) should be formatted to display with percent symbols and 2 decimal places.
- Only values from 1 to 8 should be accepted in the Territory Code field. If a value outside this range is entered, an Alert Box should be displayed with an appropriate message to the user.
- Create the following Value Lists to facilitate the data entry process:
In tblCustomers, create a two column Value List that displays the Territory Code and its corresponding name next to it. When the end user displays the list, they will see both the code and its name, which would make it easier to select the correct code. This, in turn, will minimize errors.
Make sure both the code and the full territory name are fully displayed when the Value List is displayed in the field.
When the user chooses one of the territory codes from the Value List, the territory code number is displayed in the field.
- Contact Title
Create a one column Value List for the Contact Title field of the tblContacts table that lists all of the most common prefixes (Mr., Ms., etc.) for contacts. Include on this list the titles that you feel are most useful or appropriate, given your own database needs and preferences.
- Product Category
Create a one column Value List for the Category field of the tblProducts table, to include 3 items: