How To Create Database In MICROSOFT ACCESS

The main purpose of this program is to create and work with databases that can be tied to both small projects and large businesses. With its help, it will be convenient for you to manage data, edit and store information.

Microsoft Office application – Access – is used to work with databases

 

Creating a new database

Naturally, before starting work, you will need to create or open an existing database.

Open the program and go to the main menu by clicking on the “File” command, and then select the “New” item. When creating a new database, you will be presented with a blank page, which will have one table in it, or a web database that allows you to use the built-in tools of the program for, for example, your publications on the Internet.

In addition, to simplify the creation of a new base as much as possible , the user is provided with a choice of templates that allow you to create a base focused on a specific task. This, by the way, can help you quickly create the required table form without having to manually configure everything.

Filling the database with information

Having created a database, it is necessary to fill it in with the appropriate information, the structure of which should be thought over in advance, because the functionality of the program allows you to design data in several forms:

  1. Now the most convenient and widespread type of information structuring is a table. In terms of their capabilities and appearance, tables in Access do not differ much from those in Excel, which, in turn, greatly simplifies the transfer of data from one program to another.
  2. The second way of entering information is forms, they are somewhat similar to tables, however, they provide a more visual display of data.
  3. For calculating and displaying information from your database, reports are provided that will allow you to analyze and calculate, for example, your income or the number of counterparties with whom you work. They are very flexible and allow you to make any calculations, depending on the entered data.
  4. Receiving and sorting new data in the program is carried out by means of requests. With their help, you can find specific data among several tables, as well as create or update data.

All of the above functions are located in the toolbar, in the “Create” tab. There you can choose which element you want to create, and then, in the opened “Designer”, customize it for yourself.

Database creation and information import

Once you’ve created a new database, the only thing you’ll see is an empty table. You can start filling it out manually or fill it in by copying the necessary information from the Internet. Please note that each piece of information you enter should be placed in a separate column, and each entry should have a personal line. By the way, the columns can be renamed to better navigate their content.

If all the information you need is in another program or source, the program allows you to customize the data import.

All import settings are located in a separate tab in the control panel called “External data”. Here, in the “Import and Links” area, the available formats are listed, including Excel, Access documents, text and XML files, Internet pages, Outlook folders, etc. After choosing the required format from which information will be transferred, you need specify the path to the location of the file. If it is hosted on a server, the program will ask you to enter the server address. As you import, you will be confronted with various settings that are designed for the correct transfer of data to Access. Follow the instructions of the program.

Primary keys and table relationships

When creating a table, the program automatically assigns a unique key to each record. By default, it has a column of names that expands as new data is added. It is this column that is the primary key. In addition to these basic keys, the database can also contain fields associated with information contained in another table.

For example, you have two tables containing related information. For example, they are called “Day” and “Plan”. By selecting the “Monday” field in the first table, you can link it to any field in the “Plan” table, and when you hover over one of these fields, you will see information and associated cells.

Relationships like these will make your database easier to read and most likely increase usability and efficiency.

To create a relationship, go to the Database Tools tab and in the Relationships area select the Data Schema button. In the window that appears, you will see all the databases being processed. Please note that databases must have special fields for foreign keys. In our example, if in the second table you want to display the day of the week or a number, leave the field blank and name it “Day”. Also adjust the format of the fields, as it should be the same for both tables.

Then, with two tables open, drag the field you want to link to the specially prepared foreign key field. The “Change Links” window will appear, in which you will see the separately highlighted fields. To ensure that the data in both related fields and tables is modified, check the box next to Ensure Data Integrity.

Creation and types of requests

A request is an action in a program through which a user can edit or enter information into a database. In fact, requests are divided into 2 types:

  1. Elective requests, thanks to which the program gets out certain information and makes calculations on it.
  2. Action requests that add information to the database or delete it.

Selecting the “Query Wizard” in the “Create” tab, the program will carry out the process of creating a certain type of query . Follow the instructions.

Queries can greatly help you organize your data and always refer to specific information.

For example, you can create a custom query based on certain parameters. If you want to see information on a specific date or day of the “Day” table for the entire period of time, you can set up a similar query. Select the “Query Builder” item, and in it the table you need. By default, the query will be selective, it becomes clear if you look at the toolbar with the Select button highlighted there. In order for the program to search for the exact date or day that you need, find the line “Selection condition” and enter the phrase [what day?] There. Remember, the request must be placed in square arches [] and end with a question mark or a colon.

This is just one use case for queries. In fact, they can also be used to create new tables, filter data by criteria, etc.

Setting up and using forms

Through the use of forms , the user can easily view information for each field and switch between existing records. With long inputs of information, the use of forms makes it easier to work with the data.

Open the “Create” tab and find the “Form” item by clicking on which a standard form based on your table data will appear. The appeared fields with information are subject to all sorts of changes, including height, width, etc. Please note that if there are relationships in the above table, you will see them and can be reconfigured in the same window. At the bottom of the program, you will see arrows that will allow you to sequentially open each column of your table, or immediately move to the first and last. Now each of them is a separate record, the fields of which you can customize by clicking on the “Add fields” button. The information changed and entered in this way will be displayed in the table and in all tables attached to it. After setting up the form, you need to save it by pressing the key combination “Ctrl + S”.

Generating a report

The main purpose of reports is to provide the user with a general summary of the table. You can create absolutely any report , depending on the data.

The program allows you to choose the type of report, providing several to choose from:

  1. Report – an auto report will be created using all the information provided in the table, however, the data will not be grouped.
  2. An empty report is an unfilled form, the data for which you can choose yourself from the required fields.
  3. Report Wizard – guides you through the process of creating a report and guides you through the grouping and formatting of data.

In a blank report, you can add, delete or edit fields, filling them with the necessary information, create special groups that will help to separate certain data from the rest, and much more.

Above are all the basics that will help you cope and customize the Access program for yourself, however, its functionality is wide enough and provides for more fine-tuning of the functions discussed here.

Leave a Comment