This chapter is from the book
This chapter is from the book
This chapter is from the book
The Query Wizard
Queries choose specifically defined data from another query or a table. For example, if you have a table of all the surnames in the United States, you can create a query to restrict the return to be only those surnames starting with S, or those that are Smith. Queries in Access also can modify and analyze data in other queries or tables.
Access has made enormous strides in ease of querying by building on the query-by-example method pioneered by other database systems. Older methods required users to write difficult queries using stilted syntax and special keywords. Today you still can (and in some cases must) use this method, but for most daily chores, querying Access is simple and easy. Even so, Microsoft has included a Query Wizard. I've included a table with some data for you to practice querying on. This is a much reduced and simplified table from some real medical data with the personal information (and most fields) stripped out. It's part of your sample data called tblMiscData. This example uses that table.
Here's how I made a query using the sample data table. Follow along by doing or just reading about it.
Click the Queries button in the database view to bring up the Queries area. Your sample database, Day 3 should have the query already finished as part of the sample database. To see how I created this query, follow the steps. Click the Create query by using wizard hyperlink. This launches the Query Wizard.
Figure 3.8 shows the first dialog box of the Query Wizard. If you are following along, you need to select a table or query to use for your query. This example uses the tblMiscData from the sample data. It also includes all three fields from the table into the query. You can see the result of this operation in Figure 3.9.
){kind=link}
){kind=link}
Figure 3.8 The first step of the Query Wizard.
The next dialog box asks whether you want a summary or a detail query. Summary queries return information such as the number of records or the totals of fields. For this example, use the detail option and then click Next.
Finished already! Here you get to name the query and then open or launch it or view it in design view. Note the query only returns exactly the same records and in the same format as the table.
At this point, you might wonder what is the point of a query. If you have the query open, click the design view button (far left); or, if it's not open, open the sample data database to the Queries section and click the object qryMiscData Query. To see this query in more useful action, highlight the sample data query qryDate and then click the Design button in the toolbar right above where the database objects reside. Your screen should resemble Figure 3.9, but at this point without the entry in the Criteria line.
I've added the criterion <#1/1/95# to the criteria area of the query the wizard made in the Date field and saved it using the name qryDate. Other than that, it's the same query as the wizard made.
Figure 3.9 Here is the modified wizard made query in design view.
This new query tells Access to pull or select only those records where the date field is less than January 1, 1995. If you have it open, try running this query by clicking the Query View button at the far left on the main toolbar. Scroll around to assure yourself that this simple change vastly changes the query from one the same as the table, to one bearing information.