Why learn to perform basic tasks in Microsoft Access?
In previous courses, we learned about the benefits of using Excel to manage spreadsheets. When it comes to quick data entry by users, manipulating numerical data for fast analysis, having rich formatting features and mathematical functions, spreadsheets are hard to beat. As the volume of data increases, databases become a viable option. Databases are geared for long-term data storage of large data sets, maintaining multiple relationships between data values, and the ability to update forms, queries, and reports automatically when values are updated.
Think of spreadsheets as ledgers: you have a list of customers and may wish to know who are your top five most important based on annual revenue. Think of databases as intelligent file cabinets: you may wish to know who are your top five most important customers, what was the date of their last purchase, and what products did they purchase. It is this ability to handle the relationships within the data, in addition to the amount of data, that makes databases a powerful business tool.
Introduction to Creating Database Tables
The building blocks of relational databases are the tables that organize and store information within the database. In this section, you will learn how to create a blank database, assign a name to it, and specify a location. Next, we will explore the different types of tables based on the purpose they serve. Finally, we will create a database table using the appropriate commands in Access.
Understanding Tables
Tables are one of the key objects in Access. Access uses tables to store data. To store data, you create one table for each type of information that you track. Types of information might include customer information, products, or order details.
The example below is taken from the Microsoft Access “Northwind Traders” sample database. It is a table containing a list of orders for the fictitious company with reference to Order Number, Order Date, Order Status, etc.
Types of Tables
There are three types of tables in Access: an object table, a transaction table, and a join table. Object tables contain data relating to a real-world object, like a company or a customer or a product. Transaction tables store information pertaining to events, like when an order is placed for a product or an invoice is sent to a customer. Finally, join tables are used to establish a relationship between two tables, like a product table and a customer table.
You can add new tables to an existing database by using the commands in the Tables group on the Create tab. Access will automatically create a new table with a table structure built in. Access will also set the field types based on the data entered. The new table will be automatically displayed in Datasheet view, allowing you to take immediate action, i.e., enter change field names, set properties, and enter data directly in to the table.
An alternate way to create a new table is to select Table Design in the Tables group. Access will create a new table and display it in Design View. This approach provides a more traditional albeit manual method of setting the details of the table.
Introduction to Fields in Access
If tables are the building blocks of databases, fields are the key component of tables. We will explore field properties which tells Access what type of data will be entered into a specific field. Then we will discuss the importance of assigning names to each field and the rules for ensuring a valid field name. Finally we will get into more detail regarding data types and field properties.
Regardless of how you created your table, it is a good idea to examine and set field properties. While some properties are available in Datasheet view, some properties can only be set in Design view. To switch to Design view, right-click the table in the Navigation Pane and then click Design View. To see a field’s properties, click the field in the design grid. The properties are displayed below the design grid, under Field Properties.
To see a description of each field property, click the property and read the description in the box next to the property list under Field Properties. You can get more detailed information by clicking the Help button.
Field Properties sets boundaries on what/how data is entered into table fields. There are five types of field properties:
- Field Size: sets the maximum number of characters that can be entered into a text field.
- Format: sets how the data is to be displayed in the table field.
- Input Mask: sets the rules as to how data can be entered so that data is entered correctly.
- Default Value: sets the default value that will appear in the table field each time a new record is entered.
- Required: sets whether a value is required to be entered into this table field.
Field Names and Data Types
Field Names
A field name should be descriptive enough to identify the purpose of the field, without being overly long to prevent excessive typing. Enter the field name by placing the pointer in the first row of the Table Design window under the Field Name column. In order to ensure a valid field name, a field name:
- Cannot exceed 64 characters,
- Cannot include periods (.), exclamation points (!), accent grave (`), or brackets ([]),
- Cannot include spaces,
- Cannot include low-order ASCII characters,
- Cannot start with a blank space.
Data Types
The data type must be consistent with the data to be stored in the field. The “text” type is the most commonly used data type, including numbers that will not be added like social security or street address numbers. Here is a partial list of Access data types:
| Data Type | Description |
|---|---|
| Short text | Alphanumeric characters |
| Long text | Alphanumeric characters |
| Number | Numeric values |
| Large Number | Numeric values |
| Date/Time | Date and time data |
| Currency | Monetary data |
| AutoNumber | Automatic number increments |
| Yes/No | Logical values: Yes/No, True/False, etc. |
| OLE Objects | Pictures, graphs, sound, video |
| Hyperlink | Line to an Internet resource |
| Attachment | External files |
| Calculated | Stores calculations based on other fields |
| Lookup Wizard | Displays data from another table |
Primary Key
Every table must have a primary key—a field with a unique value for each record. Otherwise there would be no way to distinguish between records. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key.
For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key of the Customers table. When a primary key contains more than one field, it is usually composed of pre-existing fields that, taken together, provide unique values. For example, you might use a combination of last name, first name, and birth date as the primary key for a table about people.
Choosing a Primary Key
Without the ProductID field, another field would be needed to uniquely identify each product in the database. Without a strong primary key, the database engine would be at a loss to perform basic operations on the table properly.
Good primary keys must:
- Uniquely identify each record,
- Not be null,
- Exist when the record is created,
- Must remain stable,
- Be simple and contain as few attributes as possible.
A primary key can be set for a table in one of three ways. Open a table in Design view and:
- With the field to be used as the primary key selected, click the Primary Key button key in the Tools group.
- Right-click the appropriate field and choose Primary Key from the shortcut menu.
- Allow Access to automatically create the primary key by simply saving the table.
In this example, Field One was highlighted and Primary Key was selected in the Menu bar to designate it as the Primary Key.
Note: Field names typically don't use Field One, Field Two in databases, screen shot depicts primary key.
*****
Word revisions in red font.
- Why It Matters: Microsoft Access Basic Skills. Authored by: Robert Danielson. Provided by: Lumen Learning. License: CC BY: Attribution Links to an external site.