Bài giảng Business Driven Technology - Technology plug-in T7 - Problem Solving Using Access

TECHNOLOGY PLUG-IN T7Problem Solving Using AccessLEARNING OUTCOMESDescribe the process of using the Simple Query Wizard using AccessDescribe the process of using the Design view for creating a query using AccessDescribe the process of adding a calculated field to a query using AccessDescribe the process of using aggregate functions to calculate totals in queries.Describe how to format results displayed in calculated fieldsINTRODUCTIONA query is a tool for extracting, combining, and displaying data from one or more tables, according to criteria you specifyIn a query, you can sort information, summarize data (display totals, averages, counts, and so on), display the results of calculations on data, and choose exactly which fields are shownYou can view the results of a query in a tabular format, or you can view the query’s data through a form or on a reportCreating Simple QueriesUse the Simple Query Wizard to create a select queryA select query displays data from a table or tables, based on the fields that you select, but it does not sort or filter the dataCreating Simple QueriesTo create a query using the Simple Query Wizard:Open the file T7_SlopesideBikes_Data.mdb You will see a Security WarningClick on the Options button and select Enable Click OKClick the Create tab and then click the Query Wizard buttonDouble-click Create query by using wizardClick the Tables/Queries box drop-down arrowClick Table: CUSTOMERCreating Simple QueriesCreating Simple QueriesAdd all the fields by clicking on the right double arrow buttonClick NextMake sure that the radio button Detail is selected and click NextType Customer Query as the Query titleClick Finish to view the query in Datasheet viewClose the query (it will automatically be saved)Creating Simple QueriesReordering Columns In The Select Query DatasheetYou can reorder columns by clicking and draggingTo reorder columns:Open the Customer Query by double-clicking on it from the Navigation paneClick the First Name field selector and drag to the leftNotice the black column border line that appears to the left of the selected columnAs you move your mouse across the screen, the column border line will move with itClose and Save the queryReordering Columns In The Select Query DatasheetCreating A Select Query In Design ViewTo create a select query in Design view:Click the Create tab and then click the Query Design button in the Other groupClick the name of the BIKE tableClick the Add button Click the Close button to continueDouble-click the Bike ID fieldDouble-click the Description fieldRun the query by clicking the Run button Close and Save the query as Bike ListCreating A Select Query In Design ViewCreating A Select Query In Design ViewAdding Selection CriteriaTo A Select QueryTo specify criteria for a select query:In the Navigation Pane, click the Bike List query, right-click and click on Design ViewIn Design view, double-click the Cost Per Hour fieldClick in the Criteria cell under the Cost Per Hour field and type in <15 Run the queryClose and Save the query as Bikes Under $15Adding Selection CriteriaTo A Select QuerySorting Data In A Select QueryYou can control how records appear in Query datasheet view by using the sort featureTo modify the select query to sort records:Open the Bikes Under $15 queryIn Design view, click Sort under the Description fieldClick the drop-down arrow and select AscendingRun the queryClose and Save the query as Sorted Bikes Under $15Advanced QueriesTo enter formulas in calculated fields, follow these steps:Click the Create tab and then click the Query Design buttonClick the name of the BIKE, CUSTOMER, and RENTAL tables Click the Add buttonClick the Close button to continueDouble-click the Last Name and First Name fields from the CUSTOMER table, double-click the Date field from the RENTAL table, and double-click the Description field from the BIKE tableAdvanced QueriesTo add a new calculated field, click inside the field row of a blank column, and type Rental Amount:. This is the name of an anonymous field Now you will enter the expression to be calculated. Type in ([Time In]-[Time Out])*24*[Cost Per Hour] Run the query to see the results of the calculated fieldClose and Save the query as Rental AmountAdvanced QueriesUsing Aggregate Functions To Calculate Totals In QueriesAccess includes a group of powerful built-in commands known as aggregate (or total) functionsUsing aggregate functions, you can easily calculate totals for groups of records returned by the query specificationsAggregate functions can calculate the sum, minimum, maximum, average, count, variance, and standard deviationUsing Aggregate Functions To Calculate Totals In QueriesTo use aggregate functions in queries:Click the Create tab and then click the Query Design buttonClick the name of the BIKE table and click the Add button Click the Close button to continueDouble-click the Description and Cost Per Hour fieldsClick the Totals button (the Sigma icon on the menu bar) to add the total row to the query gridUsing Aggregate Functions To Calculate Totals In QueriesClick in the total row for the DescriptionClick the arrow and select Group ByClick in the total row for the Cost Per Hour cellClick the arrow and select the Avg function Click the Run buttonClose and Save the query as Bike Average Cost Per HourUsing Aggregate Functions To Calculate Totals In QueriesFormatting Results Displayed In A Calculated FieldTo use the format property for calculated fields:Open the Rental Amount query in Design viewRight-click in the Rental Amount calculated fieldClick Properties... from the shortcut menuClick in the Format boxClick the drop-down arrowScroll down and click the Currency formatClose the Field Properties dialog box by clicking the close box in the upper right cornerClick the Run buttonFormatting Results Displayed In A Calculated FieldCreating And Running Queries To Modify DataTo use action queries:Create a New query in Design viewSelect the BIKE table and click AddClose the Show Table Dialog boxDouble-click the Cost Per Hour field to add it to the query gridConvert the query to an action query by choosing the Update button on the tool barClick inside the Update To: row and type in [Cost Per Hour] +.50 (this will add 50 cents to each cost per hour)Run the queryCreating And Running Queries To Modify Data