1. Create a new db and name it EXERCISE.

2. Use table wizard (table – new – wizard) select business – customers. To the selected items field move: customer ID, ComopanyName, ContactFirstName, BillingAddress, StateOrProvince, PostalCode, ContactTitle, PhoneNumber, FaxNumber, LastMeetingDate, then choose the business – contacts style - last meeting date.

3. Select element ContactFirstName and Rename it to ContactName; StateOrProvince replace with just State, and ContactTitle will be EmployeeID. Also, accordingly adjust Caption (this name will appear as the name of table’s column in data entry view) of each re-named field.

4. Save the table as ProspectiveCustomers, let Access set Primary Key for you.

5. Open the table in Design view and add 1 row above State – Field City, text, 25 characters

6. Select the PostalCode field and from field properties remove the pre-defined input mask

7. In order to enter state codes in uppercase only, select the State field and into the Format text field type > (converts lowercase to uppercase; < does the opposite)

8. Go to the Entry view, select all columns, click Format – Column Width and fit columns to text (remove checkmark from standard width and click best fit)

9. Add 4 records (type state codes in lowercase – access will automatically convert it to all caps; in the date field there’s a pre-defined input mask – you need to enter m/dd/yyyy)

10. Back in Design view decrease field size: Address – 30 characters

11. Insert Row above EmID, field: WebPage, type; hyperlink; description, Company Web Page Address

12. Below last meeting, create a new field Comments, type: text, field size: 250

13. Save

* * *

1. Create new table, use wizard, business – employees: EmployeeID, FirstName, LastName, SSN#, DeptID, BirthDate, Date Hired, Address, City, State, Code, HomePhone, EmergencyContact, EmPhone

2. Save as Employees, let Access set Primary Key, do not relate the table to any other in your db

3. In design view, swap first and last; rename fields (and their caption) SSN to ID card number, remove the input mask from this field, also, rename state/province to just State (incl. its caption), then remove Postal Code input mask. Also replace deptID with JobCode, change it to text, 10 characters; adjust properties of the State field so that we can enter state codes in uppercase only finally, remove the input mask from the birth and hire dates.

4. Add 3 records

* * *

1. In Design View create table Product Type (do not use wizard)

2. The table fields will be:
- Product ID – primary key
- ProductName - Text
- ProductType – Text, Caption Product Type
- ProductCode – Text, caption with space
- Price – currency
- QuantityAvailable - Number
- Details – memo (64000)

3. Write Caption for each and every field

4. Select Product Code and adjust its properties: we want the product code to be always in all caps (Format: >) and also we want to ensure that the data entry person doesn’t enter eny other code but C, D, E, F or T ? in the validation rule field type: "C" Or "D" Or "E" Or "F" Or "T" (you can also limit the number range, e.g.>=0 and <=800); in the validation text field write You must enter C, D, E, F, or T for product code. (this is the “error message” that appears when you enter a value that is not allowed).

5. Enter 3 records (try to check out the functionality of the validation rule)

* * *

1. Design 3 forms (use wizard)with detail (for data entry), header (title), exit command in the footer

2. Format the forms as following (start with the Product Type form)

3. Remove the “product” part from the labels

4. Position ID in the left-top corner, name right beside, and all other fields below ID, however, insert line between ID&type and other fields . Text labels of the other fields will be left aligned, text boxes right-aligned (simply select the desired fields (holding down the shift key), r.c. – align right option)

5. Set tab stop option for ID to NO (r.c. – properties – other – tab stop = NO)

6. Design form

7. Add calculated control to form
- next to the quantity field draw a text box (click the ab button), move its label right above (and choose a different color and style for this field)
- use the build button in control source so that this field displays total of form’s quantity*price; then change format of this text label to currency (r.c. text box – properties – data – control source = [Price]*[Quantity] (choose these parameters from the very same form) – (this will show the total value of your current stock)
- finally, rewrite text label to Total Value

8. Remove dividing line from form properties

9. Format the Employee form (title in header, close button in footer)

10. Emp ID in left top, remove text label, no tab stop, right next to it there will be first and last name; below a line.

11. Right under the line, positioned in the left part of the form’s detail, relocate fields: address, city, state, postal code, phone; align text labels to left, textboxes to right; right next to this area draw a rectangle (click the rectangle button on the toolbox bar), choose transparent background color, any effect, change its border color and increase border width. Position emergency fields in the middle of this rectangle, and add a text label with title Emergency.

12. Below the rectangle, in the right bottom, move fields: ID card, birth date, dept, and hire date, align text labels to the left, textboxes to the right.

13. Adjust tab order like following (r.c. any neutral part of form’s detail – choose tab order): last name, first name, address, city, state, postal code, home phone, id card#, dept#, birthdate, date hired, emerg contact, em phone (select the desired element (click the gray “square” in front of it) grab it and move where desired)

14. Design the form

15. Remove dividing line from form properties

16. Format the Prospective customer form (header – title, footer close button)

17. Cust ID in left top, remove text label, no tab stop; next to the ID, there will be company and contact name. Reposition other fields like following: below co name – address, state, postal code, web page, phone, fax; in the right lower corner – emID, meeting date, and comments in a rectangle with a border; design the form

Compact the Database (tools - utilities)