MY CART
Using the Simple Query Wizard
You use a query to answer a question that you have about the information stored in the database tables. You can then further analyze the results that the queries pull to produce even more information than the query itself displays. Reports are often based on query results, upon which they then can perform additional mathematical and statistical calculations. Queries are also an excellent way to show information from related tables in a single result set, as the results that you pull from queries aren’t limited to a single table. The power and flexibility of query design is the entire reason you use database programs. They can quickly access, calculate, and summarize the records that they pull from the various tables.
Access provides you with a simple query wizard which you can use to initially create queries. However, do not rely too heavily on this tool, as at some point you will inevitably have to create a query that is more complex than this tool allows. For advanced queries, you must learn how to create a query in query design view, versus using the wizard to create them.
However, to create a simple query using the wizard, 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 that appears, you can see the ways in which you can create queries. Select the “Simple Query Wizard” choice, and then click “OK” to begin.
In the first screen of the wizard, you must select the first table from which you will pull data by using the “Tables/Queries” drop-down. Once you have selected a table, the fields from that table will display in the “Available fields:” list. To add a field from the table into the query, select its name from the “Available fields:” list and click the “>” button to move it into the “Selected fields:” list. Repeat this as needed, selecting the fields that you will want to see in the query. When you are finished, click “Next >” to continue.
If you only selected fields from a single table in the first screen, then when you click the “Next >” button to continue, you will only need to provide the query with a name and then click the “Finish” button to finish creating the query. If, however, you picked data fields from two or more related tables, then when you click the “Next >” button, you will instead view a second screen which asks if you would like a “Detail” or “Summary” query. You can select the option button for the type of query that you wish to create. If you select “Summary,” then you will be able to click the “Summary Options…” button to open the “Summary Options” dialog box. In this dialog box, you can select what type of summary to perform over a selected field. Make your selections, and then click the “OK” button to return to the “Simple Query Wizard.”
Click “Next >” to continue. In the next screen, if you selected “Detail” on the previous screen, you will only need to name your query and then click “Finish” to create the query. If you selected “Summary” and your summary includes dates, you may be presented with additional date grouping options. Continue to answer any questions, as appropriate for your query and click “Next >” to continue until you reach the final screen where you must type a name for your query, and then click “Finish” to create the query.
Designing Queries
To make a query in design view, click the “Query Design” button in the “Queries” group (“Other” group in 2007) on the “Create” tab in the Ribbon to create a new query in the query design view. The first thing you will see is the “Show Table” dialog box appear over the query design view. Just as with the “Relationships” window which you used earlier, here you will have to add the table or tables that you need for the query into the query design view. You simply select the names of the tables that you wish to add, and then click the “Add” button in the “Show Table” dialog box to add the necessary tables into the query.
The query design view gives you power and flexibility in designing queries. Although it isn’t the only way to make them initially, you will have to learn how to use query design view at some point as you grow in your Access skill set. In query design view, the tables from which you extract data are placed into the top section of the design view. You then add the fields from these tables that you want to view in your query results into the bottom grid section, which is called the QBE, or “Query By Example,” grid.
If you want to add all of the fields of a query table into your query’s result set, you can click and drag the first field in the table, which shows an asterisk, down into the QBE grid. That will then show all of the fields in that table in the result set of the query. Once the fields are in place, you also add any “Criteria” and “Sorting” options, as needed, to the QBE grid to filter and sort just the data that you wish to see.
Ensure that you only select the tables that you absolutely need in order to run the query. Adding additional tables which you will not use forces the query to access these tables whenever it is run, slowing it down pointlessly. It can also produce unexpected and sometimes erroneous results. As you add the necessary tables to the query, the joins which you created between the tables will also be displayed at the top of the query.
Make sure that you have added all of the necessary tables for your query. For example, assume that you have two tables from which you wish to extract data: the “Customers” table and the “Employees” table. However, also assume that those two tables do not share a direct join between them. In order for the query results to make any sense whatsoever, you would also have to add the table that is used to associate those two tables, as well. Assume that the “Employees” table is related to the “Customers” table through the “Sales” table. In this case, you would also have to add the “Sales” table to the query, even if you had no intention of displaying any data from that particular table. It is needed in order to relate the two tables from which you do want to extract data. If you add two tables that are not joined to each other in any way, the query result will often produce a Cartesian product, where every value in every row of one table is multiplied by the value in every row of the second table. You will usually notice when this happens, as you will probably have several hundred, if not thousand, more records in your query result set than you do data records in either table.
Once you have added the necessary tables to the query, click the “Close” button in the “Show Table” dialog box to close it and display the query design view beneath it. You should see the tables that you added shown as small table diagrams at the top of the query design view. If you forgot a table and need to add it into the query, you can click the “Show Table” button in the “Query Setup” group on the “Design” tab of the “Query Tools” contextual tab within the Ribbon to bring up the “Show Table” dialog box again. If you accidentally added a table which you do not need, you may right-click on the table diagram of the table that you do not want in the query design view, and then select the “Remove Table” choice from the pop-up menu that appears to remove the table from the query.
Next, you will need to add the fields that you want to show in the query result set from the tables into the grid at the bottom of the query design view. To do this, you can click and drag the name of the field that you want to display from the tables, and drop them into the columns at the bottom of the design grid. You can also double-click on the name of a field shown in the tables to add it to the design grid, as well. There are actually quite a few ways that you can add the fields from the tables into the grid area below. Note that the order in which the fields are listed in the grid is the order in which those fields will be displayed in the query result set.
Before you can remove a field which you accidentally added to the grid, or reorganize the order of the fields in the grid, you must first select the column to delete or move in the result set. To do this, place your mouse pointer slightly above the column in the grid area that you want to select, until you see a downward-pointing black arrow. Then click once to select the field. To delete it at that point, you may simply press “Delete” on your keyboard. To move it, place your white mouse arrow into the very top of the selected column, and then click and drag the selected column to the left or right. As you drag it, you will see a thick, black line appear between the columns over which you drag your mouse. This line represents where the column will be inserted when you release your mouse.
Most often, after you have added the fields that you want to view into the query grid, you then add sorting and filtering criteria to the query. However, if you do not wish to restrict the data that is displayed, then you can simply run the query at this point. To run a query and view the result set, you can click the “Run” button in the “Results” group on the “Design” tab in the “Query Tools” contextual tab within the Ribbon to view the query’s result set. The result set looks like a table does when viewed in datasheet view. However, a query result set is not, by default, a “base” table in the same way that your other database tables are. The “table” that is produced when you run a query disappears as soon as you close the query. A query is really a definition of what data should be retrieved and displayed from the tables. Therefore, a query always shows the most “up-to-date” data every time that you run it.
You can switch the query back to the query design view after you have run the query by clicking the “View” button in the “View” group on the “Home” tab in the Ribbon. If you click the “View” drop-down arrow, then just select the “Design View” choice from the drop-down menu. Either way, once you are ready to save your query, click the “Save” button in the Quick Access toolbar. You can then type a name for your query into the dialog box which appears, and click “OK” to save the query. You can then close the query without losing all of your query design work.
