Access Chapter 6 (Reading)
Specialized Queries
Website Source: Microsoft Access Queries Links to an external site.
The following discusses various types of specialized queries created to perform a specific action.
Parameter Query
A parameter query prompts you for a value when you run it. When you supply the value, the parameter query applies it as a field criterion and shows the user results. Which field it applies the criterion to is specified in the query design. If you do not supply a value when prompted, the parameter query interprets your input as an empty string. Brackets are used in the query design grid to indicate to the user what to enter when prompted. For example: On a field that contains months of the year, [Enter Month] would be a parameter query, that stops, has the user enter a month, then runs.
Cross Tab Queries
You use select queries and crosstab queries to retrieve and present data, and to supply forms and reports with data. When you run a select or a crosstab query, Access displays the results in Datasheet view (looks kind of like Excel spreadsheet). The user designates which fields below in a row, column, and what data is calculated using a variety of statistics like SUM, AVERAGE, MIN, MAX, Count.
Action Queries
There are four types of action queries: append queries, delete queries, update queries, and make-table queries. Except for make-table queries (which create new tables), action queries make changes to the data in tables they are based on. These changes cannot be easily undone, for example, by pressing CTRL+Z.
Delete/Append Query
When you want to either quickly delete a lot of data or delete a set of data on a regular basis in an Access desktop database, a delete or an update query might be useful because the queries make it possible to specify criteria to quickly find and delete the data. Using a query can also be a timesaver because you can reuse a saved query.
Update Query
You use update queries in Access databases to add, change, or delete the information in an existing record. You can think of update queries as a powerful form of the Find and Replace dialog box. You cannot use an update query to add new records to a database, or to delete records from a database.
To add new records to a database you use an append query, and to delete whole records from a database you use a delete query.
Make Table Query
You use a make table query when you need to copy the data in a table, archive data, or perhaps save query results as a table.
Find Duplicates Queries
Duplicate data often creeps in when multiple users add data to the Access database at the same time or if the database wasn’t designed to check for duplicates. Duplicate data can be either multiple tables containing the same data or two records containing just some fields (columns) with similar data. Use the New Query dialog, click Find Duplicates Query Wizard to setup the query.
Restoring From Backup File
If you make changes using an action query that you later decide you didn't want to make, usually you will have to restore the data from a backup copy. For this reason, you should always make sure you have a fresh backup of the underlying data before running an action query.