Create a new folder, name it with your last name, and then create a new db and name it EXERCISE. (L/BSBA/CS416/yoursection/yourfolder/Exercise.mdb

Use table wizard (Create – Table Templates) select option – customers, go to Design View and save table as ProspectiveCustomers. Change the table structure so that it contains following fields:To the selected items field move: CustomerID, ComopanyName, FirstName, LastName, EmployeeID, BillingAddress, City, State, PostalCode, PhoneNumber, FaxNumber, EmailAddress, WebPage, LastMeetingDate,, Comments.

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)

Add some records (type state codes in lowercase – access will automatically convert it to all caps; in the LastMeetingDate enter date in format mm/dd/yy or use a calendar)

Select all columns by their description row, click the Home - Records section, More, Column Width – best fit.

Create a new table, name it Employees, define following fields: EmployeeID, FirstName, LastName, IDCardNumber, JobCode, BirthDate, DateHired, Address, City, State, Code, HomePhone, EmergencyContact, EmPhone

Adjust properties of the State field so that we can enter state codes in uppercase only.

In Design View create table ProductType

The table fields will be:
- ProductID – primary key
- ProductName - Text
- ProductType – Text
- ProductCode – Text,

         - Price – currency
- QuantityAvailable - Number
- Details – memo (64000)

Select Product Code field and adjust its properties: we want the product code to be always in all caps (Format: >) and  the validation rule will be: "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 will filter the data entry and allow insertion of allowed codes only.

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

Design 3 forms (use wizard) with detail (for data entry), header (title), and close form button in the footer

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

Remove the “product” part from the labels

Position ID in the left-top corner, name right beside, and all other fields below ID, however, insert line between ID 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 ctrl + shift keys), r.c. – choose align right option/

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

Add calculated control field to the form

- next to the quantity field draw a text box, move its label right above (and choose a different color and style for this field)
 

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

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

Right under the line, 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.

Below the rectangle, in the right bottom, move fields: ID card, birth date, jobcode, and date hired attributes, align text labels to the left, textboxes to the right.

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, phone, id card#, jobcode, 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)

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

Cust ID in left top corner with text label removed and 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