Introduction to Microsoft Access course.

A database allows for the classification, sorting, and filtering of large amounts of information. Each accounting management software,
commercial management, inventory management, etc., is, by the way, a database. You can also do it with a spreadsheet.
like Excel. This is partly true. If you are an advanced spreadsheet user, some functions may seem similar.
However, the capabilities of Microsoft Access are far superior in these areas, especially if you use large
quantities of data.

To start our Access training, let’s begin with a small glossary specific to databases.

2. The ranking by sorting.

Let's take an address file, for example. Sorting will allow us to arrange it in alphabetical order by last name, first name, etc.
In most databases, these rankings can be done at different levels. For example.

Ranking by Name.

Then by First Name.

And finally by postal code, etc.

In our example, people will first be sorted by name. If two people have the same name, the sorting will then be done
according to the first name. For two people with the same name, the ranking will then be done according to the postal code of the place of residence, etc.

3. Filtering of records.

The record filter function allows you to display only the records that meet complex criteria.
In our address file case, this allows Access to display only men over fifty years old living in
a city. This type of filter is very effective. To make these filters, you must absolutely create suitable fields - with the
good data - according to the person. The main difficulty when you create an Access database: It's choosing
correctly the fields.

4. Vocabulary. The terms recordings, and fields.

Databases, especially Access, are characterized by records and fields. In the case of a notebook
the address, the registration represents the person's contact details. The field represents the box to fill in, a piece of information
included in a category for each record. The creation of a database - therefore involves beforehand - the creation
of a structure - depending on the desired information. This step is by far the most important. Due to their design,
adding fields - while the database and its various analysis modules are created - is tedious. Examples
are also provided directly with the database.

What fields should we create? Let's take our example of an Access address file. What are the categories of
information that we would be led to search for - knowing that the function of a database involves sorting and filtering.
Another criterion will come into play - additional information.

The Name criterion. This is primarily an information about the person - but it will allow us to conduct a search on a
nobody.

The first name criterion. This criterion is identical to that of the last name. Could we combine the last name and the first name? Yes and no. Let's take the
use case of this database for mailing. The person's address indeed includes their first and last name.
On the other hand, the standard phrase at the bottom of the letter says 'I beg you to accept, Mr. Lejeune, my best regards.'
Notice right away that the first name is no longer used. For friendly letters, the first name is used.

The address criterion: includes the street and house number, postal box. In the address, the beginner will break down this information.
in batches. Is it necessary? Probably not. It is unlikely that you will sort - or filter on the street - and less
Again on the house number. This field is indicative.

The postal code criterion. Here we could also combine the postal code and the city. However, separating the two will allow us
different little things. The city is specific - and will allow us to make a selection. However, at least in Belgium and in
France, the postal code will allow sorting not by residence - but by a housing area, typically by the municipality.
in Belgium.

The city criterion. This criterion will allow us to sort by residence.

The criteria phone number, fax, mobile, etc. Unless conducting a reverse directory search, this information is
that indicative.

The professional information criteria. It includes the name of the company, the person's role - and the contact details of
The company. The rules are the same as those for the private address.

Further information will be added according to the use of this Access database as information - like the
date of birth, the first name of the wife or husband, the number of children, etc.

5. Access Database?

Now that the preliminary explanations of the course have been acquired, let us focus on Microsoft's database. The
Access tabs are divided into 7 parts. Let's review the main ones.
Let's start with the table tab. Thanks to this tab, we create a table. The tables contain raw information - therefore unprocessed.
from the database - such as records and fields.

The query tab. A query allows various formatting of tables - such as sorting, filtering, displaying or not displaying data,
etc.

The form tab. A Form displays data from tables - and queries - in a pleasant manner.

The report tab. A report allows you to print data from tables and queries - with groupings and calculations if necessary.
This is indeed the big difference between Excel and Access, calculations are not done by simple formulas, but rather by these reports.
of printing. The advantage is that once the report is done, everything is done automatically. The downside is making these reports.
However, when it comes to making numerous records - specifically more than 500, Excel soon becomes overwhelmed.
and limited to its 65535 rows per sheet.

The macros tab. Macros allow you to execute functions from queries or forms. We will not go into detail.
in the ACCESS programming in this training - but we will use these functions to create an input form.

The Page tab: allows you to create web pages to access the database.

After starting Access under the TABLES tab, let's select the command "Create a table in design view". A window will appear
3 columns (one titled Field Name, one titled Data Type, and the last titled Description) will appear.

