Microsoft Access 2007 Tables Tutorial
Getting Started with Microsoft Access 2007: A Table Creation Tutorial
Previously, you may have encountered my initial post regarding Microsoft Access 2007. However, it lacked practical guidance on performing specific tasks.
This article provides a Microsoft Access tutorial focused on the creation and utilization of tables. Subsequent posts will cover queries, forms, reports, and automation techniques.
Assumptions and Compatibility
To keep this tutorial concise, we'll operate under a few assumptions. Firstly, familiarity with spreadsheet software, such as Excel, is expected. Secondly, while this guide is tailored for Access 2007, the core principles generally apply to other versions, though minor adjustments may be necessary.
From Spreadsheet to Database: The Need for Tables
Consider the following spreadsheet example.
This represents a typical list of items in Excel. Imagine this list expanding to several thousand rows; you might recognize that this format isn't the most efficient method for managing such data.
Ideally, you should have your own data to practice with. A hands-on example will greatly enhance your understanding.
Data Consistency in Databases
In Access, this information would be stored in tables. However, before creating tables, ensuring data consistency is crucial. Databases demand a higher level of consistency than spreadsheets.
Certain elements within the example data require correction. For instance, inconsistent entries in the Finished column, such as the word "Never," and the use of "x" in the Rating column, need to be standardized. Leaving cells blank is generally acceptable.
Creating Your First Access Database and Table
Let's begin using Access and make some initial decisions. Two preliminary steps are required before table creation.
First, select the option to create a blank database, rather than utilizing a pre-built template. Explore the templates later for additional examples.
Secondly, assign a name to your Access project at the outset. This differs from other Office applications where you can begin working before naming the file.
Click Blank Database, enter a file name, and click Create.
Access automatically creates a default table to assist you. However, we will design the table manually for optimal structure.
To proceed with manual design, click the X in the top right corner to close the default table. Then, click the Create tab in the ribbon and select Table Design. If you encounter difficulties, close Access without saving and restart.
Remember, define your requirements before designing the table and adding data. Modifying the structure later can be cumbersome.
Defining Table Fields
Our primary task is to determine the fields we want to store in the table and the type of information each field will contain. If you're using the example data, configure the fields as shown.
Relational Databases and Normalization
Before continuing, it's important to understand a key concept: Access is a relational database. This means you can work with multiple tables simultaneously and define relationships between them.
Relational databases aim to minimize redundancy and wasted space. This process is known as normalization. Further research into normalization is encouraged, but we will focus on a practical example here.
Notice that the same sci-fi authors appear multiple times in the spreadsheet. To replicate this in Access, we'll create a separate table specifically for authors and then relate it to the book table. This ensures each author is listed only once, reducing the potential for errors.
For now, define the author field as a Number.
Saving Your Table
Save the table by clicking the Save button on the toolbar.
You'll be prompted to name the table. Book is a suitable option. Remember, table names are internal components of the database, similar to sheets in an Excel workbook.
Before saving, Access requires one more step. Every table should have a field with a unique value for each row. This is typically an ID field.
Click Yes on the dialog box…
…and a new field is added. The field type is Autonumber, automatically assigning a unique value to each new row. Save the table again and close it.
Repeat the process to create an Author table, mirroring the structure shown.
Relating the Tables
Now, we need to relate the tables to each other.
Click the Database Tools tab and then the Relationships button.
Add both tables using the Show Tables dialog.
Close the dialog and take a moment to understand the concept of relationships.
A common relationship type is many to one. In our case, each author can have multiple books. To create this relationship, click on ID in the Author table and drag it to the Author field in the Book table.
Access will prompt you for relationship details. Tick the Enforce Referential Integrity box and click Create.
Close the relationships window and save the changes.
Setting Up a Lookup Field
To improve usability, we'll set up a lookup field in the Book table to display author names instead of IDs.
Open the Author table and add some author data.
Open the Book table in Design view. Click on the Author field and then on the Lookup tab below the field list. Configure the fields as shown. A more detailed tutorial will explain the settings further.
Close the table, save it, and reopen it in Datasheet view.
A combo box will appear in the Author field. Click the arrow to select an author from the list. Fill out the remaining records.
You have now successfully replicated the spreadsheet data in Access tables and established a relationship between them. This foundation allows you to build queries, forms, and reports that surpass the capabilities of Excel.
Further exploration will be covered in subsequent posts.
How did this Microsoft Access tutorial go for you? Have you read any of the books? Share your thoughts in the comments.
