How to install Access
I already mentioned a few lines ago that Access is part of the Microsoft Office productivity suite: the latter, however, is not available for free: it can only be used through the purchase of a commercial license or the subscription to a subscription to the Office platform. 365; for Access to be available in the package, you must purchase a license equal to or greater than Office 365 Home, with prices starting at 10 euro / month. Before purchasing, you can still get a free trial version of Office to see if Access is the right solution for you.
Don't know how to download Office for free? Don't worry, the procedure is quite simple: connected to the Microsoft Office 365 website, click on the button Try free for 1 month, type the email and password associated with your Microsoft account (if you don't have one, you can create it by following my specific tutorial), then press the button Log in.
At this point, fill in the forms shown on the next page with the required information. Just to be clear, I advance that you will be asked to specify a payment method- Don't worry, you won't be charged until your Office 365 trial ends (you can turn off auto-renewal before it expires via this webpage). Once you have completed filling out the forms, all you have to do is follow the on-screen instructions to download and install your trial copy of Microsoft Office.
Do you need further clarification on the steps outlined above? I urge you to take a look at my guide on how to download Office, in which I have explained everything to you in detail.
Once the setup is complete, you can start Access using the icon resident in the Start menu / Start Screen di Windows (called up by clicking on the flag-shaped icon located in the lower left corner of the screen) or from the folder Applications di MacOS.
How to create a database in Access
As I already mentioned at the beginning of the post, creating a database in Access is not complicated, however you need to know exactly the criteria by which to organize the data in your possession. Before we go ahead and take the actual action, I want to clarify some fundamental concepts regarding the elements that make up a database.
- Table - it is a set of horizontal lines and vertical columns. In the columns (or fields) are indicated property element, while each row identifies a specific data. If we want to take for example a table of students, the columns will be defined property of each student, one for each column: you will have the field Your name, the field Last name, the field Year of birth, the field Tax Code, the field Year of study and so on. Each row, on the other hand, defines a single student. Homogeneous elements (i.e. having the same properties) must be specified in each Access table.
- Entity - is the technical name that defines the elements that are part of a table.
- Cell - it is the unit (with its assigned value) in which a row and a column intersect.
- Record - is the set of properties of a specific entity belonging to the table: in other words, a record is nothing more than a row of the table that defines a specific element.
- Primary key - it is the field of a table that uniquely identifies each element. In other words, the primary key is a "unique property" of an object: if we want to take the student's example, we could use the tax code column as the primary key. As it is easy to understand, within a table there cannot be two equal primary keys.
- Report - it is the "relationship" that links two tables containing also distinct elements. For example, tables Students ed Exams, can be linked from left to right by the relationship supports (student takes exam) and the report supported by from right to left (exam taken by student). Relationships are used in databases consisting of more than one table, when the elements of each of them need to be "linked" in some way.
Now that you are familiar with basic database terminology, it's time to get down to business: if you haven't already, start the program Access, click the icon Blank desktop database, assign a name to the database by typing it in the text box provided and click on the button Crea.
Just like the other Office components, you can make use of ready-made database templates: you can search using the appropriate text area located at the top (eg. Product management), click on the icon that best suits your needs and then on the button Crea. For convenience, I will refer to an empty database from now on.
By default, Access creates a table containing a field ID, already set to be one primary key: If you wish, you can rename the field by right clicking on the corresponding entries and selecting Rename field from the proposed menu. If, for example, you want to create a table that contains a list of students, you can rename the field ID in Tax code.
You can define the data type that a field must contain, as well as its properties, by clicking on the field to be modified and then on the item Fields: You can use the drop-down menus Data type e Size (if applicable) to specify it. In this example, the social security number can be specified as short text: you can specify the maximum length in the box Field size. In this example, the social security number is exactly long 16 characters.
If necessary, you can check the box Obligatory, in order to prevent a field from being left blank. To add a new field, you can use the buttons Short Text, Number, Currency, Date and Time, Yes / No e Other fields present in the tab Fields, or click on the item Click to add placed inside the table. To delete a field, simply do click destroy on it and select the item Delete field give the purpose menu.
Add data
Now that you've finally created your table structure, it's time to fill it in with the data you need! Don't worry, the operation is really simple: click on the cell you want to fill and type in the data you want to assign to it.
You can move from one field to another in the record by pressing the key Tab (usually located above the Shift key on the keyboard) and start a new record by pressing the key Submit. If I had assigned the attribute Obligatory to a field (by intervening on the relevant box, as I explained above), you must necessarily assign a value to it before proceeding with the next record.
To delete a record, do click destroy on the gray rectangle located to the left of the first field that identifies it, select the item Delete record from the menu that opens and then presses the button Yes.
Once the table is complete, you can save it by pressing the button Save, the shaped one diskette located in the upper left corner of the Access screen, thus giving your table a name.
You can create new tables in your database by clicking on the section Crea, located in the top bar, and then on the button Table.
Create relationships between tables
The simplest method of creating relationships between two tables is certainly to “link” the respective ones primary keys: wanting to return to the previous example, it is possible to create a relationship between entities Student ed esame, of the type Student "supports" Exam, creating a connection between the keys that uniquely identify the records of each table, that is Tax Code e Exam code. In very simple terms, this makes the database understand that the students and exams entered in the tables are linked together in some way.
Apparently, this thing may seem useless, but I guarantee you it is not, since, in addition to assigning a certain order and consistency to the data, setting relationships between tables can be useful to generate practical report: for example, within the Student table, a hypothetical field Number of exams taken it can contain the automatic count of the relationships between the student record and the related elements of the exam table. In this way, when a student goes to take an exam, just update the report and the total count will be automatically generated upon request.
Understanding the usefulness of relationships, it's time to get down to business. To create one, click on the item Database tools placed at the top, then on the button Relations: from the small panel proposed, select the first table you want to involve in the relationship, press the button Add and repeat the operation with the following ones. Once you have added all the necessary tables, press the button Close.
At this point, click on the new panel Relations that appears on the Access screen, select the primary key which identifies the records of the first table (marked with a light bulb icon), drag it to the primary key of the second table and then presses the button Crea: the appearance of an arrow connecting one table to another confirms that the relationship has been created.
Once you have created the necessary relationships, press the button Close located at the top and then on the button Yes to return to the table view.
Run queries
One of the most valid tools of Access, and of relational databases in general, is the ability to run queries targeted, defined in jargon Query: these tools, in particular, are essential to carry out any type of search in the database (eg "show the names of the 9 CFU exams"), to modify entire records in bulk or, again, to generate new tables or reports from existing tables (eg "all users who took the programming exam 1").
Mainly, there are two types of queries: those of selection, which allow you to show the result of a search based on certain parameters, and those of command, which allow you to perform bulk changes on multiple records at a time based on a specific search criteria.
To generate a query, click on the section Crea placed at the top, then on the button Query structure: select the table you are interested in viewing the results from the proposed menu, press the button Add and finally, su Close. Then click on the panel Query1 that appears on the screen.
Initially, the screen may seem a bit tricky, but it is actually one of the most powerful features of Access: click on the cell Field further left, select a field in the table using the drop-down menu proposed and click on it to add it to the query, then repeat the operation with the following fields.
At this point, you can refine your search by acting on the various items of the small table shown below, modifying them in this way.
- Order - you can sort the results of your search according to a specific field by setting the Ascending or Descending items corresponding to the field itself.
- Show - by placing the check mark in the box, the field is shown in the results.
- criteria - here you can set the criteria with which to refine the results based on the chosen field (for example, display the students whose course year is greater than or equal to the second).
- Or - useful section for specifying logical union operations.
Once the query is correctly defined, press the button Run: a table will be generated on the fly containing the results of the query performed.
If you are familiar with the SQL language and are familiar with control operations (ALTER, JOIN, CROSS JOIN and so on), you can use them using the buttons Updating, elimination, Pass-through e Queuing on the screen Outline View analyzed just now. If you wish to manually issue SQL statements, press the button Data definition per I will proceed.
Generate reports
The generation of a report is certainly the fastest way to have immediate access to the data you are interested in analyzing within your database. Just to give you an example, returning to our Students / Exams database, you may want to generate a report showing all the students who took the Programming 1 exam.
As you can imagine, you can create a report starting from one table or from a specification Query defined with the procedure I explained to you a little while ago: in general, what you have to do is click on the table or query you are interested in generating the report, go to the section Create, press the button Wizard report and follow the on-screen instructions to define the fields to include, the criteria to follow and, if necessary, the grouping options. Once the report is generated, you can change its structure by doing click destroy on the relevant item placed in the box Report on the left panel, and selecting Structure view from the proposed context menu.
Unfortunately, I cannot be more specific about this operation, since there are so many cases to be analyzed and the length of this guide would not be enough to analyze them all. What I can guarantee you, however, is that it takes a little practice to become familiar with this powerful tool!
In fact, once you have the necessary experience, a few seconds will be enough for you to have access only and only to the part of the data you intend to analyze, thus optimizing the time at your disposal and increasing productivity: this is precisely the power of Access, do not you think?
How to use Access
