Introduction to tables, queries, forms, reports in Access

Access database includes 4 objects: tables, queries, forms and reports. Combining these 4 objects together can import, store, analyze and compile data the way you want.

In this lesson, we will learn about them, how they interact with each other to create a fully functional relational database.

Access tables

Within the scope of this tutorial, you know that the database is a collection of data organized into multiple linked lists. In Access all data is stored in the table, the table is the center of the database. Tables are organized into vertical and horizontal columns.

Introduction to tables, queries, forms, reports in Access Picture 1
An example of a table in Excel

The rows are called records and columns are fields. A field is not just a column, it's a way to organize information by data type. Each piece of information in the school is of the same type. For example, every entry in the First Name field will be the name, and all entries entered in the Street Address field are the address.

Introduction to tables, queries, forms, reports in Access Picture 2
Field in Access 2016

Similarly, a record is not just a row, it is an information unit, each cell in a given row is part of the record of that row.

Introduction to tables, queries, forms, reports in Access Picture 3
Record in Access 2016

Notice how each record intersects with the fields. Although the information in each record is organized into fields, it still belongs to other information in that record. For example, the ID number identifies a certain record, the record ID number will refer to all the information contained in that row.

Introduction to tables, queries, forms, reports in Access Picture 4

Tables used to store information are closely related to each other. Suppose you have a bakery, a database that includes a table with name, customer information (phone number, address, emal). Because this information includes all details about the customer, you save them in the same table. Each customer is represented by a single record and each type of information about those customers is stored in their own field. If you want to add any more information such as a customer birthday, for example, you only need to create a new field in the same table.

If the table helps store all data, the other 3 objects are forms, queries, and reports that allow you to work with the table. Each of these objects will interact with the records stored in your database table.

Form in Access

Form (form) is used to import, edit, view records. You may have to fill out the form multiple times when logging in to the website, enrolling, etc. The reason the form is used so often is because it is the easiest way to instruct the user to enter the correct data. When filling in information in Access form, the data will be entered in the format that the database designer wants: In one or more related tables.

Introduction to tables, queries, forms, reports in Access Picture 5
Form in Access 2016

Form makes data entry easier. Working with large tables can be confusing and especially when the tables are connected, you may have to enter the data set more than once. However, with the form, it is possible to enter data into multiple tables at the same time, all in the same place. Database designers can place additional restrictions on separate forms to ensure all necessary data is entered in the correct format. In general, forms make data consistent, organized, and that's essential for a robust, accurate database.

Query in Access

Query is a way to search, compile data from one or more tables. Query execution is like asking a detailed question about a database. When creating a query in Access, you are defining specific search conditions to find exactly the data you want.

The query is much stronger than the simple searches you do on the table a lot. Although searching can help find the name of a customer in the list, but if you do a query, you can find the names and phone numbers of many customers who have purchased goods in the past week. Well-designed queries can provide information that you can't find just by looking at the tables in the database.

Introduction to tables, queries, forms, reports in Access Picture 6
Illustrate a query in Access 2016

Report in Access

The report gives you the ability to present data in a printout. If you have ever received a computer printout of your schedule, purchase invoice, then it is a database report. The report is useful, as they allow the presentation of the components of the database in an easy-to-read format. It is even possible to customize the appearance of the report to make it more attractive and intuitive. Access provides the ability to create reports from any table or query.

Introduction to tables, queries, forms, reports in Access Picture 7
Report in Access 2016

Combination of tables, forms, queries, reports

Although you can understand how objects are used, you will initially be somewhat vague about how they work together. All these objects work with the same data. Each piece of data that queries, forms, and reports use is stored in a database table.

Introduction to tables, queries, forms, reports in Access Picture 8
Combination of tables, forms, queries, reports

Form allows adding data to the table and viewing existing data. The report presents data from tables and queries, then searches and analyzes data in similar tables.

These relationships may sound complicated, but they work together very well and naturally that we often don't pay attention to when used together.

An example of you looking for a book in the library, for example, would look like this:

Introduction to tables, queries, forms, reports in Access Picture 9
Example of finding books at the library

When searching, you enter the search terms into the form, create and run the query as required. When the query finishes searching for records in the database table that match the request, you will receive a report. This report collects information from queries you have made and related tables, in this case a list of books that match your search terms. Can show the connection between these objects as follows:

Introduction to tables, queries, forms, reports in Access Picture 10
Describe the combination of forms, queries, tables, reports in Access

Without these tools, you will have to search for yourself in a huge bookstore, related records can be on multiple tables, if you find them manually or with your eyes, you will never know.

Next lesson: Get familiar with Acccess 2016 interface and basic operations

Previous article: Introduction to database - Database

Article source: gcflearnfree.org