Getting Started

Here are the procedure for making a new database in Access. Read the paragraph but do NOT do what it says for I will give you a half-finished database to save time.

Start Access then make a new database by clicking 'Blank Database' in the window at the right side of the Access window. Note that the first thing Access asks you to do is to give the new database file a name - with other programs you would not expect to give the file a name until you first save it, much later on. It is advisable to keep the name of the database relatively short and do not use spaces or other punctuation in the name of the database. Also, the name of the database should reflect the database's contents.

For this exercise, to save time, I will give you the half-finished banking database called bankdb.mdb (.mdb is the three letter extension given for Microsoft DataBase files). You can download it to your space by right-clicking this link and choosing 'Save Target As..."

Double-click the downloaded file or start Access first and open the file with File>Open. If you get a security warning then click the Open button.

This screen will now appear:

The two main features of this main screen are the menu bar that runs along the top of the window and the series of tabs in the main window. The meaning of the various tabs (tables, forms etc) was given on the main database page that you should have already read. This tutorial focuses on the first four tabs: Tables, Queries, Forms and Reports.

Creating and Viewing Tables

Tables are the main units of data storage in Access. Recall that a table is made up of one or more columns (or fields) and that a given column may appear in more than one table in order to allow a relationship between the tables to be established. The rows in the table are also called records. The tabular format reminds us of an Excel spreadsheet - the main differences between a database table and a spreadsheet are that:

From the business example discussed earlier, we concluded that two tables would be sufficient to store the data about Customers and their bank Accounts. The Accounts table has already been created for you - it's the only object in the bankdb database that you have downloaded and opened. We now give the step-by-step instructions for creating the Customer table.

There are a number of ways to create a table in Access. Access provides wizards that guide the user through creating a table by suggesting names for tables and columns. The other main way to create a table is by using the design view to manually define the columns (fields) and their data types.

While using the wizards is a fast way to create tables, the user has less control over the column names (fields) and data types. In this tutorial, we will describe the steps to create a table using the design view. You are encouraged to experiment on your own with using the Create Table wizard.

2.1 Creating a Table Using the Design View

When defining the fields (columns) for a table, it is important to use field names that give a clear understanding of the data contents of the column. For example, does the field CNO indicate a Customer Number or a Container Number ?

Field names in Access can be up to 64 characters long and may contain spaces. However, the use of spaces in field names and table names is strongly discouraged. If you wish to make field names easier to read, consider using an underscore character to separate words. However be certain no spaces appear before or after the underscore.

2.2 Viewing and Adding Data to a Table

Data can be added, deleted or modified in tables using 'datasheet view', a simple spreadsheet-like display. To bring up this view of the Customer table, highlight the name of the table and then click on the Open button. You can switch between Design view and datasheet view at any time using the View icon in the toolbar.

In this view of the table, shown in the figure below, the fields (columns) appear across the top of the window and the rows or records appear below. This view is similar to how a spreadsheet would be designed.

Note at the bottom of the window the number of records is displayed. In this case, since the table was just created, only one blank record appears.

To add data to the table, simply type in values for each of the fields (columns). Press the Tab key to move between fields within a record. Use the up and down arrow keys to move between records. Enter the data as given below:

so that the finished table looks like this:

Instead of typing all the data by hand you may be tempted to copy and paste from the above table into your Customer table - you are welcome to try but it may be a bit more complicated than you expect. Copying the contents of a single cell and pasting it into your new table is easy enough but if you select the whole range of cells you probably won't be successful when you try to copy and paste them into your table. The (rather annoying) trick to make it work? Paste first into an Excel spreadsheet then copy the cells again in the Excel spreadsheet then make sure that your new table has at least four rows then, in the new table, select the same area that you copied originally (six columns by four rows)  then paste!

To save the new data, choose File>Save.

To modify existing data, simply click in the cell you want to change, then type a new value.

To delete a record, first select the record of interest, then choose Edit>Delete Record.

To close the table and return to the Access main screen, choose File>Close or simply click the close box of the table window. Close the Customer table now.

At this point in the tutorial, you have two tables, Customers and Accounts, and each one contains data. In the subsequent sections, we will cover how to query and report on the data in the tables and how to create a user-friendly data entry form using the Access wizards.

