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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- avanwaves user guide university of california berkeley
- homework instructions
- user sguideandreference guide autodesk
- user sguideandreference guide
- the query builder the swiss army knife of sas enterprise guide
- evolution query builder
- peoplesoft query building an expression using an sfis hrms function
- tutorial 03 maintaining and querying a database
- access lab notes california state university northridge
- plug in city university of new york
Related searches
- california state university system
- california state university second bachelor s
- california state university tuition
- california state university jobs
- california state university system schools
- california state university system wiki
- california state university application log in
- california state university campuses list
- california state university log in
- california state university application deadline
- california state university tuition fee
- california state university fees