Want to retrieve or update certain records in your Microsoft Access database? Queries in Access can help you do that.
Want to retrieve or update certain records in your Microsoft Access database? Queries in Access can help you do that. Queries let you retrieve, update, and delete records in your tables using custom criteria.
You can define these custom criteria yourself. When the records in your tables match your criteria, the action specified in your query is run.
There are multiple query types that you can use in Microsoft Access. Here we show how you can create those queries in Access.
1. How to Run a Select Query in Microsoft Access
As the name suggests, a Select query in Access lets you select and retrieve certain records from your tables. You can specify a custom condition and Access will retrieve only the records matching this condition.
Before you create a query, you must have a table in your database. Once you’ve created and populated a table with some data, you can run a query as follows:
- Open your database in Access, click the Create tab at the top, and select Query Wizard.
- Choose Simple Query Wizard and click OK.
- Select your database table from the dropdown menu. Then, select the field that you’d like to use in your query and click the right-arrow icon. You need to do this for each field that you want to add to the query.
- If you want to add all the fields, click the double-right-arrow icon. Then, hit Next.
- Select the Detail option and hit Next at the bottom.
- Enter a name for your query, select the Modify the query design option, and click Finish.
- You can now specify custom criteria to filter records in your table. To do this, put your cursor in the Criteria field for your column, type your criteria, and press the save icon at the top-left corner.
- As an example, we’ll configure the query to only show users that are younger than 35 years. We’ll type <35 in the Criteria field for the Age column.
- Double-click on your query in the Navigation Pane and you’ll see the filtered records.
2. How to Run an Update Query in Microsoft Access
An Update query looks much like a Select query but this one edits your table records. This query modifies and updates the records in your tables using your custom criteria.
For example, if you want to change the country for all your users in Germany to the US, you can create a custom criterion that automatically finds the required records and updates them for you.
Here’s how you create an Update query in Access:
- In Access, click the Create tab and select Query Wizard.
- Follow the on-screen instructions to add tables and fields that you want to use in your query.
- When your query opens in Design View, click Update from the Query Type section at the top. This will convert your Select query to an Update query.
- Click the Criteria row for the column that you want to update. Then, enter custom criteria to filter your records. We’ll use =Germany as we want to find all users that have Germany as their country in the table.
- Type what you want to replace the original record with in the Update To field. We’ll enter US as we want to update all records from Germany to the US.
- Press Ctrl + Save to save the query.
- Double-click on your query in the Navigation Pane to run it.
- You’ll get a prompt that says the query will make changes to your tables. Click Yes to continue.
- Another prompt will display the number of rows that will be affected. Click Yes to continue.
- Open your table and you’ll find that the query has updated the records as specified.
3. How to Run a Delete Query in Microsoft Access
If you want to remove certain records from your tables, a Delete query can help you do that. This query deletes the rows from your tables that match the given criteria. You’re free to use any criteria of your choice.
Keep in mind that you can’t restore the records once they are deleted. Therefore, keep a backup of your database before running a Delete query. If you aren’t sure how to backup something, it’s worth reading the Microsoft support article on the backup and restore process.
Here’s how you create the query:
- Click the Create tab and select Query Wizard in Access.
- Follow the usual steps to make a query.
- Once the query is created and is open in Design View, select Delete from the Query Type section at the top.
- Enter the criteria to delete records in the Criteria field. As an example, we’ll delete the records where the Age column is less than 40. For that, we’ll type <40 in the Criteria field.
- Press Ctrl + Save to save the query.
- Run the query by double-clicking on it in the Navigation Pane.
- Click Yes in both the prompts that appear on your screen.
- The matching records will be removed from your table. You can verify this by opening the table.
4. How to Run a Make Table Query in Microsoft Access
A Make Table query creates a new table from the filtered data of your existing tables. If you have several tables and you want to retrieve certain records from those tables and create a new table, this is the query you can use.
You can use this query with a single table database as well.
Here’s how to make a Make Table query in Access:
- Click the Create tab, select Query Wizard, and follow the instructions to create a basic query.
- On the query Design View screen, click on Make Table in the Query Type section.
- A box will appear asking you to enter a name for your new table. Enter a descriptive name and click OK.
- Put your cursor in the Criteria row for the column that you want to filter. Then, type the criteria to filter your records. We’ll create a new table holding users from Germany and so we’ll enter =Germany in the Criteria row for the Country column.
- Hit Ctrl + S on your keyboard to save the query.
- Double-click on your query to execute it.
- Hit Yes in the prompts on your screen.
- A new table will appear in the Navigation Pane. Double-click on it to view your filtered records.
Find Records in Access Without Hassle
If you have thousands of records in your tables and you’re struggling to extract certain records, queries can help you easily find the records you need and even help perform actions on them.
If you’re looking to add more data to your tables, Forms in Access is an easy way to do that. A form helps input new data into your tables by letting you focus on one entry at a time. This helps remove the possibility of accidentally modifying other records in your tables.
There are many ways to add data to a Microsoft Access Table. Using Forms is the easiest way to build your database.
About The Author