Access Tutorial: The basics explained
Would you like to create a database with Access for the first time? In this tutorial we explain the most important basics and terms from Access Version 2010.
Structure of an Access database
In addition to the tables, queries, forms, reports, macros and modules are used in Access.
- All data is initially organized in tables. A table can contain addresses or article information, for example. The data are kept in columns and rows.
- The rows are called records and the columns are called fields.
- With queries you can filter, combine or calculate the data contained in the tables. For this purpose, various tables are connected to one another via relationships if necessary.
- The data is entered by the respective user in forms. The data can also be viewed in forms. You can have query results output in forms.
- Reports are similar to forms and are used for printing.
- Macros are used to automate work processes. Several actions, such as menu commands, can be carried out automatically one after the other.
- In modules, you can save functions and procedures that you have programmed yourself using VBA.
Design an Access database
A database starts in your head. It is important to define the structure in advance.
- Think about which data (e.g. articles, orders, customer information) should contain your database.
- Divide the various dates into topics. Each topic is later recorded in a separate table.
- Determine what information needs to be stored in each table. In a table with articles, for example, you need the article name, article number and price as column headings.
- To avoid duplicate data records, define a so-called primary key. This ensures that redundant data cannot even arise. For articles, the article number would be the primary key.
- Define the relationships between the individual tables. To create queries from different tables later, the tables must be linked. When ordering, for example, article numbers are also recorded. So the relationship between the Item table and the Orders table would be the Item number field.
Views in an Access database
You always have at least two different view types for each object.
- In Design view, you specify which fields to use and set options. You design the view in forms and reports.
- The data sheet view shows the contents of tables and the results of queries.
- For forms and reports, the data is displayed in the form or report view.
Create the first simple Access database
The beginning of every database is the table.
- From the Build menu, navigate to the Table Design button.
- Enter the name of the fields in the "Field name" column. Name the fields, e.g. article name, article number, etc.
- Then you define the data type. In the case of an article name, the text field is a good choice. Access provides you with a selection here.
- If you switch to the data sheet view, you can enter test data.
- If you have created several tables, now define relationships between the tables. To do this, select the "Relationships" button in the "Database tools" tab.
- With the mouse you can draw a line between the fields.
- To display filtered data in a form or report, select the "Query Design" command in the "Create" tab. Add one or more tables from which you want to use fields by clicking.
- Select the fields you want to use from the tables. Either drag a field from the list to an empty column or double-click an entry in the list.
- If you use several tables, make sure that you have defined the relationships correctly.
- Save the table.
- To create a form, go to the "Create" tab and select "Form Design".
- Then click on "Add existing fields" in the "Tools" group. Now select the fields from tables or the query you just created and place them in the draft.
- You have now created your first simple database in Access.
In the next practical tip, you will read how to carry out a crosstab query in Access.