[ Pobierz całość w formacie PDF ]
.Suppose that you want a query that shows the total amount of money each cus-tomer owed before discount.Then you want to see the amount of money these cus-tomers saved based on their discount (a calculated field you create named TotalSaved).You further want the information to be grouped by customer and sorted byhighest amount owed.Finally you want the Total Saved field to display dollaramounts (format like this $111.11).Follow these steps to create this query:1.Start a new query and select the Customer, Pets, and Visits tables.2.Click the Totals button (the ") on the toolbar to turn it on.3.Double-click the Customer Name field in the Customer table.4.Double-click the Total Amount field in the Visits table.5.In the Total: cell of Customer Name, make sure the Group By is selected.6.In the Total: cell of Total Amount, select Sum.7.In the Total Amount column select a Sort: order of Descending.3596-X ch23.F 5/30/01 11:42 AM Page 755Chapter 23 &' Working with Advanced Select Queries7558.Click on an empty Field: cell in the QBE pane.9.Type Total Saved:Sum([Visits].[Total Amount]*[Customer].[Discount]) in thecell.10.In the Total: cell of Total Saved expression, select Expression.11.Making sure the cursor is still in the Total Saved field, click the Criteria: cell.12.If the Property sheet is not opened, right mouse click to bring up the rightclick menu and select Properties.13.On the General tab, select a Format of Currency (for the Total Saved field).Your query should be similar to Figure 23-13.Notice that the query uses two fieldsfrom different tables to create the Total Saved: calculated field.You had to specifyboth the table and the field name for each field the Sum function used [Visits].[Total Amount], and [Customer].[Discount].NoteYou had to use the Pets table, although you did not use any of its fields in the QBEpane for the query.It was necessary to use the Pets table to maintain and build alink between the Customer table and the Visits table.In other words, if you hadomitted the Pets table, there would be no way to link the Customer table to theVisits table.Figure 23-13: A query using an Expression Total:.Notice that the expression is builtbased on fields from two different tables  Customer and Visits.3596-X ch23.F 5/30/01 11:42 AM Page 756Part IV &' Advanced Access Database Topics756If you click the Datasheet button on the toolbar, your dynaset should be similar toFigure 23-14.The Total Saved field is a calculated field that you created using theexpression you built and specified as an Expression Total:.Notice that the resultingdisplay shows a currency format for the Total Saved field.Figure 23-14: A datasheet created by an Expression totalNoteIn the datasheet in Figure 23-14, the calculated field Total Saved shows the infor-mation in Currency format, using the Dollar sign and two decimal places.If you didnot specify a format for the field in the query design you would see as many as 12decimal places and no dollar sign.If all you want to do is limit the number ofdecimal places, while using the thousands comma you can specify a format ofStandard.Although specifying a Field format is relatively easy to do in the QBE pane, it hasone drawback  you do not visually see that a format has been assigned to thefield.In lieu of using the field format property you can also use the Format()function around the Sum()function making the Calculated field more complex, yetvisibly accurate.For example, to do so, add the following line to the existing criteriaformula in the calculated field cell:Total Saved: Format(Sum([Visits].[TotalAmount]*[Customer].[Discount]),  Standard ) or Format(Sum([Visits].[Total Amount]*[Customer].[Discount]), Currency )TipUsing the Format function in the calculated field cell takes precedence over theformat field property.If you specify a format function in the calculated field cell, itwill be used instead of the format property you set in the property list.At this point you should close the query without saving it because it will not beused again.3596-X ch23.F 5/30/01 11:42 AM Page 757Chapter 23 &' Working with Advanced Select Queries757Creating Crosstab QueriesAccess permits use of a specialized type of total query  the crosstab  to displaysummarized data in a compact and readable format.A crosstab query summarizesthe data in the fields from your tables and presents the resulting dynaset in a row-and-column format.Understanding the crosstab querySimply put, a crosstab query is a spreadsheet-like summary of the things specifiedby the row header and column header that is created from your tables.This querypresents summary data in a spreadsheet-like format created from fields that youspecify.In this specialized type of total query, the Total: row in the QBE pane isalways active.The Total: row cannot be toggled off in a crosstab query!In addition, the Total: row of the QBE pane is used for specifying a Group By totaloption for both the row and the column headings.Like other total queries, theGroup By option specifies the row headings for the query datasheet and comesfrom the actual contents of the field.However, unlike other total queries, thecrosstab query also obtains its column headings from the value in a field (table orcalculated) rather than from the field names themselves.NoteThe fields used as rows and columns must always have Group By in the Total: row.Otherwise, Access reports an error when you attempt to display or run the query.For example, you may want to create a query that displays the Type of Animal fieldas the row heading and the owner s state as the column heading, with each cell con-taining a total for each type of animal in each state.Table 23-3 demonstrates howyou want the query to look.In Table 23-3, the row headings are specified by Type of Animal: Cat, Deer, Dog, andso on.The column headings are specified by the state: ID, OR, and WA.The cell con-tent in the intersection of any row and column is a summary of records that meetsboth conditions.For example, the Cat row that intersects the OR column showsthat the clinic treats seven cats in the state of Oregon.The Dog row that intersectswith the WA column shows that the clinic treats five dogs in the state ofWashington.This table shows a simple crosstab query created from the fields Type of Animaland State, with the intersecting cell contents determined by a Count total on anyfield in the Pets table.3596-X ch23.F 5/30/01 11:42 AM Page 758Part IV &' Advanced Access Database Topics758Table 23-3A Typical Crosstab Query FormatType of Animal ID OR WACat 1 7 3Deer 1 0 0Dinosaur 0 1 0Dog 2 4 5Dolphin 0 2 0Creating the crosstab queryNow that you have a conceptual understanding of a crosstab query, it is time tocreate one.To create a crosstab query like the one described in Table 23-3, followthese steps:1.Start a new query and select the Customer and Pets tables.2.Double-click the Type of Animal field in the Pets table.3 [ Pobierz całość w formacie PDF ]