Access Tutorial – An Introduction to Microsoft Access
In our tutorial for Microsoft Access we guide you with examples through all the basics of Microsoft’s database software. Learn how to create a database in Access, forms& Create reports, link data types, and work with macros to store data or build queries. At the end of the guide you will find templates for Microsoft Access, z.B. to transfer Excel spreadsheets to an Access database, serve an FTP server with Access, or import stock quotes from Yahoo. Our example illustrates Access for creating a customer database.
myKey is your dealer for Microsoft Windows& Office as second hand software. In our Microsoft Software Shop you can buy product keys conveniently for immediate download. You save up to 75% on the original prices without compromises to new licenses.
In our guides for Office 2010, Office 2013& Office 365 you will find everything about features, top functions and differences between the various Office versions. All our licenses are verified and legally compliant.
Microsoft Access explained in detail and easy to understand
Microsoft Access is, very fundamentally, a database management system used to store large amounts of data for a project or company. The actual database, the data itself that is entered, changes over time but always remains stored in the same tabular structure.
For example, Microsoft Access is great for storing customer, product and project data and linking them together. A well-managed database enables quick queries on specific customers and the products associated with them. In the following tutorial we will work with Microsoft Office 365 Pro Plus. Basically, all available functions – unless otherwise noted – are also available in this way in previous versions of Microsoft Access from 2007 onwards.
Setting up new databases in Microsoft Access is easy
If you have opened Access, you will see an empty table, which must always be named first. In this example customer data should be stored. To do this, click Save and write in the field under "Table Name:" Customers. Before you can enter data, you must first create a meaningful structure. This is possible with the design view, which you can find in the upper left corner.
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 field should be kept. It counts the records.
You enter the terms at the "Field Name" column, and in the "Field Data Type" column you specify the data type entered, 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 phone numbers are also texts, since the "Number" data type is only selected if calculations are also performed with it. Then go back to the datasheet view to enter the actual data. Your data sheet should look like this:
Programming in Access – Introduction
After you have created your first table, you want to make it easier to maintain it. To do this, you can use forms to simplify data entry, create reports to analyze the data, and link different tables together, so that in our example we have a separate table for all customer data and can link it to the table of all product data.
Create forms quickly and easily in Microsoft Access
To simplify data entry and not have to open the table every time, it is useful to create an input form. To do this, go to the "Create" menu and open the form wizard. Now select the table to which you want to add data and also which data you want to enter. It should now look like this:
After that you click on "Finish" and now all already entered data records will be displayed in a clear way.
With the arrow keys you can click through the records and with a click on the arrow button with asterisk you can enter new records. To save them after entering them, press the "Save" icon.
View reports
Access also offers the possibility to create reports. To do this, go back to the "Create" menu and click on the report wizard. As with the form creation, you select the table from which the data originates and the data itself, which should appear on the report. After that you can click "Finish" for a basic report and your entered data will appear in a graphical form that you can either print immediately, save as PDF or send as email.
Linking tables
Let us now devote ourselves to linking two tables. To do this, we first create a second table with the name "Products". In this table you should enter which products were sold to whom and at what price. To create more tables, go back to the "Create" menu and click on "Table". You can now either enter the data directly into the table or create a form for it. Before that you have to go back to the design view and structure your table. It is essential – and this is fundamental – that you create a value that appears in both tables, z.B. Customer number. Based on this value, you later link the tables.
To link them now, go to the "Database tools" menu and click on "Relations". Then add the tables you want to link together. Now drag the customer number from the "Customers" table to the corresponding field from the "Products" table. After that, a dialog box will automatically open and you can edit the relationship:
Here you simply click on "Create. The line between the two tables shows the relationship. These relations will be very helpful later for the queries.
The important data types and field properties
As already mentioned, fields can have different data types. We will introduce the most important data types below.
Short text
This is, as the name suggests, a short text with a maximum length of 255 characters. This data type will be z.B. is used 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 e.B. a detailed product description or additional notes. In earlier versions this data type is called "Memo.
Numbers can be entered here in order to be able to perform calculations with them. The input options are limited to -2.147.483.648 to 2.147.483.64. An example of this would be quantities 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, here could be z.B. a sales date could be entered.
Currency
Numbers entered in a field with this data type are automatically displayed as currency. The default setting is Euro.
Yes/No
When this data type is selected, a box appears in the table, which can be checked. Then a check mark appears symbolizing "Yes".
Ole object for attachments
By selecting this data type, other Office documents can be linked to the record. In the table, the data type is represented as a staple. By double-clicking on the field in the table, attachments such as z.B. contracts or invoices, be added.
The data type allows to link a data set with a website / URL, e.g.B. the corresponding product page.
Attachment
Images can be linked directly to the data set.
Calculates
This data type makes it possible to perform calculations automatically. However, in the design view you must already specify what should be calculated. So if you select "Calculated" as field data type, a dialog box will appear automatically. Here you select at expression elements products and at expression categories the fields, with which should be calculated. In the expression elements, they also select the operators (add, multiply, etc).) from. The window should now display z.B. look like this:
Queries and macros
With queries you analyze data sets from different tables and display them clearly. Macros are used for automation to z.B. enter new customer data or send reports on a regular basis.
Query
Queries are a very useful tool to graphically display data from linked tables. To create a query go to the "Create" menu and click on the query wizard. For the beginning it is enough to use the selection query wizard. For example, to display which products have been purchased by which customer, select the corresponding fields from the Customers and Products tables. This way z.B. Your data selection will look like:
9 Data field selection
Now click on "Finish" and as a result a new table appears that has combined the data entries from the two original tables. Of course, there are also far more complex query options, but these will not be discussed in detail here, since this type of query is usually sufficient for commercial use.
Macros
Macros are designed to automate a certain series of actions, e.g.B. importing data from other sources. We will show you as a simple example the creation of a macro to create a new 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 dropdown menu, you can already see how many possible uses there are for macros. Now select the "OpenForm" item. At "Form name" you now select the corresponding form (in this example "Products").
Now add a new action "GoToRecord". This action is necessary to define what should happen after the form has been opened. For Object type select "Form" and for Object name select "Products" again. For record, you can select which record to display, i.e. first, last, etc. Now select "New. Then you save the macro.
If you now run the macro, a form will automatically open to enter new records into the product table.
The best templates for Access databases
Thanks to the work of Microsoft itself and many volunteers, there is a whole set of templates for Microsoft Access that you can use to get ready-to-use databases. In addition to the Microsoft templates already built into Access, on the following pages you will find other free templates for different purposes, queries& Macros you are free to use:
The Microsoft templates for Access can be found in the program itself by creating a new database via New / Sample Templates.
Equipped with these basics for Access we wish you good luck with your testing. This tutorial will teach you myKey – Windows& Buy Office Product Keys cheap – with us you save up to 75% on the new price. After purchase in the Online Shop you will receive your license key with software and installation instructions conveniently by mail for direct downloading.
In our legal basics for used software we explain how software is checked by us. This way you protect yourself from unintentionally using illegal software privately.