MY CARTDelete, Crosstab, Find Duplicates, and Find Unmatched Queries in Microsoft Access 2013
For the complete tutorial, click here.
Delete Queries
You can use a delete query to delete records in a table based on specified criteria. Deleting unnecessary records will speed up the performance of queries, reduce redundancies, and make for more smoothly operating databases.
When you run a delete query, you are deleting records that match a specified criteria. You do not delete field information only, you actually erase the entire record from the table. In order to not delete all of the records in your table, you must carefully select which records to delete by using query criteria.
To create a delete query, start by creating a query in query design view. Then add the table that contains the records to delete into the query design view. Next, click the “Delete” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab in the Ribbon.
Next, add the fields to the QBE grid that you need to use for criteria purposes and place the criteria by which you wish to select the records to delete into the “Criteria:” row. You can then run the query. You will see a dialog box appear, telling you how many rows you are about to delete from the selected table. If this is correct, click “Yes” to continue. If this looks incorrect, you can click “No” to abandon the record deletion and double-check your criteria.
Crosstab Queries
You can create crosstab queries to answer questions about how field data within a single table relates to each other. Crosstab queries display one table field down the left side of the result table, and another table field across the top of the table. In the intersecting cells, you will see data about how the two fields are related via a third field. For example, if you had a table that showed the salesperson, the products sold, and the number of each sold, you could create a crosstab query that sums the total amounts sold of each product for each salesperson.
When you create a crosstab query, it is important to remember that the crosstab must be created from a single table or query. Many times, these types of queries are based on other queries that you may have created, as a single base table in your relational database rarely has the necessary data for meaningful crosstab analysis. Crosstab queries display their answers in a spreadsheet-like format and, as such, the result set of a crosstab query is not updateable.
Access gives you the “Crosstab Query Wizard” to assist you in creating crosstab queries. You can run this wizard by clicking the “Query Wizard” button in the “Queries” group (“Other” group in 2007) on the “Create” tab in the Ribbon. In the “New Query” dialog box that appears, select the “Crosstab Query Wizard” and then click “OK.”
In the “Crosstab Query Wizard,” the first screen prompts you to select the table or query that will serve as the basis of the crosstab query. You can select the desired table or query in this screen, and then click the “Next >” button to continue. The second screen will prompt you to select up to three fields that you want to display as the row headings at the left side of the crosstab query’s result set. Move the desired fields from the “Available Fields:” list to the “Selected Fields:” list using the “>” button. Then click “Next >” to continue. The next screen asks you to select which field you want for the column headings at the top of the crosstab result set. Select the desired field and click “Next >” to continue. On the next screen, you select what number you want shown in the intersecting cells of the column and row headings. In the “Fields:” list, select the desired field to calculate and then select which function to perform on that field from the “Functions:” list. If you wish to show a row summary, you can check the “Yes, include row sums” checkbox. Then click “Next >” to continue. In the last screen you can type a name for the crosstab into the field provided, and then click “Finish” to view the result set of the crosstab query.
The Find Duplicates Query
You can use the “Find Duplicates” query to find duplicate records within a table. To create a find duplicates query, click the “Query Wizard” button in the “Queries” group (“Other” group in 2007) on the “Create” tab in the Ribbon. In the “New Query” dialog box, select the “Find Duplicates Query Wizard” and then click “OK.”
The first screen of the “Find Duplicates Query Wizard” allows you to select the table within which you wish to find duplicate records. Make your selection and then click “Next >” to continue. In the second screen, you select by which fields you wish to examine the records to look for duplicates. You click on the fields within which you wish to look for duplicate information in the “Available fields:” list, and then click the “>” button to move them into the “Duplicate-value fields:” list. You cannot have more than 10 fields listed in the “Duplicate-value fields:” list. When you have added the necessary fields, click “Next >” to continue. In the third screen you can select any other additional fields you would like to display in addition to the ones within which you will be searching for duplicate values. You can click on the names of the fields you wish to view in the “Available fields:” list, and click the “>” button to move them into the “Additional query fields:” list. Then click “Next >” to continue to the last screen. In the last screen, you can type a name for the query into the text box available, and then click “Finish” to view the duplicates in a query result set.
Note that this is not the recommended process for removing duplicate records from a table. This query simply informs you of which records are duplicated in the selected table. While it is possible to delete the displayed records from the find duplicates query result set, you can also remove duplicates using the function of the primary key in a table.
If you want to remove duplicates from a table in Access, you can right-click on the table from which you wish to remove duplicates in the Navigation Pane, and then choose “Copy” from the pop-up menu that appears. Then right-click into the table list in the Navigation Pane, and choose “Paste” from the pop-up menu that appears. Doing that will cause the “Paste Table As” dialog box to appear. In this dialog box, type in a name of the copy of the table that you are about to make and select the “Structure Only” option in the “Paste Options” section of the dialog box. Then click “OK.”
Next, open the copy of the table structure that you made in table design view. If there are any primary key field assignments made, remove them by selecting the field or fields by which the primary key is assigned, and then clicking the “Primary Key” button in the “Tools” group on the “Design” tab of the “Table Tools” contextual tab in the Ribbon. Then select the combination of fields by which you wish to determine if there are duplicates in the original table, and click the “Primary Key” button again to assign the unique combination of values produced by the selected fields as the new “Primary Key.” Remember that the purpose of a primary key is to eliminate duplicate values within a table. So, once that is done, click the “Save” button in the Quick Access toolbar to save the structural changes you made.
At that point, create an “Append Query” to append the records from the original table that contains the duplicate records into the new table structure. When you run the append query, any records which contain a combined duplicate value in the primary key fields will not be appended into the copy of the table. You should see an error message that says that a certain number of rows were not appended due to table key violations. Those are the duplicate records, so you may append the records that were not removed into the copy that you created. Then the copy of the original table should contain duplicate-free records.
The Find Unmatched Query
In a relational database, you aren’t supposed to have records in a “child,” or related, table which have no reference to a related record in a “parent” table. For example, in a “Sales” table that contains a “CustomerID” field, any reference placed into the “CustomerID” field should correspond to a valid “CustomerID” in the “Customers” table.
However, this isn’t always the case. If properly constructed from the beginning, enforcing referential integrity on your Access tables will ensure that you do not have unmatched records. But if you are creating a new Access database from data that was not stored in a relational database that had referential integrity checks, then you may have unmatched field references within your tables. Using the “Find Unmatched” query finds such records in a “child” table field that do not contain any valid reference to a record in the parent table. Such records are said to be “orphaned.” You can then re-create the necessary reference in the records shown, maintaining data validity.
To create a find unmatched query, click the “Query Wizard” button in the “Queries” group (“Other” group in 2007) on the “Create” tab in the Ribbon. Select the “Find Unmatched Query Wizard” in the “New Query” dialog box, and click “OK” to launch the “Find Unmatched Query Wizard.”
In the first screen, select the name of the table or query that may contain unmatched, or “orphaned” records. Then click “Next >” to continue to screen two. In the second screen, select the name of the “parent” table that contains the records that should contain the matching values for the records in the first table selected. Then click “Next >” to continue. In the third screen, you must select the names of the two fields in each table by which the two tables should be joined. Then click the “<=>” button to link the two fields for the duration of the query to find unmatched records. Then click “Next >” to continue to the next screen. Here, you can select any additional fields you wish to display in the query results from the “Available fields:” list, and click the “>” button to move them into the “Selected fields:” list. Then click “Next >” to continue to the last screen where you can type a name for the query into the text box provided. Click “Finish” to then display the records from the first table that contain no matching records in the second table.
