Queries Are Our Friends
MS Access database queries are what we use to put our data to work. We need them to round up our records, perform calculations, or achieve massive editing tasks. MS Access has two types of queries: “select” and “action” queries. Select queries display specified data, filter it, sort it, and allow us to perform calculations. Action queries, though, are the real “muscle” of database power. Using action queries, we can update many rows of data or delete many records. We can also append data to new tables or make a brand new table.
Let’s look at some hypothetical examples how we might use these action queries:
The Update Query – the World’s Fastest “Find and Replace”
You maintain a large database with customer listings. After a customer reaches a specified cash amount in purchases, you want your database to list that customer as “preferred.” Rather than checking your database each week to do “find and replace” changes, you can design and save an update query that will look each week and update your records in one go.
Once you push the “OK” button to execute an update query all specified records are instantly changed and you cannot undo the changes. Careful! Make a wrong criteria selection and you could with one keystroke adulterate your database. You should test your update query by doing a select query first to see what records will be changed. Also, by all means, make sure you have a current backup of your database at all times.
The Dangerous Delete Query
Your customer database is being overloaded with customers who have not bought anything within the past 24 months. Rather than looking through many records, you can design a delete query with a formula based on the date of the last purchase made by a customer. Customer records that meet the criteria will be deleted.
Like the update query, once you push the “OK” button, all specified records are instantly and irrevocably deleted. If you make a mistake in your criteria and delete the wrong records, you cannot recover the data. Warning: NEVER attempt a delete query without backing up your database first. ALWAYS test your delete query by doing a select query first. That way you can see the actual records that will be deleted. Delete queries are best used on special occasions, say, when you are doing a semi-annual data purge.
The Append Query Insurance
Rather than throwing out the customer information you want scrubbed out of your active database, consider making an append query. You would use a regular query to find the old customers and then change the query to export those records to an archived table in the same database that you can refer to in case a customer returns. After making sure your append query worked, you can then delete the archived records from your active table.
Make Clones with the Make-Table Query
Your marketing office wants a complete list of all your customers with only selected data. They intend to design their own proprietary database, but want to bypass entering the date. You can use a make-table query to specify those records and export the data to a similarly designed empty table in a new database. The export process is fast and accurate, but care must be taken in designing the target database. It is almost easier to make a special one-time select query, export the records to an MS Excel spreadsheet, and import the records from the spreadsheet. The main advantage of the make table query, though, is that once you make the query you can run periodically and update the cloned table without going through the select and import process.
Before Exercising the “Nuclear Option”…
Our action queries are very powerful tools. We can change, delete, and export huge amounts of data in an MS Access database quickly and (in the case of the update and delete queries) irrevocably. They are the “nuclear option” in the management of the entire database, and they should be used with the utmost care and attention. Learn more about Action queries from Microsoft Office Online.
To learn more about queries, check out the Bright Hub tutorial, Creating Simple Queries in Access 2007.