How to create a simple query in a Microsoft Access database from scratch? step by step guide

Getting specific data can be a task that takes hours manually. That’s where programs like Microsoft Access they help you create a simple query in a database to locate them more quickly. If you want to know more about it, keep reading this step by step guide.

Normal in a company is to work with specific information contained in large data tables. That is why it is convenient to have a method that allows you to visualize it instantly.

The queries show only what interests you, and gives you the option to combine data from other tables. This means that they are of great help in terms of organization and streamlining processes. Accordingly, we’ll teach you all about simple queries and how to take advantage of them.

What are simple queries and what are they for in Access?

If you need to collect data from one or more sources in your database, the best thing to do is to use a simple or select query. It is essential to know what are simple queries and what are they for in Access to familiarize yourself with its operation.

The simple query or selection query is a basic Microsoft Access process that allows you to view information stored in a databasethrough Data sheet view. In administrative tasks, normally, one does not work with all the registered information. On the contrary, the user requires are specific data.

A query gives the user the opportunity to select, precisely, what you need at the moment. It should be noted that the queries do not save data, they simply show those that are stored in the tables. The great advantage of the tool is to be able to show you data that comes from multiple tables or other queries. The same way, there are other advantages that are also noteworthy.

Among them, we can mention:

• When opening a data tablethe selection query allows you to see only those fields that are of interest to you.
• You can use the query tool to combine information from multiple sources.
• You can use expressions as fields in queries.
• allows you to visualize those records that meet a specific criteria.

Learn step by step how to create a simple query in Microsoft Access

Select queries make searching for information in a database more efficient. There are two main methods for designing a query: Through the query wizard, and through the Design View. For this reason, we teach you step by step how to create a simple query in Microsoft Access. Both the wizard and the design view have steps that differ in their respective procedures for creating a query. For example, you can’t access layout elements from the attendee.

However, both methods share basic steps:

• Open Microsoft Access Y choose the tables or queries that you are going to use as data sources.
• Next, specifies those fields coming from the data sources that should be included.
• To limit the records that should be returned by the query, you must specify the limitation criteria. However, this step is optional.

After you have created the select query, you must execute it in order to view the results. you should only open the document In the view “Information Sheet”. Keep in mind that you can always reuse the query, either as a data source for the elaboration of a form, another query or a report. Here’s an overview of creating a select query in a database through the wizard and the design view.

In addition, we leave you a brief explanation about the Access web application:

With the Query Wizard

The query wizard helps you automatically create a simple select query. The procedure is fast, plus it notifies you of any design errors. However, you don’t have as much control over the details that make up the query project.

As a recommendation, it is necessary create the relationships between the source fields for the query. Otherwise, the wizard will ask if you plan to create any relationships between the data sources. However, you will have to restart the wizard if you change any relationship.

To create the query, you must perform the following steps.

• Open Microsoft Access. Next, go to the tab “To create”. In the group “Inquiries”, you must click on “Query Wizard”.
• The new dialog box appears on the screen. “New query”. Then click on “Query Wizard” and then click “To accept”.
• You must add the fields you require. The field limit is 255, coming from up to 32 tables or queries. For each added field you must perform two additional steps:

• go to “Tables/Queries” and click on the table or query that covers the added field.
• In section “Fields available”, you must double click on the field to add to add it to the list of selected fields. In case you want to add all the fields to the query, you must click on the double right angle bracket (>>) button.
• Finally, after adding all the fields, click “Following”.

• In case you want to add numerical data, the wizard does the trick by asking you if you want the query to return summary data or details about the individual records. Carry out any of the following steps as appropriate.
• To display the individual records of the queryclick “Details”. Then press “Following”.
• To display the summary of numeric fieldsclick “Summary”. Then press “Summary Options”.
• You must specify the fields that will be summarized in the “Summary Options” dialog box. Likewise, you must also clarify how to summarize said data, since only the numerical fields are displayed. For each field, you must choose a function:

