Microsoft Access is a program that allows anyone to create, modify and manage a database in a very simple way. This Microsoft product, thanks to its complete graphic interface, is suitable for anyone's needs, being usable both for small projects and for the activities of large companies. It is also an excellent tool for optimizing data entry operations, since it does not require you to work directly on tables and spreadsheets. Read on to find out how to get the most out of Microsoft Access.
Steps
Part 1 of 6: Creating a New Database
Step 1. Select the "File" tab and choose the "New" item
A database is an entity that holds all of your data in their different forms. You can choose to create an empty database, an empty web database or select a predefined template from those provided.
- The empty database is the standard database template provided by Access and is great for local use. Creating an empty database also includes a table within it.
- The web database is designed to be compatible with Access online publishing tools. Again, creating the database will automatically create an empty table.
- Access templates represent ready-made databases, intended for a wide range of uses. Choose a database template if you don't have the time to build your entire database structure.
Step 2. Name the database
After choosing which type of database to create, give it a descriptive name that can quickly describe its purpose. This is very useful if you have to work with multiple databases. Type the name in the "File name" field. To proceed with the creation of the database, press the "Create" button.
Part 2 of 6: Adding Data to a Database
Step 1. Determine which facility is best for storing your data
If you have chosen to start by creating an empty database, you will need to think about how you can best organize the data, and take steps to create the right structures. You can interact with data in an Access database in several ways.
- Tables: is the main method by which data is stored within a database. Tables can be compared to Excel worksheets, precisely because, in both cases, the data is organized in rows and columns. For this reason, importing data from an Excel sheet or from another equivalent program is a simple and straightforward process.
- Masks: is a tool used to insert data into the database. Although it is also possible to insert data directly from the tables, using the masks you will have a graphical interface that makes data entry operations much simpler and faster.
- Report: tool with which the data in the database are aggregated and displayed. Reports are used to analyze data and get answers to specific questions, such as how much revenue is generated or where customers are distributed. Reports are usually created and structured to be printed in paper form.
- Query: tool with which you extract and filter the data in the database. You can use queries to view specific data in multiple tables. Queries are also used to insert, modify, and delete data in tables.
Step 2. Create your first table
Starting with an empty database you will automatically have an empty table available. You can then start inserting data directly into the existing table, either manually or by copying and pasting the data from another source.
- Every single piece of information must be entered in a column (field), while every single record corresponds to a row, that is to say the set of all fields. For example, each record in the "Customers" table will correspond to a single customer, while a field or column represents every single piece of information relating to that specific customer (name, surname, e-mail address, telephone number, etc.).
- You can rename the columns of a table to make it easier to understand the information it contains. To do this, double-click the column header, then change its name.
Step 3. Import the data from another source
If you want to import information from an external file or other location, you can configure Access to retrieve the data and put it into the database. This method is very useful for retrieving data from a web server or other shared resource.
- Select the "External Data" tab.
- Choose the type of file you want to import. Within the "Import and Link" section there are several options relating to the types of data that can be imported. You can press the "More" button to view additional options. The "ODBC" driver, which stands for "Open Database Connectivity", includes the ability to connect to various database engines, including Microsoft SQL Server.
- Access the path where the data is located. Since this is a server, you will need to provide its address.
- In the next window, choose one of the available options to specify how and where the data should be stored within the current database. At the end, press the "OK" button, then follow the instructions on the screen to complete the data import.
Step 4. Add another table
The records concerning information of different nature will be stored in different tables; your database will thus have a lean and easily manageable structure, and consequently a higher response speed. For example, you might have a table that contains the information of your customers, and another that contains the orders placed by them. You will then be able to easily link the information contained in the two tables through the appropriate relationships.
From the "Create" section of the "Home" menu tab, press the "Table" button. A new table will be created within the database. At this point you will be able to populate it with related data, just as you populated the first table
Part 3 of 6: Setting up Table Relationships
Step 1. Understand how keys work
Each table must have a primary key that corresponds to unique data that distinguishes each record. By default, Access creates an "ID" field (column), whose value is automatically incremented for each record that is added to the table; this field represents the primary key. Tables can also have "Foreign keys". The keys are nothing more than fields of different tables related to each other. Obviously the fields affected by the relationship must contain the same data.
- For example, in the "Orders" table you must necessarily have a "Customer_ID" field, necessary to relate the products of a particular order with the customer who executed it. You can set up this link by creating a relationship between this field and the "ID" field (the default primary key) of the "Customers" table.
- Using relationships helps keep data consistent, efficient and always available.
Step 2. Select the "Database Tools" tab
Press the "Relations" button located in the "Relations" section. A new window will appear containing an overview of all the tables in the database. Each field will be listed under the name of the table it belongs to.
Before you can create a new relationship, you must have already created the fields affected by that relationship. For example, if you want to use the "Customer_ID" field of the "Orders" table for your new relationship, you must first create this field within the table in question. Make sure it has the same format as the field you want to relate it to - in this case it will need to be a number
Step 3. Drag the field you want to use as the first table's foreign key to the relevant field in the second table
From the new window that appeared, press the "Create" button to create the new relationship. Graphically you will see a line appear that connects the respective fields of the two tables in question.
When creating the new relationship, select the "Apply referential integrity" checkbox. This will ensure that the data is intact, by acting so that the changes can propagate automatically. Consequently, if the value in one field of the relationship changes, the change will automatically be reflected in the second field as well
Part 4 of 6: Creating the Queries
Step 1. Understand the role of queries
Queries are the tool that allows you to quickly view, add and modify data in your database. A wide variety of queries are available, from simple query or search queries to those used for creating new tables, starting from existing data. Queries are the essential tool for creating reports.
Queries are divided into two main groups: "Selection" and "Command". Select queries are used to extract data from tables and aggregate it as needed. Command queries are used to add, modify, and delete data from tables
Step 2. To create a simple select query, use the "Query Wizard" function
This will guide you through the steps required to create a query to query the database. You can choose the "Query Wizard" function from the "Create" tab of the menu: this will allow you to create the query quickly, starting from the fields present in the table of your interest.
Create a Selection Query with Criteria
Step 1. Use the "Query Design" tool
You can use criteria to narrow the range of values returned by the query and thus display only the information you need. To get started, select the "Query Design" item on the "Create" tab.
Step 2. Choose the table affected by the query
The "Show table" dialog box will be displayed, from which you can choose the one you are interested in by double clicking on it.
Step 3. Choose the fields to be extracted from the query
Double click on each of the fields in the table. This will automatically add them to the grid showing the query structure.
Step 4. Add the criteria
You can use different types of criteria within queries. For example, if you only want to view products that have a price above € 50, you will need to enter the following code
=50
as a criterion for the "Price" field. If you only want to view customers residing in the UK, you will need to enter the code
UK
within the "Criteria" row.
You can use more than one selection criteria within the same query
Step 5. To run the query and get the results, press the "Run" button
The "Run" button is located in the "Structure" tab. The results of the query will be displayed inside the window. To save the newly created query, press the hotkey combination "Ctrl + S".
Create a Selection Query with Parameters
Step 1. Use the "Query Design" tool
A parameterized query allows you to specify which data to extract from the database on each run. For example, if there are customers from different cities in the database, you can create a query that has the city of residence as a parameter, so that on each run you are asked to enter the city whose customers you want to get.
Step 2. Create a select query and specify which table or tables are affected
Add the fields to be returned by the query by selecting them with a double click of the mouse from the "Design view" window.
Step 3. Add the parameter within the "Criteria" section
Parameters are declared inside brackets "". The text inside the square brackets will be shown in the value entry request window that will appear when the query is run. For example, to request the insertion of the city of residence of your customers, select the "Criteria" line of the "City" field, then type the following code
[Which city?]
The text relating to the entry of the parameter can end with the character "?" or ":", but not with "!" or "."
Step 4. Create a query with multiple parameters
This way you can structure your query to make it more flexible and customizable. For example, if the field to be parameterized was of type "Date", you can manage the range of dates returned by the select query using the code
Between [Insert starting date:] And [Insert ending date:]
. In this case, two data entry windows will be displayed when running the query.
Create a Query for Creating a Table
Step 1. Go to the "Create" tab and choose the "Query Design" icon
You can generate a query that uses data extracted from one or more tables to create and populate a new table. This procedure is very useful if you want to share a portion of the database, or create specific masks that work on a subset of its data. First you will need to create a classic select query.
Step 2. Choose the table (or tables) you want to extract data from
To do this select each table with a double click of the mouse. You can use one or more tables.
Step 3. Choose the fields from which to extract data
To do this select them with a double click of the mouse. The chosen fields will be inserted into the grid of your query.
Step 4. Set the criteria
If you want to extract some specific data from a field, use the "Criteria" section as a filter. Please refer to the "Create a Select Query with Criteria" section of this guide for more details.
Step 5. Check your query to make sure it returns the correct dataset
To do this, execute the selection query before creating the resulting table; in this way you will be sure that the extracted data is correct. Continue by changing the criteria applied to the fields so that all returned data matches what you want.
Step 6. Save the query
To do this, use the hotkey combination "Ctrl + S". The saved query will appear in the panel on the left of the Access window. Click the icon of the query in question to select it again, then access the "Structure" menu tab.
Step 7. Press the "Create Table" button located in the "Query Type" group
A new window will appear asking you to enter the name of the new table. Enter it, then press the "OK" button.
Step 8. Press the "Run" button
A new table will be created with the data returned by the select query created. The table will appear in the relevant panel on the left of the Access window.
Create an Append Query
Step 1. Open the query created above
Append queries can be used to insert data extracted from one table into another that already exists. This is a very useful procedure if you want to add more data to a table generated with a build query.
Step 2. Press the "Queuing" button located in the "Structure" tab
This will bring up the "Queuing" dialog box. Choose the table in which you want to insert the new data.
Step 3. Modify the query criteria so that it returns the data you want to append to the existing table
For example, if you have created the previous table using the data relating to the year "2010", by entering the value 2010 in the "Criteria" section of the "Year" field, change this criterion to add the data relating to the year 2011.
Step 4. Choose where the new data should be entered
Make sure you configure the fields correctly for each of the columns you want to insert. For example, having made the changes indicated above, the data should be appended in the "Year" field, which will be entered in the "Append to" line.
Step 5. Run the query
To do this, press the "Run" button located in the "Structure" tab. The query will be executed and the data will be appended to the specified table. To check the correctness of the operation, you can open the table in question.
Part 5 of 6: Creating and Using Masks
Step 1. Select the table you want to create a mask for
The forms allow a quick and easy consultation of the data present in a table, to quickly pass from one record to another and to create new records. They are essential in managing long data entry sessions. Most users find it much easier to insert data into tables using forms.
Step 2. Press the "Mask" button located in the "Create" tab
This will automatically create a new form based on the fields contained in the chosen table. Access proves to be a valuable aid in the automatic creation of forms, managing to correctly size the input fields. However, you can always change the size and position of the input fields on the form according to your needs.
- If you want a specific field not to be displayed inside the mask, select it with the right mouse button and choose the "Delete" option from the context menu that appeared.
- If the tables in question have relationships, the fields containing the data concerned will be displayed under each record, within a specific table. You will then be able to change the data related to the displayed record much more quickly and simply. For example, each of the sales representatives in your database may have a customer portfolio database related to their records.
Step 3. Use the new mask
The arrow buttons at the bottom of the window are used to scroll through the records in the table referenced by the form. All the fields of the form will be gradually populated with the data present in the records. The buttons at the ends of the control bar will immediately take you to the first and last record in the table.
Step 4. Press the data card button in the upper left corner of the mask panel
You will now be able to edit the data in the fields using the form.
Step 5. Apply the changes to existing records
To do this you can change the values of any field of each of the records that make up the table. The changes you make, interacting with the form, will automatically be applied to the related table and to any other table in relation to it.
Step 6. Insert new records
Press the "Add New" button next to the navigation controls to create a new record that will be appended to the records already in the table. To enter data, use the fields on the form. Compared to using the table view pane directly, this procedure for inserting new records is much simpler.
Step 7. When done, save the mask
To be able to use it later, be sure to save the newly created mask using the hot key combination "Ctrl + S". The new mask icon will be displayed in the panel on the left of the Access window.
Part 6 of 6: Creating a Report
Step 1. Select a table or query
The reports allow you to view the data quickly and easily, aggregated according to your needs. They are often used to show the turnover generated in a given period or to have a list of shipments that need to be fulfilled from the warehouse. However, they are easily adaptable to virtually any purpose. Reports can be fed either directly from a table or from a query.
Step 2. Go to the "Create" tab
Select the type of report you want to create. There are several ways to go about creating a report. Access can automatically create a report, but you can create a totally customized one if you wish.
- Report: this function automatically creates a report containing all the records present within the data source. Although the data is not aggregated or grouped, this is probably the easiest and fastest way to display the information you need for a very small database.
- Blank Report: This option generates a blank report, which can be populated with any data that reflects your needs. In this case you can choose any available field in order to customize the report in total freedom.
- Report Wizard: This feature guides you through the process of creating a report, allowing you to choose and aggregate the data to view, and finally format it as you wish based on your needs.
Step 3. Set up a data source for a blank report
If you have chosen to create a blank report, start by selecting the data source to display in the report. To do this, choose the "Arrange" tab and the "Properties" box in sequence. Alternatively you can use the hot key combination "Alt + Enter".
Press the down arrow button next to the "Data Source" field. A list of all available tables and queries will be displayed. Select the desired data source to link to the report
Step 4. Add the fields to the report
Once you've set up a source for your data, you can start building your report by entering the fields you want to view. Go to the "Format" tab and choose "Add existing fields". The list of available fields will appear in the panel on the right of the window.
- To add fields to your report, simply drag and drop them from the list that appears in the report designer. The record will appear in the report. The additional fields inserted in the report will be aligned with those already present.
- To resize the fields in the report, select one of the sides and drag it with the mouse.
- To delete a field from the report, select it and press the "Delete" key on your keyboard.
Step 5. Add a grouping
Groupings allow you to quickly analyze the information in a report, as they allow you to organize related information. For example, if you wanted to group your company's sales by regions, or by the person who made them, Access groupings will allow you to do so.
- Go to the "Design" tab, then press the "Group and sort" button.
- With the right mouse button select each field you want to add to a group, then choose the "Group" option from the context menu that appeared.
- A header will be created for this group. To print the group name you can change the header as you wish.
Step 6. Save and share the report
Once you have optimized and completed your report, save and share it, or print it as if it were a normal document. Use it for example to share your company information with possible financiers or employees.