2.3 The Accounts table

Before you go any further, open up the Accounts table and look at it in both design and normal (datasheet) views - make sure everything is as you expect. Remember you can switch between Design view and datasheet view at any time using the View icon in the toolbar. In design view, pay attention to which field was designated as the primary field. It cannot be the CustomerID for the CustomerID numbers are not unique in this table - multiple accounts can belong to the same customer. Recall that the primary key must be a field that contains unique values - which field is the primary key in this table? In the 'Date Opened' field, note that Access only displays the last two digits of the year. All four digits are stored in the table, however, so when entering the dates it is important to type in the full four digits for the year.

2.4 Creating Relationships Between tables

Before you set up relationships, close any open tables since otherwise certain options may not be available later.

To get started making relationships, display the Relationships screen by choosing Tools>Relationships. The blank Relationships screen will appear. The next step is to make sure that the window displays all the tables between which we wish to set up relationships. If you do not already see the Customer and Accounts tables then right-click anywhere on the Relationships screen and select the Show Tables... option from the pop-up menu:

When the Show Table dialog box appears, highlight both the Customers table and the Accounts table as shown below and then click on the Add button.

Then click on the Close button to close this dialog box. The Relationships screen will now reappear with the two tables displayed as below:

To connect the Customers table with the Accounts table to form a relationship, click on the CustomerID field in the Customers table and drag it over on top of the CustomerID field on the Accounts table. It doesn't matter which way you drag - Access will spot that the CustomerID filed is the primary key of the Customer field (which is why it appears in bold) and will assume that this is the 'one' side of a 'one-to-many' relationship. This makes the Accounts table the "Many" side as One customer may have Many accounts. Upon releasing the mouse button, the Edit Relationships dialog box will appear as below:

One additional step to be taken is to check the box labeled "Enforce Referential Integrity". This option puts constraints into effect such that an Accounts record can not be created without a valid Customer and Access will also prevent a user from deleting a Customer record if a related Accounts record exists. At this point, click on the Create button to create the relationship. The Relationships screen should reappear with the new relationship in place as follows:

Note the symbols "1" (indicating the "One" side) and the infinity symbol (indicating the "Many" side) on the relationship. Close the relationships screen and select Yes to save the changes to the Relationships layout. If you cannot save your changes it is probably because you did not close all open tables before setting up the relationship and this means you cannot turn on the option 'Enforce Referential Integrity' so in that case press cancel, close the tables and try again.

If the relationship does not appear in the above fashion, highlight it and press the delete key to delete it. Then go back to the table design view and make certain that the CustomerID field is designated as the key of the Customers table. Then go back to the Relationships screen and try to recreate the relationship.

2.5 Review of Creating and Viewing Tables

1. Click on the Tables tab on the Access main screen
2. Click on the New button.
3. Choose the Design View and click the OK button.
4. Fill in the name, data type and description of each of the fields in the table.
5. Designate a primary key by clicking on one of the fields with the right mouse button and then choose Primary Key from the pop-up menu.
6. Save the table by pulling down the File menu and choosing Save.
7. Close the new table by pulling down the File menu and choosing Close.

To change the design of an existing table (e.g., to add, change or delete a field):

1. Click on the Tables tab on the Access main screen
2. Highlight the name of the table to be modified and click on the Design button.
3. Make the necessary changes.
4. Save the table by pulling down the File menu and choosing Save.
5. Close the table by pulling down the File menu and choosing Close.

To add, delete or change data in an existing table:

1. Click on the Tables tab on the Access main screen
2. Highlight the name of the table to be modified and click on the Open button.
3. Make the necessary changes to the data.
4. Save the table data by pulling down the File menu and choosing Save.
5. Close the table by pulling down the File menu and choosing Close.
    

1. close any open tables.
2. Pull down the Tools menu and select the Relationships menu item.
3. To display tables, right click and choose Add Tables
4. To create new relationships, drag a key field from one table and drop it on the associated field in another table
5. To edit an existing relationship, double click on the relationship line.
6. To delete an existing relationship, click on the relationship line and press the delete key.