Access Lab Notes - California State University, Northridge

IS 312 Spring, 2012 ? Microsoft Access Lab Notes

1. Getting Started ¨C Creating a new, empty database called ¡°SCHOOL.¡±

a. Insert your removable media.

b. Load Access 2010. Click Blank Database under Available Templates

c. In the right panel, click the Open File icon

. In the File New Database dialog box, specify

the drive letter for your removable media (see below left), click Ok and then click Create. The

default file name for the new database is database1.

d. The opening screen of the new database will prompt you to create the first table (below right).

Ribbon

Navigation

Pane

Object

Window

2. Creating a table in Design View

a. Click the View icon

on the Ribbon to create the first table in Design View, save the table

under the name ¡°Student.¡±

b. Enter the following field names and data types, set field properties, and set the Primary Key.

ID (Primary Key)

Last Name -------First Name -------Major -------------Units --------------GPA ---------------

c. Click the

Text

Text

Text

Text

Number

(Field Size: Integer)

Number

(FieldSize: Single; Format:

Fixed; Decimal Places: 2)

icon to save the table definition.

3. Populating the Student Table (data entry)

Click the View icon again to switch to the Datasheet View. Enter sample data as follows:

Page 1 of 10

IS 312 Spring, 2012 ? Microsoft Access Lab Notes

4. Creating a second table, ¡°Major¡±:

a. Click the Create tab above the Ribbon, click Table Design, and save the table under the

name ¡°Major.¡±

Major Name --------(Primary Key)

Department ---------GPA Requirement ----

Text

Text

Number

(FieldSize: Single,

Format: Fixed,

Decimal Places: 2)

5. Import tables from one Access database into another database

a. Click the File tab and select New. In the right panel, click the Open File icon

. In the File

New Database dialog box, specify the drive letter for your removable media, name the

database ¡°SCHOOL¡±, click Ok, and then click Create.

b. Click the External Data tab and select Access. A Get External Data dialog box opens (see

below left). Use the Browse button to find and open database1 and click Ok.

c. An Import Objects dialog box opens (see below right). Click Select All to select both tables

you created earlier, and click Ok. After you close the Get External Data dialog box, the two

imported tables will appear in the SCHOOL database.

6. Defining relationships between tables

a. Click the Database Tools tab, and then click Relationships

. Use the Show Table box

(below left) to put both tables in the Object Window (below right). Close the Show Table box.

b. Select the common field Major Name from the Major table, drag and drop it on field Major

Page 2 of 10

IS 312 Spring, 2012 ? Microsoft Access Lab Notes

of the Student table.

c. In the Edit Relationships window (below left), check the Enforce Referential Integrity box,

and click ¡°Create¡±. A line now links the two tables (below right). The ¡°1¡± and ¡°¡Þ¡± signs

indicate that this is a one-to-many relationship (Access automatically detects such

relationships). Save the relationship and close the Object Window.

d. Referential integrity is a system of rules used to ensure that relationships between records in

related tables are valid, and that you don't accidentally delete or change related data. In our

example here, if referential integrity is enforced between the Major table and the Student table,

Microsoft Access will make sure that every Student record has a valid Major field value that

can be found in the Major table. Conversely, no Student record can have a Major field value

that does NOT exist in the Major table. To see how these rules work, we will try to make some

changes to the data and observe how the system responds:

i. Open the Major table and attempt to delete the Marketing major record. You should receive

an error message. Can you explain why this happened?

ii. Open the Student table and try to change Jose Perez¡¯s major to FIN (for Finance). Again,

you should receive an error message. Can you explain why this happened?

7. Designing a single-table select query

a. Click the Create tab. Click Query Design on the Ribbon. What appears in the Object Window

is called Query-By-Example (QBE) window, along with the familiar Show Table dialog box.

Add the Student table to the QBE window and close the Show Table box.

b. Double click each field name or drag-and-drop it into a desired column.

Page 3 of 10

IS 312 Spring, 2012 ? Microsoft Access Lab Notes

c. Query exercise #1: to list all students who have a GPA greater than 2.50. Enter ¡°> 2.5¡± in the

row labeled criteria under the field: GPA (above left).

d. To run the query, click the Run icon

on the Ribbon. Above right is the output.

8. Defining Calculated Fields (Expressions)

a. Calculated fields are used in queries to dynamically obtain results using existing fields. As a

general rule, if a field value can be calculated based on other fields, then this value should not

be stored permanently in a table (why?). Therefore, a calculated field is not stored in a table.

Instead, it is defined in a query. Every time you run the query, values in a calculated field will

be recalculated.

b. We will insert a calculated field into the query. This new field, called Full Name, will link

values from existing fields Last Name and First Name to form a student¡¯s full name. If a

student has a name change and thereafter we rerun the query, the calculated field will reflect

the change.

c. In the QBE window, highlight the column for Last Name, click Insert Columns on the

Ribbon to add a blank column.

d. Put your cursor in the first row (Field:) of the new column. Click the Builder icon

on the

Ribbon to open the Expression Builder. Enter the following expression in the blank box (see

screen shot below left):

Full Name: [First Name] & ¡° ¡± & [Last Name]

If your expression includes a field name, that field name must be enclosed by a pair of square

brackets ¡°[].¡± In our example, both First Name and Last Name are field names, so they are

each placed between a pair of ¡°[].¡± If your expression includes any text characters (called a

string), they must be placed between quotation marks. In our example, we would want a space

between the first and last names of each student, hence the space between the quotation marks.

e. Click OK to close the Expression Builder. Put a ? in the Show box under Full Name.

Remove the ? from Last Name and First Name (below right). Now run the query.

f. Question: how can we display the query output sorted by students¡¯ names?

g. We will now insert another calculated field into the query. This field, called Total points,

will multiply values from existing fields Units and GPA. Again, because such a field is to be

calculated dynamically using the most up-to-date values, it should NOT be permanently

Page 4 of 10

IS 312 Spring, 2012 ? Microsoft Access Lab Notes

stored in the source table (the Student table in this case).

h. In the QBE window, put your cursor in the first row (Field:) of the blank column next to

GPA. Open the Exp. Builder and enter the following expression (see screen shot below left):

Total Points: [Units] * [GPA]

Again, remember that a field name must be enclosed by a pair of ¡°[].¡±

i. Click OK to close the Exp. Builder. Put a ? in the Show box under Total Points. Now run

the query (below right).

j. To change the format of Total Points, switch back to Query Design View and right click the

column. Select Properties. In the Field Properties dialog box, set Format to Fixed and

Decimal Places to 1. Close the dialog box and rerun the query to verify the result.

k. Save the query under the default name, Query1.

9. Calculating simple statistics (Aggregate Queries)

a. You can use the Totals ( ¡Æ) tool to calculate simple statistics such as totals, averages,

maximum, minimum, count, etc. Still in the Query1 design view, delete every column except

Major, Units, and GPA.

b. Click the Totals icon

on the Ribbon. A new row, Total:, appears in the QBE window. To

calculate average units and GPAs by major, change Group By under Units and GPA to Avg

(below left). Change the format of the Units column so that the resulting value will keep one

decimal place. Change the format of the GPA column to retain two decimal places. Run the

query and you should see the result as shown (below right).

c. Note from the result that with this type of query, for each field that you calculate a statistic, a

calculated field will replace the original field. In our example, two calculated fields,

AvgOfUnits and AvgOfGPA would replace the original fields, Units and GPA.

d. Click the Office button at the top left corner, and select Save As => Save Object As. Enter

Page 5 of 10

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download