Getting Started with Access
Getting started with Access can seem rather daunting, but it is not insurmoumtable. This Getting Started lesson will give you an overview of Access terminology and explain the basic concept of database design.
Note: These lessons are excerpted from Luther Maddy’s Access Workbooks (C) 2024 which are available in printed or eBook format from Amazon: Access: The Basics and Access: Beyond The Basics
MS Access- File Downloads & Other Resources
Microsoft Access Terminology Guide
Watch the video: Access Database Terminology
Watch the video: Understanding Relational Database Design
Access Terminology
A database is an organized collection of information. Access makes it easy not only to organize data but also to manipulate and analyze the data it contains. You need to be familiar with a few database terms before you learn how to use a database.
Field: A field is a piece of information such as a First Name, City, or State. The field is the most basic element of an Access database.
Record: A record is a collection of related fields. An example might be a table of clients. Each record stores all information about a client in fields such as: First Name, Last Name, Street Address, City, State, Zip Code, Phone Number, and Birthday.
Table: A table is a collection of records with the same fields, as in our example of a client table. The records consist of rows in the table and the fields are the columns. For example, a table named Employees would be expected to only include company employees.
Database file:The database file is a collection of related tables. The file also contains the other Access database objects such as the Forms, Queries, Reports and Macros that comprise a specific database.
Query: A query is an Access object that allows you to specify which fields and records you want to see. Queries are often used as the basis of reports. The query selects the records and fields to view and the report allows you to enhance the appearance of the resulting list produced.
Form: A form is a way to view, enter, edit, or print records in a table. You can design the form to match existing forms you already use.
Report: Reports allow you to present and view selected information in a meaningful way. The most common report layout organizes the information in columns. You can use reports to create totals and sub-totals.
Database Design
When you begin to create an Access database, think before you act. You should spend considerable time designing the database before you even start Access. The database design includes the tables and the fields they contain. You also need to establish the relationship between the tables.
Ideally, the design should be complete before you start using the database to prevent important tables or fields from being left out. Although Access allows you to change the design of a working database, you will often need to modify the forms, reports, and queries that refer to the table or tables which have been changed.
Changing the design of a database in use is often time consuming and difficult, depending on the number of existing forms, reports, and queries. Remember the old adage, a stitch in time saves nine. Plan for every possible use of the database during the design phase: every question it might be asked and every report that might be wanted before you begin to create the database.
Relational Database Design
Access has relational database capabilities, which means it can link or relate two or more tables. Relating tables allows Access to look up information from either table when needed.
A well-designed relational database design saves in the duplication of information. With an invoicing database, instead of entering a customer's name and address each time he or she makes a purchase, you can store customer information in a separate Customer table. When you create a new invoice stored in the Invoices table, the Invoices table needs to contain a field that links to the specific customer record in the Customers table, often a customer number.
To relate two tables, each must have one field in common. This field must also be the primary key in the table representing the “one” of the "one to many" relationship. In this invoicing example, one customer may have many invoices.
Relating these two tables saves having to enter the complete customer name and address on each invoice each time he or she places an order. If you find yourself duplicating the same information, your database design is suboptimal.
Relational databases also allow you to maintain historical data. In the invoicing database, you have a record of every invoice your customers have. This process is referred to as normalization, which means that the database is optimally designed to reduce redundancy and to improve the data integrity.
Ensuring Data Integrity
Data integrity refers to having correct or valid data in the database. You can address this in the design process by identifying how to restrict a user’s ability to enter incorrect data. One way to ensure data integrity is to limit the type of allowable data entered in a field, which can be done by setting specific options in “Field Properties”. You will learn about this is a future lesson, but remember data integrity should considered during the database design process.
