Download a PDF of this page here.
Download the Spanish version here.
Essential Concepts
Creating Databases
Creating a database in Access involves several key steps:
- defining the purpose of the database,
- gathering all necessary data and materials, and
- determining the structure and relationships of the data.
Naming databases and their components requires following best practices such as using clear, descriptive names that indicate the content or purpose, avoiding spaces and special characters, and maintaining a consistent naming convention to enhance readability and manageability.
Creating a table in Access involves specifying the fields and their data types, setting primary keys, and entering data into the table. It’s essential to choose appropriate data types for each field to ensure accuracy and efficiency in data storage and retrieval.
Fields in Access
Field names and data types are critical components of a database table. Field names should be descriptive and concise. Data types (e.g., Text, Number, Date/Time, Currency) define the kind of data that can be stored in each field, ensuring appropriate data handling and storage.
| 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 |
Table Primary Key
A primary key is a unique identifier for each record in a table, ensuring that no duplicate records exist. It is essential for establishing relationships between tables and maintaining data integrity. A primary key is usually a single field, but it can also be a combination of multiple fields.
To set a primary key in Access, select the field(s) that will uniquely identify records, then click the “Primary Key” button in the Design tab. The chosen field(s) will now prevent duplicate entries and help maintain consistent data relationships
Indexes
An index in Access is a database feature that enhances the speed of data retrieval. By creating an index on a field, Access can quickly locate and sort records, improving performance during searches and queries. Indexes are particularly beneficial for fields frequently used in sorting, searching, or joining tables. However, excessive indexing can slow down data modification operations.
Glossary
database
a structured collection of data that allows for efficient retrieval, modification, and management.
data type
specifies the kind of data that can be stored in a field, such as text, number, date/time, or currency.
disk size
the amount of storage space required to store data on a disk.
field
a single piece of data within a record in a table.
field properties
attributes that define the behavior and appearance of a field, including size, format, and input mask.
field type
specifies the nature of the data stored in a field, similar to data type.
index
a database feature that enhances the speed of data retrieval by maintaining a sorted list of the data values.
indexed field
a field that has been indexed to improve search and query performance.
input mask
a format that dictates how data is entered into a field.
join table
a table that establishes a relationship between two other tables, often used in many-to-many relationships.
metadata
data that provides information about other data, such as field names, data types, and field properties.
Microsoft Access
a relational database management system (rdbms) developed by Microsoft.
NULL
a special marker used in databases to indicate that a data value does not exist.
object table
a table that stores data about entities or objects in a database.
primary key
a field or combination of fields that uniquely identifies each record in a table.
query
a request for data or information from a database, formulated in a specific format.
transaction table
a table that records transactions or events in a database, often used for tracking changes over time.
unique
a property of a field that ensures all values in that field are distinct and not duplicated.