• Sum: It is used to return the sum of all the values ​​of the field.
• Avg: It is used to return the average of all the values ​​in the field.
• Minimum: It is used to return the minimum value of the field.
• Max: It is used to return the maximum value of the field.

• To include a calculation of the records belonging to a data sourceyou must activate the box “Count data source name records”
• then click “To accept” to complete the operations in the dialog box “Summary Options”.
• If you want to append a field from date and time to query, the wizard complies by asking you how you want to group the date values. To do this, you must select time period which you plan to use. Then press “Following”.
• Finally, you must assign a title to the query on the last page of the wizard. Later, you must specify if you want to modify it or open it. Then press “To finalize”. If you don’t want to add number or date fields to the query, you can skip from step three (3) to step nine (9).
• If you want to modify the querythe document opens in “Design view”. On the other hand, if you only want to open the query, you can display its content in Data sheet view.

In Design View

You can too create a simple query manuallythrough the Design view. said option gives you a greater number of options to edit query design. However, the process is considerably slower than using the wizard. In addition, it is sensitive to errors.

The query is done as follows:

Step One: Append Data Sources

To add the data sources, you need to use the dialog box “Show table”. Likewise, you can always append other data sources in the future if needed.

Let’s see the steps to follow:

• Open Microsoft Access. Immediately go to the group “Others”located in the tab “To create”. Then click on “Query Design”.
• The dialog box appears on the screen “Show table”. Use the tabs “Tables”, “Queries” or both to select the data sources you want to use. Make the selection with double click. Then press “To accept”.
• To finish, close the dialog box.

Second step: Merge data sources

When you add data sources that are related to each other, the Microsoft Access tool takes care of combining the data automatically. However, if the sources are not relational, they cannot be automatically merged.

Therefore, the combinations must be added by the user:

• Adding a join is relatively easy. Take a source field and drag it onto the field of another source it corresponds to. Access draws a line between the two fields to signal the creation of a join.
• You may need to change a combination. In that case, you should double click on the combination to change.
• The dialog box appears on the screen “Combination Properties”.
• The box presents three options. Review each one and select the one that best suits you. Then click on “To accept”.

Step Three: Append Output Fields

After finishing adding the combinations, it is necessary add output fields. These represent the fields that contain the data to include in the result of the simple query.

Let’s see:

• From a data source located in the top panel, drag any field you want to add down to the “Field” row. The checkbox is located on the layout grid, in the bottom pane of the window.
• If you prefer to append all fields quickly, double-click the title of the data table or query in the upper panel. This highlights all fields from that source. Then drag them all onto the layout grid. The Access platform automatically fills in the queue “Table”.

optionally you can specify the criteria to limit the records that the query should return, and also summarize data in case you use numerical values ​​that need to be totaled. If not necessary, you can go directly to see the results.

Fourth step: Visualization of results

You can view the results of the query by clicking on “Run”in the tab “Design”. tool Microsoft Access goes on to show the results of the query through the view “Data sheet”. If necessary, you can make changes to the query. You must click on “Start”after in “View” and finally in “Design View” to return to the editing screen. Make the relevant changes and press again “Run”.

In an Access web app

The procedure is quite similar to the previous ones. However, it has some extra steps so that the results returned by the query can be seen from the browser. Otherwise, the mechanism is similar. Notably microsoft does not recommend continuing to use Access web apps.

Therefore, it is convenient to review other alternatives:

• open the app website in Access.
• Click on the tab of “Start”. Then press “Advanced” and finally select “Query”.
• The dialog box appears on the screen “Show table”. Use the tabs “Tables”, “Queries” or both to append all the data sources you plan to use. Make the selection with a double click. Afterwards, press “Add”. When you’re done, press the button. “To close”.
• From any data source located in the top panel, drag all the required fields onto the row “Countryside” in the bottom panel, in the window layout grid.
• Add the criteria that are required to limit the fields.
• Right click on the tab “Query”. Then press “Save”. You need to enter a name to identify the query.
• You can right click on the tab “Query”and then select “Data sheet view” to display the results of the query.