Access Week 1 Class Exercise

ACCESS 2000 CLASS EXERCISE 1

1. Create a new database

Run Microsoft Access
Select the Blank Access Database option.
Save the new database file as My First Database on the Access folder on your floppy disks. See Figure 1.

Figure 1

2. Create a new table using the Table Wizard.

Select the Tables object in the database Window.
Double click Create table by using wizard. See Figure 2.

Figure 2

Refer to Figure 3 in this step. Select the Students Table among the list of sample tables in the Business category. Add fields StudentID, FirstName, LastName, Address, City, StateOrProvince using the > button. Select StateOrProvince among the list of included fields and click the Rename Field... button and rename this field as State.

>	Include the selected sample field in the fields in my new table
>>	Include all the sample fields in the fields in my new table
<	Discard the selected field from the fields in my new table
<<	Discard all the fields in my new table

Figure 3

Click Next. Rename the new table as My Students.
Verify that the option Yes, set a primary key for me is selected so that it will define a primary key for you automatically. Click Next.
Select the option Modify the table design in the final step of the Table Wizard and click Finish. Now you will be advanced to the Design View of the My Students Table.

3. Modify the design using the Design View.

Refer to Figure 4.

Figure 4

Field area: Each row contains the (Field Name, Data Type, Description) combination for a field.

Property area: Displays the General and Lookup properties for the selected field (row) in the field area.

a. Add the fields BirthDate, Gender, Credits and QualityPoints. Save the table.

b. To add another field before StudentID; point to the StudentID field, right-click and select the Insert Rows command. Add the field SSN for Social Security Number whose data type is Text.

c. Select SSN. In the property area, make required property Yes.

d. To make SSN the primary key, right click in the SSN row and select the Primary Key command. Note that the key is located to the left of the SSN field now.

e. Now that we do not need the StudentID field, lets delete it by pointing to it, right-click and select the Delete Rows command.

f. Explicitly issue the File; Save command to save the changes in the Design View.

g. To add an input mask to SSN;

Ø Select SSN

Ø In the property area, click the Input Mask box.

Ø Click the Build button ... to display the input mask wizard. Click Social Security Number. Click Try It box to try the mask. Click Finish to accept the mask.

To add an input mask to BirthDate;

Ø Select BirthDate

Ø In the property area, click the Input Mask box.

Ø Click the Build button ... to display the input mask wizard. Click Short Date. Click Finish to accept the mask.

You can use the InputMask property to create an input mask (sometimes called a "field template") that uses literal display characters to control how data is entered in a field or control.

Microsoft Access provides two field properties that produce similar results: the Format property and the InputMask property.

Use the Format property to display data in a consistent format. For example, if you set the Format property for a Date/Time field to Medium Date format, all dates entered will display in this form: 12-Jan-96. If a user of your database enters a date in the form, 01/12/96 (or any other valid date format), Microsoft Access will convert the display to the Medium Date format when the record is saved.

The Format property affects only how a value is displayed, not how it is stored in the table. Also, a display format isn't applied until the data entered is saved. Nothing is displayed in the field to suggest or control the format in which data is entered. If you need to control how data is entered, use an input mask in addition to, or instead of, a data display format. If you want data to display exactly as entered, don't set the Format property.

Predefined display formats are available for Number, Currency, Date/Time, AutoNumber, and Yes/No fields, and you can define custom formats for them as well. There are no predefined formats for Text, Memo, or Hyperlink fields, but you can define custom formats. You can't define a display format for OLE Object fields. If > (<) is entered for the format property of a text field, than this field is displayed all in uppercase (lowercase) letters.

Use the InputMask property to display literal display characters in the field with blanks to fill in.

Changing the InputMask property requires that you save the table.

h. To change any other property of a field;

Ø Select the field

Ø Click the property to change

Ø Change the property.

Change the following properties:

Field Name	Property	Value
FirstName	Field Size Required	25 Yes
LastName	Field Size Required	25 Yes
State	Field Size Format	2 >
Credits	Field Size Default Value	Integer
QualityPoints	Field Size Default Value	Integer
Gender	Field Size Format Validation Rule Validation Text	1 > “M” or “F” must be M or F

About field size

Text: 0-255 characters, default is 50.

Number: For numbers without decimal point: Byte (max.255) , Integer (max.32,767) , Long Integer (max.2,147,483,647) . For numbers with decimal point: Double (8 bytes) and Single (4 bytes)

Date/Time: General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time and Short Time

i. Save the table.

j. Switch to the Datasheet View using View Menu; Datasheet and enter the records in the following table. Do not forget to fill the final record according to your own properties!

SSN

First Name

Last Name

Address

City

State

BirthDate

Gender

Credits

QualityPoints

Financial Aid

Campus

Major

111-11-1111

Ahmet Nail

Lök

Erzincan

7/11/65

Main

Management

202-02-8890

Dave

Geneaue

Quebec

3/ 3/62

Yes

Main

International Relations

222-22-2222

George

Master

Sunset Boulevard

Los Angeles

1/ 7/60

MSSF

Political Sciences

333-33-3333

Hakkı

Gürses

100. Yıl

Ankara

10/ 2/65

East

International Relations

444-44-4444

İpek

Sözen

Bilkent Çamlık Sitesi

Ankara

10/ 2/65

Yes

Main

Management

555-55-5555

Deniz

Özme

Beysukent

Ankara

6/22/64

East

International Relations

k. Adjust the column widths and the page set-up to print your work on a single page in landscape orientation.

l. Exit Access.