How do I learn Microsoft Access

Access for Beginners - The Basics

In our tutorial for Microsoft Access we use examples to guide you through all the basics of Microsoft's database software. You will learn how to create a database in Access, create forms & reports, link data types and work with macros to store data or create queries. At the end of the guide you will find templates for Microsoft Access, e.g. to transfer Excel tables to an Access database, to operate an FTP server with Access or to import stock prices from Yahoo. Our example illustrates Access to create a customer database.

myKey is your dealer for Microsoft Windows & Office as second hand software. In our Microsoft Software Shop you can conveniently buy product keys for immediate download. You save up to 75% on the original prices without compromising on new licenses.

In our guides for Office 2010, Office 2013 & Office 365 you will find everything about features, top functions and differences between the different Office versions. All of our licenses are checked and legally secure.

Microsoft Access explained briefly and simply

Basically, Microsoft Access is a database management system that is used to store large amounts of data for a project or a company. The actual database, i.e. the entered data itself, change over time, but always remain stored in the same tabular structure.

Microsoft Access, for example, is ideally suited for storing customer, product and project data and for linking them to one another. A well-managed database enables quick inquiries about specific customers and the products linked to them. The following tutorial works with Microsoft Office 365 Pro Plus. In principle, all available functions - unless otherwise noted - can also be accessed in previous versions of Microsoft Access from 2007 onwards.

Create databases in Access

When you have opened Access, you will see an empty table, which must always be named first. In this example, customer data is to be saved. To do this, click on Save and write in the field under "Table name:" Customers. Before you can enter data, a meaningful structure must first be created. This is possible with the draft view, which you can find at the top left.

No data is entered here, but field names are named, i.e. fields such as first name, last name, customer number or telephone number. Microsoft Access automatically creates a field with the name "ID", this should remain. It counts the records.

Enter the terms in the "Field name" column, and in the "Field data type" column you specify the entered data type, which you can select from a drop-down menu. The most important data types and field properties are explained in the next section. In this example, the customer and telephone number are also texts, since the data type "number" is only selected if calculations are also carried out with it. Then go back to the data sheet view to enter the actual data. Your data sheet should then look like this:

Programming in Access - an introduction

Now that you've created your first table, you want to make it easier for you to maintain it. To do this, you can use forms to simplify data entry, create reports to evaluate the data and link various tables with one another so that, in our example, you have a separate table for all customer data and can connect it to the table for all product data.

Forms

In order to simplify the data entry and not have to open the table every time, it makes sense to create an entry form. To do this, go to the “Create” menu and open the form wizard. Now select the table in which you want to add data and which data you want to enter. It should now look like this:

Then click on “Finish” and all the data records that have already been entered will now be displayed in a clear form.

You can click through the data sets with the arrow buttons and you can enter new data sets by clicking on the arrow button with an asterisk. To save this after entering it, press the "Save" symbol.

Reports

Access also offers the possibility to generate reports. To do this, go to the "Create" menu again and click on the report wizard. As with the creation of the form, you select the table from which the data originate and the data itself that should appear on the report. You can then click on “Finish” for a basic report and the data you have entered will appear in a graphically prepared form that you can either print immediately, save as a PDF or send as an e-mail.

Link tables

Let's now turn to joining two tables. To do this, we first create a second table with the name "Products". This table should be used to enter which products were sold to whom and at what price. To create additional tables, go to the “Create” menu and click on “Table”. You can now either enter the data directly into the table or you can create a form for it. Before doing this, you have to go back to design view and structure your table. It is essential that you - this is fundamental - create a value that appears in both tables, e.g. customer number. You will later link the tables based on this value.

To link these, go to the “Database Tools” menu and click on “Relationships”. Then add the tables that you want to link together. Now drag the customer number from the "Customers" table to the corresponding field in the "Products" table. After that, a dialog box will automatically open and you can edit the relationship:

Here just click on "Create". The line between the two tables shows the relationship. These relationships will be very helpful later in the queries.

Data types and field properties

As mentioned earlier, fields can be of different data types. In the following, we will introduce you to the most important data types.

Short text

As the name suggests, this is a short text with a maximum length of 255 characters. This data type is used, for example, when names are to be entered. In older versions this data type is called “Text”.

Long text

This data type allows longer texts to be entered, such as a detailed product description or additional comments. In earlier versions this data type was called “Memo”.

number

Numbers can be entered here in order to be able to carry out calculations. The input options are limited to -2.147.483.648 to 2.147.483.64. An example of this would be the number of pieces of products.

Large integer

If the data type number is too small, this can be used.

Date / time

This data type is also quite self-explanatory, e.g. a sales date could be entered here.

currency

Numbers entered in a field with this data type are automatically represented as currency. The default setting is euros.

Yes No

If you select this data type, a box appears in the table that can be clicked. Then a check mark appears, symbolizing "Yes".

OLE object for attachments

By selecting this data type, other Office documents can be linked to the data record. In the table, the data type is shown as a staple. Attachments such as contracts or invoices can be added by double-clicking the field in the table.

link

The data type enables a data record to be linked to a website / URL, e.g. the associated product page.

investment

This allows images to be linked directly to the data record.

Calculated

This data type enables calculations to be carried out automatically. However, you have to specify what is to be calculated in the design view. So if you select Calculated as the field data type, a dialog box will appear automatically. Here you select products for expression elements and the fields with which you want to calculate for expression categories. For the expression elements, you also select the operators (add, multiply, etc.). The window should now look like this, for example:

Queries and macros

With queries you analyze data records from different tables and present them clearly. Macros are used for automation, e.g. to enter new customer data or to send regular reports.

Interrogate

Queries are a very useful tool for visualizing data from linked tables. To create a query, go to the "Create" menu and click on the query wizard. For the beginning it is sufficient to use the selection query wizard. For example, to display which products which customer has bought, select the relevant fields from the Customers and Products tables. Your data selection could look like this, for example:

9 Data field selection

Now click on “Finish” and the result will be a new table that has combined the data entries from the two original tables. Of course, there are also far more complex query options, which will not be discussed in detail here, as this type of query is usually sufficient for normal commercial use.

Macros

Macros are there to automate a certain series of actions, e.g. the import of data from other sources. As a simple example, we will show you the creation of a macro to create a new data record in the form view.

To create a macro, go to the “Create” menu and then click on the “Macro” icon. If you now look at the drop-down menu, you can already see how many possible uses there are for macros. Now select the item "OpenForm". For "Form name", select the appropriate form (in this example "Products").

Now add a new action, namely “GoToData record”. This action is necessary to determine what should happen after the form has been opened. For object type select "Form" and for object name again "Products". With Dataset you can choose which dataset is displayed, ie the first, the last, etc. Now select "New". Then save the macro.

If you now run the macro, a form will automatically open to enter new data records in the product table.

The best templates for Access databases

Thanks to the work of Microsoft itself and of many volunteers, there is a whole range of templates for Microsoft Access that you can use to access ready-made databases. In addition to the Microsoft templates already integrated in Access, you will find other free templates for various purposes, queries & macros on the following pages, which you can use freely:

You can find the Microsoft templates for Access in the program itself by creating a new database via New / Sample Templates.

Equipped with these basics for Access, we wish you every success in testing. This tutorial brings you myKey - Buy Windows & Office Product Keys cheaply - with us you save up to 75% on the new price. After purchasing in the online shop, you will receive your license key with software and installation instructions conveniently by email for direct download.

In our legal basis for used software, we explain how software is checked by us. This protects you from unintentionally using illegal software privately.