 |
|
|
|||||||||
 |
|||||||||||
|
|||||||||||
 |
|||||||||||
|
|
 |
|
Overview of queries
1. What are queries?A database management system (DBMS) like Access offers many useful features, such as the ability to answer questions. For example using our sample CUST database we could ask the questions: 1.1. What is the balance of customer CE76 1.2. Which customer’s names begin with Ro? 1.3. Which customers are located in Stafford? 1.4. How much available credit do the customers currently have? 1.5. Which customers with £7,000 credit limit are represented by sales representative 07? The answers to these questions, and many more, are found in the database, and Access can find the answers for you. When you pose a question to Access, or any other DBMS, the question is called a query. A query is simply a question represented in a way that Access can understand. Thus to find the answer to a question, you first create a corresponding query using in the techniques that follow. Once you have created the query, you instruct Access to run the query, i.e. perform the steps necessary to obtain the answer. When finished, Access will display the answer in a grid in a similar format to a spreadsheet. Starting Access and Opening the CUST Database.Before creating queries, you must first start Access. To do so, follow the same steps you used previously in the notes “Microsoft Access - Creating a Database” (see page 3). Once you have done so, you need to open the database you will use for the queries. For the queries in these notes, this means you must open the CUST database (which holds the CUSTOMER and SLSREP tables you created previously). 2. Creating a new Query.2.1 You create a query by making entries in a special window called a Select Query window. Once the database is open, the first step in creating a query is to create a New Query window. To do so, click the Query button and then the New button in the Database window, as shown in the following steps. 2.2 When you open a database, the list of existing queries should display. At this stage, however, there are none in the CUST database. 2.3 Click the New button and the New Query dialog box should display. For certain complex queries, Query Wizards can help you construct the query. For most queries, however, you will choose the New Query button and then construct the query yourself using Design View. 2.4. The Add Table dialog box should appear. The list of tables in the CUST database displays in the dialog box. The CUSTOMER table is currently selected as, by default, Access selects the first table using alphabetic sequence. 2.5. Add the CUSTOMER table to the query by clicking the Add button and a field list for the CUSTOMER table should display in the upper portion of the window as displayed in figure 1. 2.6. Close the Add Table dialog box and maximise the Select Query window. Point to the line that separates the upper and lower portions of the window. The Select Query window is maximised and the upper portion of the window contains a field for the CUSTOMER table. The lower portion contains the QBE (query by example) grid, the place where you specify fields to be included, sort order, and the criteria that the records you are looking for must satisfy.
Figure 1. Select the field list box by clicking the frame and resize it by dragging down the bottom line of the frame. Drag down until all fields in the CUSTOMER table are visible. 2.7 Once you have created a new Select Query window, you are ready to create the actual query by making entries in the QBE grid that appears in the lower portion of the window. You enter the names of the fields you want included in the Field row in the grid. You can also enter criteria, such as the fact that the customer number must be CE76, in the Criteria row of the grid. When you do so, only the record or records that match the criterion (Customer Number … CE76) will be included in the answer. 3. Displaying Selected Fields in a Query.Only the fields that appear in the QBE grid will be included in the results of the query. Thus, to display only certain fields, place these fields in the grid and no others. If you inadvertently place the wrong field in the grid, select the Edit menu and then choose the Delete command to remove it. Alternatively, you could choose the Clear Grid command to clear the entire QBE grid and then start again. Perform the following steps to create a query to show the customer number, name, and sales rep number for all customers by including only those fields in the QBE grid. 3.1 Ensure that you have maximised the Select Query window containing a field list for the CUSTOMER table in the upper portion of the window and empty QBE grid in the lower portion. Point to the Customer Number field and double-click the left mouse button to include the Customer Number field in the query. (You can also drag the Customer Number field from the field list to the QBE; or select the field by clicking the down arrow to the right of the field: in the QBE). The customer number should now be included as the first field in the QBE grid. 3.2 Point to the Name field and double-click the left mouse button to also include it in the query. Finally, point to the Sales Rep. Number field and double-click the left mouse button to include it in the query. Three fields have been selected in the QBE grid. Point to the Run button 4. Running a Query.4.1 Once you have created a query, you need to run it by clicking the Run Button (!). Access will then perform the steps necessary to obtain and display the answer. The set of records that make up the answer are called a dynaset, i.e. dynamic set of records. Although it looks like a table stored on your disk, it really is not as it is constructed from data in the existing CUSTOMER table. If you were to change the data in the CUSTOMER table and then rerun this same query, the results would reflect the changes. This is the reason it is called dynamic as it changes along with changes in the actual tables. When the Run button is clicked, the query is executed and the results displayed. Depending on the success of your selection criteria, note that no results may be displayed. Notice also that an extra blank row, marking the end of the table, displays at the end of the results. This will always be the case. 4.2 Printing the Results of a Query.To print the results of a query, use the same techniques you learned earlier to print the data in a table: ¨ Click the Print Preview button. ¨ If the results are acceptable, click the Print button. ¨ After the report prints, click the Close Window button. If the results of a query require landscape orientation, switch to it before you click the Print button. 4.3. Returning to the Select Query window.You can examine the results of a query on your screen to see the answer to your question. You can scroll through the records, if necessary, just as you scroll the records of any other table. In any case, once you are finished working with the results, you can return to the Select Query window to ask another question. To do so, click the Design View button and the 4.4 Closing a Query.To remove the Select Query window from the desktop, close it. Access then asks if you want to save your query for future use. If you expect to need to create the same query at a future date, then save it now. We will describe how to save queries later in the course. Perform the following steps to close a query without saving it. ¨ Click the Close button for the Select Query window. ¨ A Microsoft Access dialog box should be displayed asking if you want to save the query. Answer No. 4.5 Including all fields in a Query.If you want to include all fields in a query, you could select each field individually. There is a simpler way, however. By selecting the asterisk (*) that appears in the field list, you are indicating that all fields are to be included. Simply point the asterisk (normally the top field) and double-click with the left mouse button. The table name CUSTOMER, followed by an asterisk is added to the QBE grid indicating that all fields are selected. Click the Run button and all fields in the CUSTOMER table are included in the report. Click the Design View button to return to the Select Query window. The Select Query window replaces the datasheet. 4.6 Clearing the QBE Grid.If you make mistakes as you are creating a query, you can fix them individually. Alternatively, you may simply want to clear out the entries in the QBE grid and start again. One way to clear out the entries is to close the Select Query window and start a new query just as you did earlier. A simpler approach, however, is to choose the Clear Grid command from the Edit menu. 5. Entering Query Criteria.When you use queries, you usually are looking for those records that satisfy some criterion. You might want the name of the customer who’s number is CE76, for example, or the numbers, names and addresses of those customers whose names start with Ro. To enter criteria, enter them on the Criteria row in the QBE grid underneath the field names to which the criteria applies. For example, to enter the fact that the customer number must be CE76, enter CE76 in the Criteria row underneath the Customer Number field. You must, of course, first add the Customer Number field to the QBE grid before you can enter a criterion. The next examples illustrate the types of criteria that are available. 5.1 Using text data in criteria.To use text data in criteria, simply type the text in the criteria row below the corresponding field name, for example CE76 in the Criteria row underneath the Customer Number field. 5.2 Using wildcards.Two special wildcards are available in Access. Wildcards are symbols that represent any character or combination of characters. The first of the two wildcards, the asterisk (*), represents any collection of characters. Thus Ro* represents the letters Ro, followed by any collection of characters. The other wildcard symbol is the question mark (?), which represents any individual character. Thus T?m represents the letter T, followed by any single character, followed by the letter m, such as Tim or Tom. The next example illustrates using a wildcard to find the number, name, and address of those customers whose names begin with Ro. For this example, because you don’t know how many characters will follow the Ro, the asterisk is appropriate. ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit window. ¨ Include the Customer Number, Name, and Address fields in the query and then click the Criteria entry for the Name field. ¨ Type RO* and click the Run button. Notice that your entry is automatically changed to Like “Ro”. 5.3 Using Criteria for a field not in the result.In some cases, you may have criteria for a particular field that should not appear in the results of the query. For example, you may wish to see the Customer Number, Name, and Address for all customers who live in Stafford. The criteria involve the Town/City field, which is not one of the fields to be included in the results. In order to enter a criterion for the Town/City field, it must be included in the QBE grid. Normally, this would also mean it would appear in the results. To prevent this from happening, remove the X from its Show check box, the box in the Show row of the grid as follows: ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Include the Customer Number, Name, Address, and Town/City fields in the query. Enter Stafford as the criterion for the Town/City field and then point to the Town/City field's Show check box. ¨ Click the left mouse button to remove the X in the Show check box. The X is removed from the Show check box for the Town/City field indicating it will not "show" in the result. ¨ Click the Run button and the results display but the Town/City field should not appear. The only customers included should be those who live in Stafford. 5.4 Using Numeric Data in Criteria.To enter a number in a criterion, type the number without any pound signs or commas. The next example displays all customers whose credit limit is £7,000. To do so, type 7000 as a criterion for the Credit Limit field. ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Include the Customer Number, Name, Balance, and Credit Limit fields in the query. Enter 7000 as the criterion for the Credit Limit field. Notice that there is no need to enter commas or pound signs in the criterion. ¨ The results display and only those customers who have a £7,000 credit limit are included. 5.5 Using Comparison Operators.Unless you specify otherwise, Access assumes that the criteria you enter involve equality (exact matches). In the last query, for example, you were requesting those customers whose credit limit is equal to 7000. If you want something other than an exact match, you must enter the appropriate comparison operator. The choices are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), and NOT (not equal to). Perform the following steps to use the > operator to find all customers whose balance is over £5,000. ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Include the Customer Number, Name, Balance, and Credit Limit fields in the query. Enter > 5000 as the criterion for the Balance field. ¨ Click the Run button and the results display. Only those customers who have a balance over £5,000 are included. 5.6. Using Compound Criteria.Often, you will have more than one criterion that the data for which you are searching must satisfy. This type of criterion is called a compound criterion. There are two types of compound criteria. In AND criteria, each individual criterion must be true in order for the compound criterion to be true. For example, an AND criterion would allow you to find those customers who have a £7,000 credit limit and who are represented by sales rep 07. OR criteria, on the other hand, are true, provided either individual criterion is true. An OR criterion would allow you to find those customers who have a £7,000 credit limit or who are represented by sales rep 07. In this case, any customer whose credit limit is £7,000 would be included in the answer whether or not the customer was represented by sales rep 07. Likewise, any customer represented by sales rep 07 would be included whether or not the customer had a £7000 credit limit. To combine criteria with AND, the criteria must be on the same line in the Criteria portion of the grid. Perform these steps to use an AND criterion to find those customers whose credit limit is £7,000 and who are represented by sales rep 07. ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Include the Customer Number, Name, Balance, Credit Limit, and Sales Rep Number fields in the query. Then, point to the Criteria entry for the Credit Limit field. ¨ Click the left mouse button and then enter 7000 as a criterion for the Credit Limit field. ¨ Point to the criteria entry for the Sales Rep Number field, click the left mouse button, and enter 07 as the criterion for the Sales Rep Number field. ¨ The fields shift to the left as indicated by the gap between the left scroll arrow and the scroll box. Criteria have been entered for the Credit Limit and Sales Rep Number fields. ¨ Click the Run button. The results display and only those customers who have a £7000 credit limit and whose sales rep number is 07are included. To combine criteria with OR, the criteria must be entered on separate lines in the Criteria portion of the grid. The following steps use an OR criterion to find those customers who have a £7,000 credit limit or who are represented by sales rep 07 (or both). ¨ The criteria are entered for the Credit Limit and Sales Rep Number fields on different lines. ¨ Select the Criteria entry for the Sales Rep Number field by pointing to it and clicking the left mouse button. Press the BACKSPACE key four times to delete the entry ("07"). ¨ Point to the second line of criteria (the line labelled "or:") for the Sales Rep Number field, click the left mouse button, and type 07. ¨ Click the Run button. The results display only those customers who have a £7,000 credit limit or whose sales rep number are 07 are included. 6. Sorting Data in a Query.In some queries, the order in which the records are displayed really does not matter. All you care about are the records that appear in the results. It doesn't matter which one is first or which one is last. In other queries, however, the order can be very important. You may want to see customers' balances and may want them arranged from the highest to the lowest. Perhaps you want to see customers' addresses and want them listed by county. Further, within all the customers in a given county, you may want them to be listed by town/city. To order the records in the answer to a query in a particular way, you sort the records. The field or fields on which the records are sorted is called the sort key. If you are sorting on more than one field (such as sorting by town/city within county), the more important field (county) is called the major key and the less important field (town/city) is called the minor key. To sort in Microsoft Access, specify the sort order in the Sort line of the QBE grid underneath the field that is the sort key. If you specify more than one sort key, the sort key on the left will be the major key and the one on the right will be the minor key. These keys OVERRIDE any indexes you may have created when you designed your tables.
Figure 2. Perform the following steps to sort the data in the CUSTOMER table by Town/City:- ¨ Ensure you have maximized the Select Query window containing a field list for the CUSTOMER table in the upper portion of the window and an empty QBE grid in the lower portion. ¨ Include the Town/City field in the Sort QBE grid, point to the Sort entry under the Town/City field, and click the left mouse button. Click the down arrow that appears. The Town/City field is included (Figure 2). A list of available sort orders displays. ¨ Choose Ascending order by pointing to Ascending and clicking the left mouse button. ¨ Run the query. The results should contain the town/city names from the CUSTOMER table. The names appear in alphabetical order. Duplicates are included. 6.1 Sorting on Multiple Keys.The next example lists the number, name, credit limit, and sales rep number for all customers. The data is to be sorted by sales rep number within descending credit limit. The phrase "sales rep number within descending credit limit" means the sales rep number is the minor key and the credit limit is the major key. It also means credit limits are to appear in descending (high-to-low) order. The following steps accomplish this by specifying the Credit Limit and Sales Rep Number fields as sort keys. ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Include the Customer Number, Name, Credit Limit, and Sales Rep Number fields in the query. Select Descending as the sort order for the Credit Limit field and Ascending as the sort order for the Sales Rep Number field. ¨ Run the query. The results should display the customers sorted by credit limit with the highest credit limit first. Within the collection of customers having the same credit limit, the customers are sorted by sales rep number It is important to remember that the major key must appear to the left of the minor key in the QBE grid. If you attempted to sort the customer data by town/city within county, for example, but placed the Town/City field to the left of the County field, the Town/City field would be considered to be the major key. This would lead to the data being sorted alphabetically by town/city, not by town/city within county. To sort in the correct order, add the county field to the QBE grid before adding the town/city field. This sorts the data by county and within all customers in any given county; the customers are sorted by town/city. 6.2. Omitting Duplicates.As you saw earlier, when you sort data, duplicates are normally included. If you don’t want duplicates included, use the Properties command and specify Yes for unique values only. Perform the following steps to produce a sorted list of the counties in the CUSTOMER table in which each county is only listed once: ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Include the County field, select Ascending as the sort order, point to the second field in the QBE grid (the empty field following County), and click the left mouse button to move the insertion point. Then, select the View menu. ¨ Choose the Properties command. The Query Properties dialog box should display as described in Figure 3. ¨ Point to the Unique Values text box, click the left mouse button, and then click the down arrow that displays to produce a list of available choices for Unique Values (Figure 3). ¨ Select Yes by clicking the left mouse button and then click the Close button for the Query Properties dialog box to close the dialog box. ¨ Run the query. The results should display the counties sorted alphabetically and each county is included only once.
Figure 3. 7. Joining Tables.7.1 Suppose you want to list the number and name of each customer along with the number and name of the customer's sales rep. The customer name is in the CUSTOMER table, whereas the sales rep name is in the SLSREP table. Thus, this query cannot be satisfied using a single table. You need to join the tables; that is, to find records in the two tables that have identical values in matching fields. In this example, you need to find records in the CUSTOMER and the SLSREP tables that have the same value in the Sales Rep Number fields. To join tables in Access, you must first bring field lists for both tables to the upper portion of the Select Query window. Access will draw a line between matching fields in the two tables, indicating that the tables are related. You can then select fields from either table. Access will join the tables automatically. The first step is to add an additional table (the SLSREP table) to the query, as illustrated in the following steps:- ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Point to the Show Table button ¨ Click the Show Table button. The Add Table dialog box displays. ¨ Choose the SLSREP table by pointing to it, clicking the left mouse button, and then clicking the Add button. Close the Add Table dialog box by clicking its Close button. Expand the size of the field list so all the fields in the SLSREP table display. A field list for the SLSREP table should display. It has been enlarged so all the SLSREP fields are visible. A line appears (see figure 4), joining the Sales Rep Number fields in the two field lists. This line indicates how the tables are related, that is, linked through the matching fields. If you did not name the fields the same, Access will not insert the line. It follows, therefore, that the same name can be used in more than one table in the same database. You can insert the line manually by pointing to one of the two matching fields and dragging the pointer to the other. The upper box now contains an Entity Relationship Diagram (ERD). These diagrams are fundamental in displaying the relationships between tables. ¨ Include the Customer Number, Name, and Sales Rep Number fields from the CUSTOMER table and the Last Name and First Name fields from the SLSREP table. Notice that you don't have to choose the Sales Rep Number from both tables in order for the join to work. ¨ Run the query and the results contain data from the CUSTOMER table, as well as data from the SLSREP table.
Figure 4. 7.2. Restricting Records in a Join.Sometimes, you will want to join tables, but you will not want to include all possible records. In such cases, you will relate the tables and include fields just as you did before. You will also include criteria. For example, to include the same fields as in the previous query, but only those customers whose credit limit is £7,000, you will make the same entries as before and then also enter the number 7000 as a criterion for the Credit Limit field. Perform the following steps to modify the query from the previous example to restrict the records that will be included in the join: ¨ Click the Design View button to return to the Select Query window. Add the Credit Limit field to the query and then click the right scroll arrow so it displays. Enter 7000 as the criterion for the Credit Limit field and then click the Show check box for the Credit Limit field to remove the X. The Credit Limit field displays in the QBE grid. A criterion is entered for the Credit Limit field and the Show check box is blank, indicating the field will not display in the results of the query. ¨ Run the query. The results display only those customers with a credit limit of £7,000. The Credit Limit field does not display. 8. Using Computed Fields In a Query.Suppose you want to find each customer's available credit. This poses a problem, because there is no field for available credit in the CUSTOMER table. You can compute it, however, because the available credit is equal to the credit limit minus the balance. Such a field is called a computed field. To include computed fields in queries, you enter a name for the computed field, a colon, and then the expression in one of the columns in the Field row. For available credit, for example, enter Available Credit:[Credit Limit]-[Balance]. Notice that no spaces are entered between the “Available Credit” and colon, and between square brackets and minus sign. You can type this directly into the Field row. You will not be able to see the entire entry, however, because there is not enough room for it. A better way is to select the column in the Field row in which you wish to place the computed field and then press SHIFT+F2. A dialog box (the Zoom dialog box) will display. You can then type the expression in the Zoom dialog box. You are not restricted to subtraction in computations. You can use addition (+), multiplication (*), or division (/). You can also include parentheses [( )] in your computations to indicate which computations should be done first. Perform the following steps to use a computed field to display the number, name, and available credit of all customers. ¨ Be sure you have maximized the Select Query window containing a field list for the CUSTOMER table (but not the SLSREP table) in the upper portion of the window and an empty QBE grid in the lower portion. You can do this by closing the query without saving it and then starting over. As an alternative, click any field in the list of fields in the SLSREP table (i.e. the table displayed in the top of the QBE) and then choose the Remove Table command from the Query menu. This will remove the SLSREP table from the Select Query window. ¨ Include the Customer Number and Name fields. Point to the Field entry in the third column in the QBE grid, click the left mouse button and, then press SHIFT+F2. Type Available Credit:[Credit Limit]-[Balance] in the Zoom dialog box that displays. The expression you entered should be displayed within the dialog box. ¨ Choose the OK button. The Zoom dialog box no longer displays. The final portion of the expression you entered displays in the third field within the QBE grid ¨ Run the query. The results display and Microsoft Access has calculated and displayed the available credit amounts. 9. Calculating Statistics.Microsoft Access supports the built-in functions: COUNT, SUM, AVG (average), MAX (largest value), MIN (smallest value), STDEV (standard deviation), VAR (variance), FIRST, and LAST. To use any of these in a query, you include it in the Totals row in the QBE grid. The Totals row does not routinely appear in the grid. To include it, select the View menu and then choose the Totals command or click the Totals button. The following example illustrates how you use these functions by calculating the total (sum) balance for all customers: ¨ Click the Design View button to return to the Select Query window and choose the ClearGrid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Point to the Totals button ¨ Click the Totals button and include the Balance field. The Total row is now included in the QBE grid (Figure 5}. The Balance field is included and the entry in the Total row is Group By.
Figure 5. ¨ Point to the Total row under the Balance field and click the left mouse button. An arrow displays. ¨ Click the arrow that displays and a list of available selections displays. ¨ Point to Sum and click the left mouse button. Sum is selected. ¨ Run the query and the result displays showing the average balance for all customers. 10. Using Criterion in Calculating Statistics.Sometimes, calculating statistics for all the records in the table is appropriate. In other cases, however, you will need to calculate the statistics for only those records that satisfy some criteria. To enter a criterion in a field, you first select Where as the entry in the Total row for the field and then enter the criterion in the Criteria row. Perform the following steps to use this technique to calculate the average balance for customers of sales rep 07. ¨ Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. ¨ Include totals in the query as you did in the previous example. Include the Balance field and select Avg as the calculation. Include the Sales Rep Number field in the second column on the QBE grid. Next, produce the list of available options for the Total entry just as you did when you selected Avg for the Balance field. Use the vertical scroll bar to move down through the options until the word Where displays. The drop-down list of available selections should be displayed. ¨ Select Where by pointing to it and clicking the left mouse button. Then, enter 07 as the criterion for the Sales Rep Number field. Where is selected as the entry in the Total row for the Sales Rep Number field and 07 is entered as the criterion. ¨ Run the query. The result displays giving the average balance for customers of sales rep 07. 11. Grouping.Another way statistics are often used is in combination with grouping. The statistics are then calculated for groups of records. You may, for example, need to calculate the average balance for the customers of each sales rep. You will want the average for the customers of sales rep 04, the average for customers of sales rep 07, and so on. This type of calculation involves grouping, which simply means creating groups of records that share some common characteristic. In grouping by sales rep number, the customers of sales rep 04 would form one group, the customers of sales rep 07 would form a second group, and the customers of sales rep 12 would form a third group. The calculations are then made for each group. To indicate grouping in Access, select Group By as the entry in the Total row for the field to be used for grouping. Perform the following steps to calculate the average balance for customers of each sales rep. Click the Design View button to return to the Select Query window and choose the Clear Grid command from the Edit menu. Access clears the QBE grid so you can enter your next query. Include the Sales Rep Number field in the first column of the QBE grid. Include the Balance field and select Avg as the calculation in the second column of the QBE grid. The Sales Rep Number and Balance fields are included. The Total entry for the Sales Rep Number field is currently Group By, which is correct, so it doesn't need to be changed. Run the query and the results display showing each sales rep's number along with the average balance of the customers of that sales rep. 12. Saving a Query.In some cases, you will construct a query that you want to use again. To avoid having to repeat all your entries, save the query. To do so, close the query as before. This time, however, indicate that you want to save your work. Then assign a name to the query. The following steps illustrate the process of saving the query and calling it Average Balance by Sales Rep: ¨ Select the File menu and the File menu displays. ¨ Choose the Save Query command and type AVERAGE BALANCE BY SALES REP. The Save As dialog box displays. The name of the query has been entered ¨ Choose the OK button and Access saves the query as part of the CUST database. ¨ Close the Select Query window by clicking the Close button. Once a query has been saved, you can use it at any time in the future by opening it and then running it. The query is run against the current database. Thus, if there have been changes to the data since the last time it was run, the results of the query may be different. 13. Graphing the answer to a Query.A graph of the results of a query can often be useful. To create a graph, use the same techniques you used in “Microsoft Access - Creating a Database ”. Sometimes, one type of graph will present the data much more clearly than another. A pie chart, for example, indicates percentages better than a bar chart. Fortunately, changing the type of graph, as well as customising the graph in a variety of ways, is a simple process in Access. Perform the following steps to produce a pie chart that relates sales rep numbers to the average balance of customers of the sales rep.:- ¨ Choose the Form button and choose the New button. The New Form dialog box appears and select Chart Wizard. Click the down arrow next to Choose the table or query where the object’s data is to come from: and the list of available tables and queries displays. ¨ Select the Average Balance by Sales Rep query and choose the 0K button. The Chart Wizard dialog box offers fields to be selected for use in your chart. Select the Sales Rep Number field, click the Add Field button, select the AvgOfBalance field, and click the Add Field button. The fields for the graph are selected (see Figure 6). ¨ Choose the Next button, and then select the pie chart as the type of chart from the Chart Wizard dialog box. ¨ Choose the Next button and then type AVERAGE BALANCE BY SALES REP. as the title. ¨ Choose the Finish button. And the graph should display. To remove a graph from the desktop, you need to close it. To do so, click its Close button. You will then be asked whether or not you want to save your work. Perform the following steps to close the graph and save it using the name Average Balance by Sales Rep (the same as the graph title): ¨ Click the Form: Form1 Close button and then choose the Yes button. ¨ Type Average Balance by Sales Rep as the Form Name in the Save As dialog box. ¨ Choose the OK button. The graph is saved in the CUST database.
Figure 6. 14. Summary.These notes introduced you to querying a database using Access. You learned how to create and run queries. You learned how to use various types of criteria. You also learned how to join tables using queries. Finally, you learned how to use calculated fields and statistics, as well as how to graph the answer to a query.
|
Select Query window displays once again.
|
|
|
[Home] [Database Creation] [Queries] [Maintenance] [Reports] [Normalisation] [Switchboards] [Macros] |