The creation of a database is one of the most complex topics in the entire IT world; let’s see how Access templates can make our work easier in the context of organizing a wine shop.
Creating a semi-professional database to manage a wine shop, as announced, is quite a complex topic. Fortunately, Access provides starter templates that can be customized according to our needs, without having to worry about creating a database from scratch if we lack the skills.
In the image from the previous point, we see the screen that appears when we launch Access. We then click on the menu item indicated by the arrow, to activate the command for creating a new database. The dialog window shown in the figure will open, where we will select the Inventory Management template that we will customize later.
In the following screen, Access asks us in which directory we want to save our work. By default, the Documents folder will be shown, with the name of the template we used. We give a name to the database, choose the save path, and press OK to proceed to the next screen.
Here we already see listed the features of our database: information on products, suppliers, employees, inventory, orders, and categories. It’s easy to understand how the wizard saved us 80% of the work compared to creating the project from scratch! We could already press Finish to create the database, but let’s click Next to check if the tables need customization.
This screen shows us the list of tables available at creation time. The concept of a table might be complex for those just approaching databases. To make it easier to understand, we could compare the database to a classic filing folder, where each type of information is written on a different sheet. These sheets are the tables.
Theoretically, it would be possible to create everything with a single table, but dividing data by type allows for easier organization and faster execution of the database and its subsequent operations, whether they be insertions, modifications, or queries. As you can see from the screen, each type of data that composes the table is called a field.
After this brief explanation, let’s check that the default composition of the various tables has all the fields we need enabled and, if not, click the corresponding box to display the checkmark. In our case, we enabled fields related to email address and payment terms for both suppliers and employees.
One very important thing is to never disable the IDtableName field found in each table. This particular type of field is nothing more than a counter that increments with each new record insertion (i.e., the group of data corresponding to filling all the fields). The ID field will be fundamental for creating relationships between the various tables.
We are nearly at the end of our auto-setup; we just need to choose in the dialog box what the graphical appearance of the database will be. We can select each style one by one and see its preview in the pane next to the list. We choose our preferred one and click Next again.
The same applies to the next window: here we must choose the visual style for printing Reports. These are nothing but a kind of “summary” of what the database contains; they perform calculations and present a large amount of data in an organized manner. For example, we might want to view and print a report on expense and income ratios by wine type or by supplier.
In the penultimate wizard window, we are finally asked for the project name. Here, if we wish, we can indicate to Access an image to include in the reports to personalize them, for example, with our logo. We then click on the image checkbox to enable the checkmark, press the button below, and choose the file from our Hard Disk.
We have finished: the last window, before the database is created, simply asks if we want to open it immediately. Naturally, our answer will be yes. Once we click Finish, the inventory of our wine shop will be created, and depending on our PC’s power, it may take a few minutes. At the end, we will be prompted to enter our company’s data.
What you see is the main command window, through which we will perform most data insertion and modification operations. Through these commands, we will easily fill the database: each insert/edit button will open a Form where we will be required to fill the various fields that make up each record.
In addition to the command window, we also have the actual database window through which we manage all structural changes we need to make. We can add other tables, modify their fields, create new reports, and customize existing ones in options and layout. In this image, the Query section is highlighted.
The Query is nothing more than a complicated way to call a database query. Here we see the Query creation wizard window: in the table list, we selected Suppliers and in the fields, the city. This allows us to find all suppliers present in a certain province. We can create various types of queries to be called later from the related section in the database window. Happy working!
Be the first to comment