Abbreviated instructions for Access Lab Book Chapter 2
In this set of Access tasks, we will create a database to store data about students at a university.
1.Turn on the computer and all of its peripherals.
2.Press the “Start” button, then “Programs”, “Microsoft Access”, then “Blank Database”.
2.1Save the database on your floppy disk (or other local storage) with the name “My First Database”.
3.Click the “Create table by using wizard” button. This will guide us through the steps to build a database.
3.1Choose the “Business” option, then from the “Sample Tables” list, choose the “Students” table.
3.2In the “Sample Fields” box, the “StudentID” is highlighted.
3.3Click the single right-arrow to add “StudentID” to the “Fields in my new table:” box.
3.4Do the same to add the fields: “FirstName”, “LastName”, “Address”, “City”, and “StateOrProvince”.
3.5Use the “RenameField” button to rename “StateOrProvince” to “State”; then click the “OK” button.
3.6Add the fields “PostalCode” and “PhoneNumber”.
3.7Click the “Next” button on the TableWizard window to move to the next step.
4.Leave the name of the table as “Student”.
4.1Make sure that “Yes, set a primary key for me” is selected.
4.2Click the “Next” button on the TableWizard window to move to the next step.
4.3In this TableWizard window, click the “Modify the table design” button, then click the “Finish” button.
5.You will now see the “Design View” window to modify your table structure.
5.1Maximize this window so you can see more lines on the screen.
5.2Click in the line below the “PhoneNumber” field and type a new field name: “BirthDate”.
5.3Press the Tab button and change the BirthDate field Data Type to “Date/Time”.
5.4Add the field “Gender” with the Data Type “Text”.
5.5Add the field “Credits” with the Data Type “Number”.
5.6Add the field “QualityPoints” with the Data Type “Number”.
5.7Use the shortcut keystroke Alt-f-s to save this database.
6.Right-click with cursor in the row for the first field (“StudentID”).
6.1From the pop-up menu that appears, choose the “Insert Rows” field.
6.2Enter a field with the name “SSN”, and keep its Data Type as “Text”.
6.3With the cursor active in this row, click on the Primary Key button on the icon bar.
6.3.1The Primary Key button has a golden key icon on it.
6.4In the bottom half of this window, click in the blank field next to the label “Required”.
6.5Use the pulldown arrow in this field to change the “No” to “Yes”.
6.5.1This field will now be required when someone enters data for a new student.
6.6 Remove the “StudentID” field by right-clicking on that row in the top half of the Design View window.
6.7From the pop-up menu that appears, choose “Delete Rows” to remove the “StudentID” field.
6.8Click in the “SSN” field row, then in the “Input Mask” field in the lower half of the window.
6.9Click on the Build button (the three dots: …) that appears at the right end of the “Input Mask” field.
6.10.Choose “Social Security Number” from this list of input masks, then click the “Finish” button.
6.10.1This will automatically convert numbers that are entered into the standard SSN format.
6.11Create the input mask “Short Date” for the “BirthDate” field using the same process as above.
6.12Create an appropriate input mask for the “PhoneNumber” field.
7.Use the Field Properties variable “Field Size” to change the “FirstName” field size to 15 characters.
7.1Make the “FirstName” field required.
7.2Change the “LastName” field to 20 characters and required.
7.3Change the “State” field to 2 characters; type the “greater than” character (“>”) in the Format field.
7.3.1The > character will automatically change all entered characters to upper-case.
7.4Type the text “LL” in the “State” field Input Mask; this allows only letters to be entered (no digits).
7.5Change the Field Size of the “Credits” field to “Integer”, and set its Default value to zero (0).
7.6Change the Field Size of the “QualityPoints” field to “Integer”, and set its Default value to zero (0).
We will now add a validation rule to demonstrate how we can prevent incorrect data from being entered into our database.
8.Select the “Gender” field in the top half of the Design View window.
8.1Change the “Gender” field size to one character (in the bottom half of the Design View window).
8.2Click in the “Format” field in the bottom half of this window and type the “>” key.
8.2.1This will ensure that all data entered into this field will be converted to upper-case letters.
8.3Click in the “Validation Rule” field and type the text: = “M” or “F”
8.4Click in the “Validation Text” field and type the text: You must specify M or F.
8.5Use the shortcut keystrokes Alt-v-s to change from the Design View to Datasheet View.
8.6Enter a record that contains data about yourself, save the database, and close the Datasheet View.
In the next set of steps, we will create a data entry form that will make it easier to enter data into the database.
9.Click the “Forms” tab at the left edge of the Database View window.
9.1Click the “New” button to start creating a new data entry form.
9.2Click the choice for “Form Wizard” to guide us through the process of creating a new form.
9.3From the pulldown choices in the field at the bottom of the “New Form” window, choose “Students”.
9.3.1This will select the “Students” table to create a new form.
9.4Click the “OK” button at the bottom of the “New Form” window to move to the next step.
9.5Click on the double right-pointing arrow to add all Student table fields to the data entry form.
9.6Click the “Next” button to move to the next step in creating this data entry form.
9.7Click the “Next” button to accept the “Columnar” format for this data entry form.
9.8Choose “Industrial” for the style of the data entry form, then click the “Next” button.
9.9In the last step of creating this data entry form, leave the form name as “Students”.
9.10Select “Modify the form’s design” radio button, then click the “Finish” button.
10.You will now see the data entry form in “Design View”; you can now modify the design of the form.
10.1Click on the “Last Name” label and hold the mouse button down; you can now move this label.
10.2Drag the “Last Name” label and field to the same line and to the right of the “First Name” field.
10.3Do the same actions to move the “Address” field where the “Last Name” field just was.
10.4Click and drag the right border of the form to the 7” position.
10.4.1You may have to move the toolbars out of the way.
10.5.Move the “State” and “PostalCode” fields to the same line as the “City” field.
10.6Hold down the Shift key while clicking on the “City”, “State”, and “PostalCode” fields.
10.6.1This will select all three fields.
10.7Move these three fields directly under the “Address” field.
10.8Line up the fields “PhoneNumber”, “BirthDate”, and “Gender” on the line below the “City” field.
10.9Move the “Credits” and “QualityPoints” fields to the line below the “PhoneNumber” field.
11.Click on the “Text Box” button on the Toolbox toolbar. It has a lower-case “ab” on that button.
11.1Drag out an area beneath the “Gender” field for a new field label.
11.2Click on the text “Unbound” in the area you dragged out. The “Unbound” text will disappear.
11.3Enter the text:=[QualityPoints]/[Credits]
11.3.1The text “QualityPoints” and “Credits” must be exactly as they appear in the fields.
11.3.2There should be no spaces in either of these two text items.
11.4Click in the “Text24:” (or similar text) label and highlight (drag through) all of that text string.
11.5Enter the text:GPAand then press the Enter key.
11.6You can re-size both the “GPA” label and the associated field separately by using their drag handles.
11.7Right-click on the “GPA” label, then choose “Properties” from the pop-up menu.
11.8Click on the “All” tab, and change the “Name” field value to “GPA”.
11.9Click the “Format” field under the “All” tab, then click the pulldown arrow.
11.10Select the “Fixed” option for the “Format” field of the “All” tab.
11.11Select the “2” option for “Decimal Places” under the “All” tab. What do you think this will do?
11.12Close the Properties dialog box to return to your data entry form.
12.Hold down the Shift key while selecting all of the text labels (NOT the fields).
12.1Click on the “Align Right” button on the Formatting icon bar.
12.1.1This will align all of the field labels right next to where those field values will appear.
13.Click on the horizontal ruler separating the “Form Header” label from the “Detail” label.
13.1Drag the horizontal ruler down so there is space to insert a form header (half an inch or so).
13.2Click on the “Label” button on the Toolbox (it’s the topmost left button on that toolbar).
13.3Drag out an area to put a header label (just below the “Form Header” label).
13.4Type the text “Student Information Form” in the area you just dragged out.
13.5Click outside of the form header you just created, then click back inside it to select it.
13.6Change the font size of the form header to 18 point.
13.7Click on the pull-down arrow next to the “Special Effect” button on the Formatting icon bar.
13.8Choose the “Special Effect: Raised” option from the choices there (1st row, 2nd column).
13.9Click on the “Text Box” on the toolbox to drag out an area for the current date.
13.10Drag out an area for the date, in the top right corner of the Form Header.
13.11Replace the “Unbound” text with the text:=Now()
13.12Click on the text label (something like “Text27” or “Text31”) and press the Delete key.
13.13Right-click on this new field and select the “Properties” option from the pop-up menu.
13.14Under the “All” tab, change the “Format” field to “Short Date”, then close this window.
13.15Use the shortcut keystroke Alt-f-s to save this form.
14.Use the Alt-v-f to change to the Form View of this form.
14.1You can now use this data entry form to enter a new record about yourself or someone else.
The next set of lab book pages goes through more advanced tasks that you can do to modify your data entry form.