|
Access Queries Home Microsoft Excel Microsoft Access Custom Reports SQL Server Visual Basic |
While tables store data, and forms and reports allow for data input
and data output, queries are what make a relational database system like Access powerful. The most
basic type of query is a Select query, which selects records from one or more tables. Other types of
queries are Update, Append, Delete, and Crosstab queries. Select queries are the tools one uses to assemble data from one or more tables (one can think of a query as a question you ask of the database). A simple select query will select records from a table, either returning all records or selecting a subset of the records. The query can also be sorted. Queries become much more useful when putting tables that have been "normalized" back together again (see the discussion on table design). In the customer list example, it was recommended that customers' favorite products be removed from the customer list and put in a separate table. Using a query, these preferences can be re-associated with the customer. To illustrate, suppose tables were designed in the manner described in our disucssion on tables:
Using the Access Query Wizard, creating a query that reassembles these fields is quite easy. The two tables with data in them might look like this: Customers:  FavoriteProducts:  Notice that customer number two has two favorite products, and customers number 3 and 5 have no records in the favorites table. This is the desired behavior of the database, where the related table has zero or more records with data about a record in the primary table. Customers can have one, many, or zero favorite products. To build a recordset that includes preferences, select the Create tab, and choose the Query design button. While many of the Access "wizards" produce good results, rarely is the query wizard helpful for a simple query. The Show Table dialog box appears: 
Add both tables, then close the dialog box. The Query window will now have both tables in them. If you have used the Relationships window to identify how the tables are joined to each other, the Query Design window will indicate this with the line joining the tables. If your database does not show this, see the discussion of relationsips here.  The relationship is called a "one to many" relationship, indicating that there can be more than one related record in the FavoriteProduct table. In fact, the actual description of a one to many relationship is that the related table can have zero or more records related to the primary table(see the discussion on primary keys on the Tables page), creating relationships on the Relationships page). So to see a results set (called a recordset), drag the desired fields into the grid at the bottom of the Query Designer (or double click on each desired field in the top pane). For example, drag the CustomerID, LastName, FirstName, and City fields from the Customers table, and the FavoriteProduct and LastPurchase from the FavoriteProduct table: 
With the query complete, choose the Home tab on the ribbon, and click the View button. The data will be joined into a recordset:  This is a very basic query. Select queries that join tables together are useful for forms and reports. More advanced queries, such as the Crosstab query, make Access a very powerful database system. Microsoft Access queries are powerful and easy to create. Call (303) 403-0386 or E-mail the human Access wizards for help. Next: build a form to show the results. |
Copyright 2012 Front Range Data Management Services