MS Access 2010 - Lesson 2: Introduction to objects in Access

Database in Access 2010 includes four objects: Table, query, form and report. These objects work together to allow you to import, store, analyze and compile your data the way you want.

In this lesson, you will learn about each object and see how they interact with each other to create a fully functional relational database.

Tables, queries, forms and reports are frameworks for any database you create in Access. Understanding how each of these objects works will help you create a useful database and help you get the information you need.

Table (Table)

Up to this point, you have understood that the database is a collection of data arranged into multiple lists and connected. In Access, all data is stored in tables. These tables are like the 'heart' of any database.

Tables are arranged into vertical columns and horizontal rows.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 1

In Access, rows and columns are called records and fields. A field is not just a column. That is how to organize information by data type. All information in a school has the same type. For example, every entry in the First Name field will be the name and each entry in the Street Address field will be an address.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 2

Similarly, a record is not just a row. It is an information unit. Each cell in a given row is part of a record that belongs to that row.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 3

Note how each record spans multiple fields. Although the information in each record is organized into fields, it also belongs to other information in that record. Do you see the number on the left of each row? That is the ID number that identifies each record. The ID number for a record refers to all information contained in that row.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 4

Tables are a way to store closely related information. Suppose you own a bakery and have a database that includes a table containing your name and customer information, such as their phone number, home address and email address. Because this information is all about your customers, put them in the same table. Each customer will be represented by a single record and each type of customer information will be stored in their own field. If you decide to add any more information (eg customer birthday), you only need to create a new field in the same table.

Form, query and report (Forms, queries and reports)

The table stores all your data, three other objects (forms, queries, and reports) that provide you with ways to work with that data. Each of these objects interacts with records stored in database tables.

Form (Form)

The form is used to import, modify and view records. You may have to fill out forms in many cases, such as when going to a doctor's office, when applying for a job or registering for school. Forms are used frequently as an easy way to guide people to enter data correctly. When you enter information into a form in Access, you will be taken to the place you need to go, to one or more related tables.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 5

Forms make data entry easier. Working with extension tables can be confusing and when you have linked tables, you may need to work with multiple tables at once to import a data set. However, with forms that can import data into multiple tables at the same time, everything will all gather at one point. Database creators can even set restrictions for each individual form component to make sure all necessary data is entered in the correct format. In short, the form helps keep data consistent and organized. It is essential for an accurate and powerful database.

Query (Query)

Query is a way to search and compile data from one or more tables. Running a query is like asking a detailed question about your database. When you create a query in Access, you specify specific search conditions to find the exact data you want.

Query is much more powerful than simple searches that you can do in the table. While searching can help you find the name of a business customer, you can run a query to find the names and phone numbers of every customer who made a purchase in the past week. A well-designed query can provide information you can't find just by looking at the data in the tables.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 6

Report (Report)

The report gives you the ability to present available data. If you have ever received a printed calendar or bill of a purchase on a computer, you have seen a report about the database. The report is useful because they allow you to present the components of the database in a readable format. You can even customize the look of the report to make it more attractive and intuitive. Access gives you the ability to create reports from any table or query.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 7

Combine objects together

Even if you really understand how to use each object, initially how they work together can still be confusing. Remember that all these objects work with the same data. Each data section in a query, form, or report is stored in one of your database tables.

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 8

The form allows you to add data to the table and see if the data already exists. Reports present data from tables and also from queries, then search and analyze data in the same tables.

These relationships may sound complicated, but in fact they work together so well and naturally that we often don't notice, when using connected database objects. Have you used the electronic card catalog to search for a book at the library? Chances are, you have entered your search information into something that looks like this:

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 9

When you perform a search, you enter your search term into a form, which is then used to create and run a query based on your request. When the query completes the search in database tables for records that match the search, you'll see a report that Access has collected information from the query and related tables, In this case is a list of books that match your search terms. You can imagine the connections between objects as follows:

MS Access 2010 - Lesson 2: Introduction to objects in Access Picture 10

Suppose instead of using these tools, you have to search in a giant table containing all the books in the library system. Related records can be spread out across multiple tables: A table for book titles and descriptions, a table that contains information about books that have been borrowed or returned, and a table with each branch of the library.

You will have to search at least three tables just to find a book, its location and see if it has been borrowed yet! It's easy to imagine how difficult it would be to find the right book. If you are not careful, you can even spoil something by accidentally deleting or editing a record. It's easy to see how objects in the database make this search easier.

In this article, we discussed the concept of a relational database. It is a database that can understand how different data sets are related to each other. Situations like the example above are the reason why people find relational databases very useful. Without a relational database, a simple task, such as finding a book and whether it is borrowed or where it is located, becomes extremely complicated and time-consuming. Knowing how to use four Access objects can make complex tasks more user-friendly.