If we like to collect movies, books, records, games or any other product whose number can become extremely high or if we want to manage our business and we can’t find an application that meets our needs, we don’t need to keep looking any further. The best we can do is create our own data pass using one of the most powerful and easy to use programs on the market such as Microsoft Access.
Microsoft Access is one of the lesser- known Office applications, an application that has always fallen under the radar between Word, Excel, and PowerPoint. And it has not gone unnoticed because it is a complex application to use if we have not previously worked with databases, which it is, but because its function, that of creating databases, is not in high demand. Before we show how we can create a database, we must be clear about a series of basic concepts.
Access basics
As I commented at the beginning of this tutorial, if we do not have knowledge of Access or databases, delving into Access can be frustrating without a basis that allows us to know what each of the functions and elements that are part of it is for. from a database.
The concepts that we must know in Access are the following.
- records . A record in Access is the smallest unit of information that can be made up of various data and values.
- fields . A field in Access is the name of the data that we want to introduce in order to recognize it without accessing the content of the table. If we want to create a table with first and last names, we will have to create the First Name field and the Last Name field.
- Tables . The records are stored in tables. Tables are the first thing we must create to start storing information. Queries, forms, and reports extract data from tables.
- inquiries . How well we can deduce from the name, the queries are filters that we can apply to the tables to find what we are looking for.
- forms . A form is the representation of the tables with the graphic interface that we want, an interface that we can create from the wizard or manually.
- reports . A report is the clean representation of the results of the queries. It is the equivalent of forms to tables.
To create a database , we just need to create a table and a query to do searches. These elements are not necessary, but they will allow us to give it a more visual aspect and make the entry of records aesthetically and functionally easier, since the tables are represented as spreadsheets and when the number of fields is high, add new ones. records, the task can become tedious.
It is recommended, before opening the program and starting to create tables, queries, forms and so on, to sit in front of the page and design the structure of the database, that is, create a list with all the fields that we want the table to have. . It is always better to add others than to fall short, since, once we have created the form, if we are missing a field, we will have to do fancy things to add it and modify the order in which the data is entered, it is not a complicated process, but yes very laborious
We can create as many tables as we want, tables to store different data that we can later relate to make queries and that these, based on the data entered, show information from various tables. Unlike other applications, in Access we don’t have to continually save to keep the data we enter in the tables, since they are saved automatically.
How to create a database in Access
As soon as we run Access, the application invites us to create a database from scratch or use one of the different templates available. Unlike Word, Excel and PowerPoint templates, Access templates only allow us to get an idea of what we can do with Access, since, to modify a template, it is necessary to have experience with Access as you are not a simple process and that, in many occasions, never goes well. Also, if we want to create our own database, it is because we want to create based on our specific needs. Before using a generic database, we can use applications dedicated to managing collections. Since what we want is to create a database without relying on any template, from the Access main page, click on Blank database .
Create a table
By default, in each and every table we create in Access, the first field is named Id, a field that is automatically filled in starting with the number 1 for the first record, 2 for the second record, and so on. The purpose of this field is not to create two identical records in a table, since they would be considered duplicates.
However, this record does not allow us to know if we have duplicated a record, since that number is different for each one. If we eliminate that field, and enter the same data from a movie in a record, Access will inform us that this record is duplicated, but if we keep the first field, we will be able to create duplicate records since each one will have a number of different record, and, when making queries, we can get the same data repeated several times. The use of Id in tables is recommended, for example, when creating invoices, delivery notes or any other type of record where we must follow a correlative numbering.
To start creating fields, just to the right of the first one, Id, click on Click to add, select the type of field we want to use (text, date, number…) and write the name of the field.
We continue performing the same process until completing all the fields that we want to be part of the table. We can create as many fields as we want. We should not worry if the row is too long, since, when entering data, we will not have to use the mouse to scroll.
Once we have created all the fields of the table, it is time to add the information that we want to include. To do so, from the same window in which we find ourselves, we must place the mouse over the corresponding field and start writing. To switch between fields, we can press the Enter key or the tab key.
If we enter very long texts, these will not be fully displayed in the fields, being a problem when checking that all the data is correctly written. The solution to this problem is to create forms. A Form, as we mentioned at the beginning of this tutorial, is the beautiful representation of the table, giving a table a friendly user interface so that we can enter data in a simpler way, as if it were an application. But first, we must save the table that we have created and assign it a name that allows us to identify it more easily if we plan to create different tables.
To save the table, we place the mouse over the table where we have entered the data, click with the right mouse button and select Save. By default, the name Table1 is shown, a name that we can change to whatever we want. Next, we must save the database that we are creating, a database that will be made up of all the tables, queries, forms and reports that we create. To save the database, click on File , and then on Save As . In the right column, we select the first option Access Database (*.accdb).
Make a form
To create a form based on the table that we have created, from the table that we have created, click on the arrow shown in the All objects section and click on Forms .
On the ribbon, click Create and then Form Wizard. Next, a window will be displayed where, first of all, we must select the table that we have created from the Table/Queries drop-down box. Next, all the fields that we have created will be shown, as we want to add them all, so as not to go point by one, click on the >> symbol and Next .
In the next window, we can establish the design that we want the form to have among 4 options: columns, tabular, data sheet or justified. By clicking on each one of them, it will show us how the data will be distributed in the form. In this example, we are going to choose the Justified option and click Next .
Finally, we must enter the name with which we want to save the form and if we want to start entering information or modifying the design. It is recommended to select the first option to check the design. If we don’t like it, or the size of the fields is too small, we will edit the design.
As we can see in the following image, now we have more than enough space to clearly see all the data in the fields, so it would not be necessary to modify the size of these in the form.
If this is not the case, we can access the design view, click on the View button located in the upper left corner and select the Design option.
To change the size of the fields, we just have to select them with the mouse and change the size. We can also change the background color, add function buttons in the Header, Detail or Footer of the form to open queries that we have previously created, to close the form, open a form, generate a report, exit the application…
If we don’t like the result of the wizard, we can try the other available form designs or create the form from the table by adding the fields manually and with the layout we want. This process is much slower, but it will allow us to create the design that we like the most. To create the blank form, on the Create option ribbon, click Blank Form and select the table on which we want to create the form.
What else can we do with Access?
Once the table and the form based on the table have been created to enter the data in an easier way, we can create queries to filter the content. With a query, we can find all the movies released in a year, those by the same actor or director, those that are in DVD format, those that we have seen or have yet to see, those that are in a certain location, if we believe a database of our movie collection.
The result of the queries has the same design as the tables, since we are applying a filter. Obviously, this design is neither intuitive nor comfortable and much less attractive. The solution is to generate reports based on the queries. Reports are the pretty representation of queries, just like forms are the pretty representation of tables.
If we do not want to depend on Access once we have created the database as we want, we can create an executable file to share it with our friends, keeping in mind that if we want to make any changes, we will have to make them in the database and back to create the executable, so whenever possible, it is recommended to use the database from Access.
