Queries in Access – Targeted filtering of customer data

Access is a powerful tool for managing and analyzing data. Particularly useful are queries, which only display the records that meet certain criteria. If you want to handle your data effectively, it is essential to master the filtering options in queries. In this guide, I will show you step by step how to set up and apply queries in Access with various criteria.

Key insights

• Use queries to specifically display records.
• Filter specifications allow for a more accurate analysis.
• The use of wildcards expands search options.

Step-by-step guide

Importing customer data

First, you need a data source. In this example, we are importing an Excel file with customer data into Access.

Navigate to the external data and select "Excel." You must choose the file that contains the customer data and import it into the "Customers" table. Confirm the import process.

After a successful import, you will see a revised database with about 20 to 25 customer records. Check whether the data is displayed correctly and if any adjustments are needed.

Creating a new query

Now start the process of creating a new query. Go to query design and select the "Customers" table that you just imported.

You can adjust the view to zoom in better and select the necessary fields. For our use case, we need the first name, last name, and date of birth. Also consider whether the address is relevant or not – for our purpose, it is not.

Now you can run the query. You will see the data structure and can adjust the order.

Filter by criteria

Now it gets exciting: To demonstrate filtering, we will focus on the first name. Set a criterion that the first name starts with a capital "R."

Use the asterisk (*) as a wildcard so that Access can interpret the names correctly. You will be surprised at how easy it is to use these filter criteria!

Look at the results. You should see various records that meet your criteria.

Advanced filtering options

You can also combine multiple criteria. For example, you now want to add the last name as an additional criterion. Set the criterion for the last name so that it starts with "M."

When you refresh the view, you will see that the query now shows all relevant data that begins with these letters. This allows for a more precise analysis of the data.

Saving the query

Once you have designed the query to your liking, it's time to save it. Click on "Save" and give the query a name that describes the criteria you used – for example, "Customer query with AM."

This is useful if you want to come back later to quickly access the query again.

Displaying records with specific values

If you want to display all records that do not have a postal code entered, you can do this as well. Set the criterion to "is null."

Then go to "Review" and see which records are displayed. If you only want to show records with an entered postal code instead, you can change the filter to "not null."

It is important to note that filter conditions are static. If you have special requirements, such as combinations with locations, it will be necessary to establish additional links.

Summary – Mastering queries with criteria in Access

Using queries in Access is an effective method for managing the volume of data and filtering out specific information. You have learned how to import data, create queries, and set specific filters. By following these steps, you can significantly improve your data analysis and work more efficiently.