Creating Queries

Queries can be used for a wide variety of purposes such as filtering data based on certain criteria, calculating values, joining records separated by different tables, deleting records from a table, updating certain records based upon specific criteria, creating new tables, and much, much more. Now that your database has a table to store data in, you probably want to create ways to view that data. One of the greatest tools that Access 2007 provides is the Query Builder, which helps you develop many types of queries. If you have prior database development experience, you've probably had to write SQL statements and already know how complex they can be (and how difficult they can be to get correct). The Access Query Builder provides a graphical interface to help generate the correct SQL statement for many query types, often helping to reduce the complexity of creating accurate SQL statements.

As mentioned earlier, the Ribbon's Create tab shows all of the various entry points for creating Access database objects. In the Other section of the Create Ribbon (all the way to the right—see Figure 1-7), are two options for creating queries: Query Wizard and Query Design.

Figure 1-7

The Query Wizard button launches the Query Wizard, which was included with previous versions of Access. It helps you generate four different types of queries—Select, Crosstab, Find Duplicates, and Find Unmatched queries—through several wizard screens. While these are useful, you will find that there are many other types of queries that cannot be created from the wizard and you will most likely want to use the Access Query designer to help you create those.

Click the Query Design button in the Ribbon to open the Access Query Designer. The Show Table dialog box displays to help add tables to be used in the query. In the Show Table dialog box, click the Assets table, the Add button, and the Close button. A field list for the Assets table appears in the designer for the new query. In the field list, double-click the * (star) field to add all of the fields in the table to the Query view. Notice that the Assets.* field is added to the list of fields in the grid at the bottom of the query designer. In the field list, double-click on the Created Date field to add it to the list of fields. Click the checkbox to uncheck the Show option for the field, so that the Created Date field is not shown twice. For the criteria for the Created Date field, enter >(Now()-7). Figure 1-8 shows what the query should look like at this point.

When this query is run, all Assets records created within the last seven days are returned in the result. Criteria values can be any hard-coded values, functions, or expressions that define some information about the records you want to select. Switch the Query to SQL View mode by clicking the SQL View mode button in the bottom-right of the Access window; you'll see the following SQL statement generated by the Access Query designer:

SELECT Assets.* FROM Assets

You can also create the specific query you need for your application by writing it directly in the SQL View mode.

Now click the Close button in the top-right corner of the new Query object. You will be prompted to save the query, so type in the name Assets Created This Week and click OK. The new query is added to the list of query objects in the Navigation pane.

Figure 1-8

Was this article helpful?

0 0

Post a comment