Home Dave Heppenstall | E-mail Contact

Find 
 
[Résumé]Résumé [Academics]Academics [About Me]About Me
Lab 3 4 5 6 7 8 9 10 |  » 

Access

14 February 2008

This tuitorial is an update to the official Lab Six web page. It is also an update to the official supplemental Access Lab Notes page.

We'll need a file for today's lab; open the Course FTP Server (Access Labs folder) and download the student.accdb file to your USB drive.

On This Page:

Create a New Relational Database

Start Microsoft Access.

Click the main menu button and select New as follows. A new panel will appear in the main Access window as shown. Click on the Browse button as indicated and save the new blank database file in a folder on your USB drive. Finally, click on the Create button.

New Database    Save New Blank Database

The following window will appear, the Datasheet View. This view shows us the data contents of the one table in our database. Note the icons on the bottom-right corner of the view, these icons switch between Datasheet View and Design View.

Datasheet View

Designing the Table

Switch to Design View by clicking on the currently deselected icon as indicated in the diagram above. You will be asked if you want to save your table, click Yes:

Save Table

The following window will appear, the Design View:

Design View

In this view, we can edit the design and properties of the table. First, we shall specify table parameters: field names and data types. In addition to the previously existing "ID" field, add the following fields: Last Name, PO Box, Residence and Telephone. The data types of the new fields will be automatically set to "Text" by default.

Field Names

Use the General tab of the Field Properties panel to specify additional parameters. For each field, specify the Field Size as follows: Last name (15), PO Box (6), Residence (12), Telephone (8). In addition, set the Data Type of the ID field to Number instead of AutoNumber.

The primary key for the table, as indicated with this icon: "Key" is the main reference field which uniquely identifies a record (table row) from other records.

Populating the Table with Data

Switch back to the to Datasheet View. Once again you will be asked if you want to save your table, click Yes.

Add some data to your table. In this view, we can enter data into the database the same way we enter data into a spreadsheet. After entering some data, our table might look like this:

No, that isn't really my student number :)

We can also insert and delete rows (records) the same way as we do in Excel:

Delete Record

Creating Forms

We use Forms to create a more attractive method for entering data. Open the Form Wizard by taking the following steps: Click the Create menu tab, click More Forms and select Form Wizard:

Form Wizard

In the first Wizard panel, add all the fields in the table by clicking >>. Click Next.
In the second panel, choose Columnar. Click Next.

Form Wizard 1 Form Wizard 2

In the third panel, select any design style you like. Click Next.
In the final panel, choose "Open the form to view or enter information".

Form Wizard 3 Form Wizard 4

We can now use this form to enter new data. Click on the New (Blank) Record button as follows:

New Record

Enter some data into this form. After you have entered data into the fields, save this record by hitting CTRL-S. When you return to the Datasheet View, hit F5 to refresh the view of your table to include the new record we just entered.

Form Data Entry

Importing External Data Tables

Here is where we will need the file we downloaded earlier. Click on the External Data tab menu and select Access from within the Import group as follows:

Import External Access Data

In the dialog box which appears, click Browse to select student.mdb from your USB drive. Select the first option to, "Import tables, queries, forms, reports, macros, and modules into the current database."

Browse for Access Data

The Import Objects dialog box will appear; showing you the contents of the external file. Highlight "Book" and click Ok to import this table.

Import Objects Dialog Box

The "Book" table has been added to your list of tables in your new database file.

All Tables Listing

Running Queries

Now we're going to use Access to run queries on the Book table we just imported. Click on the Create menu tab and select the Query Design option as follows:

Query Design

The Show Table dialog box will appear. Select the "Book" table and click Add; the table will be automatically added in the background. Click Close.

Show Table

A new tab, "Query1" is now visible in the main window as follows. The top pane contains the table we are performing a query on, the bottom pane contains fields for us to define our query.

Query1 Tab

First, select the fields we want to see in the result view:

Result Fields

In the "Book Number" column, enter "100" into the criteria field. Right-click on the "Query1" tab and select SQL View to take a look at the raw SQL query text instruction. Select Datasheet View to view the query results.

Book Number 100    Right-Click Query1

Some other criteria searches you could try are using wildcards, such as "Well*" or "*ell*". If you enter specifically, "[Enter a value]" into the criteria field, you will be prompted by Access to manually specify a value when you open the Datasheet view to look at results. Finally, you can use mathematical operations such as ">10" or ">02/02/96 and <01/01/98".

Lab 3 4 5 6 7 8 9 10 |  » 

- Top of Page