The Field Name column contains the name of the field. The Data Type column contains predefined data formats, such as
the Text format, the Memo format, the Numeric format, the Date/Time format, the Currency format, the Auto Number format, the
Yes or No format, O.L.E Object format, Hyperlink format, Assistant Choice List format...

Since the exercise for this Access course should allow us to create a CD-DVD management database in a
library, let's start directly by creating the table for this exercise.

2. Field Formats in Access.

Even though some types of fields go beyond the scope of this Access course, let's look at the main ones.

The Text format: allows you to create a field with mixed text and numbers. You cannot perform calculations.
in this type of fields. The size is limited to 255 characters.

The Memo format is equivalent to TEXT but can take more information. Display limitations,
notably in the states - makes this type of field little used. The maximum size is 65,535 characters.
 The Numeric format. This field format allows entering numbers in different formats.

The Date and Time format.

The Monetary format is identical to numeric but adds the monetary character. Generally, one uses
directly digital.

The Auto Number format allows Access to increment the number with each new record.

The Yes/no format is of boolean type (that is, of true or false type).

The format Hyperlink.

The O.L.E. Object format. This field format allows you to insert any object from an O.L.E compatible application, or
images, etc.

The choice list Assistant format allows for creating based on a typed list - or taken from another table - the
different accepted possibilities. This notion will be discussed in a separate chapter.

The other properties of the fields will be covered later in Advanced Table Properties.

3. Creation of a simple Access table.

To begin the exercise of this tutorial, we will create a simple table. In the "TABLE" tab, let's select "Create a
"table in creation mode". First, let's create a field named ranking. This field will allow us to rank our albums.
according to a number for example. We leave it in text mode. Let's complete with the following fields:

The text field for the "Album Title".

The date/time field for the 'album date'.

The text field for 'interpreter - 1'.

The date/time field for the "album date".

We could create title 2, interpreter 2, ...and so on, but we will see that better possibilities exist.

Let's exit by the cross on the right on the window - and Access asks if we want to save our table. Let's accept.
we will record and name this table 'Album'.

A new question appears asking for a primary key. This key is not mandatory - but it speeds up the processing of
information from a database under Access. Unfortunately, this key must be unique in the table. We could choose
ranking, but this is not possible if we use title 2, etc. At this stage, let’s not select a primary key.

Let's click on the album again to open it and we can now enter data into the various fields, some
records which in our case are songs.

Would it be possible to split our table in two? One part would be reserved for the content of the albums, and the other - reserved for the header.
(that is to say the album title, etc.). YES! This part will be seen later and is called a relational database.

This will modify our starting table since we will be able to use the ranking as the primary key, there will no longer be
risks of duplicates. Let's modify our table and remove the two fields 'title 1' and 'artist 1'. To do this, let's select the
Table album and click on the 'Edit' button. Let's select the row and press the 'Del' key.

Select the 'Ranking' field and with the right mouse button, let's bring up the context menu. Let's click on 'key
primary", while keeping it as a text data type. The ranking will then be unique. If our ranking were to be in numbers and
automatically, we could use the type "Auto Number" for this field in the second column. This solution poses
some issues when deleting records.

Let's now create - using the same method - a second table that we will call "content" - taking the fields
classement

When Access asks for the primary key, click YES and a field will be automatically added of type numbering.
automatic.

Here we are with 2 Access tables. The following chapter will allow us to create a relational database - by linking them.
between them via a field of the same type (here it is the ranking field that will serve this purpose).
4. The primary key.

We have just seen two ways to use the primary key in this course. This concept is secondary in many cases -
but perhaps particularly useful. When you create a new table, upon saving, Access asks
automatically if you want to create a primary key. What is this key for? It will help speed up certain processes.
the information.

The first option is to let Access create a specific field that will automatically increment according to the number.

The second solution is to use a field from the Access table as a primary key. In this case, the content of this field must be
unique (two records cannot use the same value for this field). This solution is used in this course for
our album table. In addition, Access automatically indexes in ascending order on the primary key.

In the first case, this field 'primary key' is not the record number - but rather a number that increments to
each new record. However, if you delete a record, the field is not updated in the
remaining records. To restart the primary key at 1, it is necessary to delete the field from the database and recreate it.
a field of identical names.

5. Moves to capture new records.

Click twice on the name of the created table, Access displays the content in table form - with the records in rows and
the fields in column. At the bottom of the window, there is a small toolbar indicating the Record Number
currently, the Number of records mentioned, the options Add a record, First record, and Last
recording.