[ Pobierz całość w formacie PDF ]
.&' Column headings or column orders cannot be specified.The workaround: Again, have the Wizard create the query and then modify it.To use the crosstab query wizard, click the New button in the database windowtoolbar after pressing the Queries Object button and then select the CrosstabWizard (third from the top, Figure 23-27) in the dialog box.Click OK and then followthe prompts.Access asks for&' The table or query name for the source&' The fields for the row headings&' The fields for the column headings&' The field for the body&' The titleAfter you specify these things, Access creates your crosstab query and then runs itfor you.Creating a Parameter QueryYou can automate the process of changing criteria for queries that you run on aregular basis by creating parameter queries.3596-X ch23.F 5/30/01 11:42 AM Page 768Part IV &' Advanced Access Database Topics768Understanding the parameter queryAs the name parameter suggests, a parameter query is one you create that promptsthe user for a quantity or a constant value every time the query is executed.Specifically, a parameter query prompts the user for criteria each time it is run,thereby eliminating the need to open the query in design mode to change the crite-ria manually.Parameter queries are also very useful with forms or reports because Access canprompt the user for the criteria when the form or report is opened.Creating a single-parameter queryYou may have queries that require different values for the criteria of a field eachtime someone runs them.Suppose that you have a query that displays all pets for aspecific customer.If you run the query often, you can design a parameter query toprompt the user for a customer number whenever the query runs.To create thequery, follow these steps:1.Starting with a select query, select the Customer and Pets tables.2.Double-click the Customer Number field in the Customer table.3.Double-click the Customer Name in the Customer table.4.Double-click the Pet Name field in the Pets table.5.Click the Criteria: cell for Customer Number.6.Type [Enter a Customer Number] in the cell.7.Deselect the Show: cell of Customer Number if you don t want this field toshow in the datasheet.It has been left visible in the upcoming example figure.That s all there is to creating a single-parameter query.Your query should resembleFigure 23-28.In the preceding example, you created a parameter query that prompts the user fora customer number by displaying the message Enter a Customer Number each timethe query is run.Access will convert the user s entry to an equals criteria for thefield Customer Number.If a valid number is entered, Access will find the correctrecords.Running a parameter queryTo run a parameter query, select either the Run button or the Datasheet button onthe toolbar.A parameter dialog box appears on-screen, such as the one shown inFigure 23-29, prompting the user for a value.3596-X ch23.F 5/30/01 11:43 AM Page 769Chapter 23 &' Working with Advanced Select Queries769Figure 23-28: A single-parameter query that will prompt the userfor the Customer ID to show every time the query is runFigure 23-29: The Enter Parameter Value dialog box askingfor a Customer ID numberAfter the user enters a value (for example IR001) and presses Enter, Access runs thequery based on the criteria entered.If the criteria are valid, the datasheet showsrecords that match the criteria; otherwise, the datasheet displays no records.If theuser simply presses the Enter key without entering a value, Access runs the queryand displays no records  in this case the value becomes null.If the user types IR001 in the parameter dialog box, Access displays a datasheetsimilar to Figure 23-30.Figure 23-30: Datasheet of records for Customernumber IR001 that the user entered into theparameter dialog boxThe records displayed in Figure 23-30 are only those for Petra Irish, whosecustomer number is IR001.3596-X ch23.F 5/30/01 11:43 AM Page 770Part IV &' Advanced Access Database Topics770Creating a multiple-parameter queryYou are not limited to creating a query with a single parameter.You can create aquery that asks for multiple criteria.For example, you may want a query that dis-plays all pet and visit information based on a type of animal and a range of visitdates.You can design this multiple-parameter query as simply as you designed thesingle-parameter query.To create this query, follow these steps:1.Select the Pets and Visits tables.2.Double-click the Pet Name field in the Pets table.3.Double-click the Type of Animal field in the Pets table.4.Double-click the Visit Date field in the Visits table.5.Click in the Criteria: cell for Type of Animal.6.Type [Enter an Animal Type] in the cell.7.Click in the Criteria: cell for Visit Date.8.Type Between [Start Date] And [End Date] in the cell.Steps 6 and 8 contain the prompt messages for the prompt criteria.When run, thisquery will display three parameter query prompts.Your query should resemblethat shown in Figure 23-31.Figure 23-31: A parameter query with three criteria specified withintwo columns  Type of Animal and Visit Date3596-X ch23.F 5/30/01 11:43 AM Page 771Chapter 23 &' Working with Advanced Select Queries771When this query runs, Access prompts the user for the three criteria in this order:&' Enter an Animal Type&' Start Date&' End DateLike the single-parameter example, the user must enter valid criteria.If the userenters valid criteria in all three dialog boxes, Access displays all records meetingthe specified criteria.Otherwise, it displays no records.Specifying parameter orderBy default, the Access prompt order of parameters is from left to right, based onthe position of the fields and their parameters.However, you can override theprompt order by selecting Query ª' Parameters and specifying an order.To specify a prompt order, enter the criteria on the QBE pane just as you have beendoing.For example, to specify a prompt order of Start Date, End Date, and AnimalType, follow these steps:1.Start with the query in Figure 23-31.2.Select Query ª' Parameters.3.Type [Enter an Animal Type] in the first cell under the Parameter column.4.Press Tab to move to the Data Type column.5.Enter Text or select the Text type from the drop-down list.6.Press Tab to move to the Parameter column.7.Type [Start Date] in the first cell under the Parameter column.8.Press Tab to move to the Data Type column.9.Type Date/Time or select the Date/Time type from the drop-down list box.10.Press Tab to move to the Parameter column.11.Type [End Date] in the first cell under the Parameter column.12.Press Tab to move to the Data Type column.13.Type Date/Time or select the Date/Time type from the drop-down list.14.Press Enter or click OK to leave the dialog box.Your Query Parameters dialog box should look like that shown in Figure 23-32.3596-X ch23 [ Pobierz caÅ‚ość w formacie PDF ]