Access Tutorial - Creating a Database

Covered in this section:

It is important that you understand the following definitions before you begin working with databases.

A field is one part of the information related to an entity. For example, you might have a name field, or a date field, or a field to hold a person's social insurance number. All of these fields relate to the entity person.
A record is a collection of fields. For example, an employee record contains all of the information you have about the employee.
A database is the collection of records containing all the information in the database.

Access Database Definitions

There are several different "views" available in Access database processing.

Datasheet View allows you to enter information into your database. It is in a table format similar to Excel.
Design View allows you to setup and edit the fields of your database.

Starting your Database

To open MS Access, open the Microsoft Office folder that is located on your desktop. inside you will find an icon that looks like this:

. Double Click on this icon and the following window will pop up. If the icon is not on the desktop you can also find it by click on the Start button, then All Programs, MS Office, Access.

This window gives you three options:

Blank Access database allows you to start a brand new database.

Access database wizards, pages and projects provides an easy interface to use Access.

Open an existing file allows you to open an existing Access database.

Exercise:

Open a blank database and take a look at all the features. Notice that you will be prompted to enter a name. You can either leave it as the default "db1" or choose your own descriptive name.

Creating a Table

The database window displayed here on the right containts a list of many options. Right now in the Objects bar on the left hand side Tables is selected. The the other sections on the left are the tasks you can perform on Tables. The only tasks we will be concerned with are Queries, Forms and Reports

Under the Tables option, Double click on Create table in Design view to start inserting the fields for our database.

This window allows you to insert the field names and type of data fields will contain. This example shows a Name field to contain the names of students for this database. The names of students are text so the Name field will contain Text for Data Type. To view other data types click on the down arrow to the drop down menu options.
The lower left part of this window allows you to set up additional options associated with the fields.
*Note: Field names are entered in vertically not horizontally like Works Database.

Exercise:

Create a table with the following fields:

Name - Data Type: Text
Student # - Data Type: Number
Class Name - Data Type: Text
Test Mark - Data Type: Number
Max Score - Data Type: Number
Test Weight - Data Type: Number
Comment - Data Type: Text

*Note: Access will not allow you to create fields that involve calculations under Table. However, calculations or, as they are called in Access, expressions, are regularly used when creating queries. We will get to that later.

Now click on Datasheet View.

Access will prompt you to save the table. Save it as student.

At this point, Access will prompt you to define a primary key. A primary key is used to make a record distinct from another. In our example a good primary key would be the Student # because each student’s Student # is unique. Click No for now.

Now enter in data for about 20 records. Just type in the cells like you would in Excel or the Works Spreadsheet. You can select multiple fields but there is a little trick. It is not like Works Database where you click and drag over a range of cells. You must move your mouse over the lines between two cells. A large "plus" sign will appear, now just click, hold, and drag. There is no fill function like in Excel but this will allow you to make font and color changes to multiple entries. The data entered can be made up, but make sure you fill in every field for every record.

You now have the beginning to a functional database!

Formatting a Database

Click on View in the top menu, select Toolbar, and then select Formatting to display the following toolbar.

The formatting toolbar allows you easily modify the style of text.

Notice that when you make any changes they are applied to the entire table.

Sorting and Searching

From this section on you will need a database with entries in it. Download it here.

Sorting

Records can be sorted by whatever field you want. For example, you might sort by Name; this would sort all records in alphabetical order by student name. Or, you might sort by Class Name; this would group all of the records by the class name field. The groups would be presented in alphabetical order of class name. For example, "arth" would precede "biol." To sort by one field, click on any record under the field and click either

to sort records into ascending order or

to sort in descending order.

You can specify more than one sort "key". For example you could sort first by "Class Name" and then by "Name".

To specify such a sort, go to the Records menu, select Filter and Advanced Filter/Sort.

Click on the first column and select Class Name, and choose sort by ascending order. Then, on the second column,, select Name and choose sort by ascending order as in the following example:

Click on the Filter iconto apply the sort. Now the records are sorted by "Class Name" and then by "Name."

Exercise:

In your database:

Click on the Student # field and sort it in descending order.
Sort the records by Test Marks and then by Name, both in ascending order.

Searching

In the Edit menu use the Find option to specify a request for records based on a simple match. For example, if you had a database of employees, you might want to see the record for Shayne Wright. When you perform a Find, the following window will pop up:

Type in the element you are looking for in the Find What: menu.
Make sure that the Look In: scroll down menu is selected to the whole table.

Click on Find Next to search for the element. If it is not found, an appropriate message will be displayed. The cursor will automatically skip to the found element.

Click Cancel to close the window.

Exercise:

Go to the Edit menu and select Find. Enter a name that should be found within your database.