Using Access database queries really control the reporting aspects of your data; asking those questions. It is the heart of any well designed database system. There are many types of queries and one in particular can serve you well especially if you are wanting to create a league table, prioritise records or suppress the ‘lesser’ recordset. If you want to create for example a top 10 league table using a Top Value query is the way forward. Using a simple query and sorting by a key field will also display the answer but its not always necessary to view all the records (with or without criteria) and therefore you may want to suppress the eleventh record onwards. With a Top Value query, you have the option to specify either a value or a percentage which represents a fraction of the total number of records found in the recordset and is very simple to do in Microsoft Access when building the query.
You start by building your standard Select Query as normal using the Access query grid (QBE) and set your tables, fields and any criteria. Make sure you also sort by the key field which will organise your top 10 results. For example, if you wanted to know the top 10 performers of sales representatives for a given month, you will need to have sales revenue field (data) sorted in descending order to award top spot to the hightest revenue first. The criteria would include a date range for a month you are querying and any other fields that support the final output for your report. By setting the value ‘10‘ in the Return drop-down control (see illustration above) will add an additional SQL (Top) keyword to your existing query: SELECT TOP 10 Employees......It’s that simple! If you want to know more about this query and other special and detailed queries within this powerful application, check out my two great value eBooks: How to Build Access Database Queries More Access Database Queries Contact me if you would like to know more about me or my services. Ben Beitler – “Your Access Database Expert!” Tags: Access Database, access database query, creating an access query, top 10 query example, top query, top value query
Are you looking for a snapshot of the 10 best performers in your sales department for the last quarter? Then check out how easy it is to create top 10 lists in Microsoft Access with the Top Values combo box. Have you ever wanted to compile a ranked list from data in
your Microsoft Access database? For example, let’s say you just created a query that
lists each employee’s total sales for the quarter. But what you really need to
know is the top 10 performers. Depending on the number of employees, you could more than Follow these steps: The query results will include only those employees who have To compile a list of employees who had the five lowest sales Selecting All from the Top Values
combo box will return all records in the query results, ranked in ascending or
descending order. Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday. Published: August 16, 2005, 10:30 AM PDT Modified: June 8, 2007, 9:59 AM PDT See more CXO Click the Criteria row in the Smoker column and type False. Now you need to sort the field that you want to display the top or bottom values for. The Sort row works a little differently when you're using top or bottom values:
You want the query to display the top values in the Cost field, so... In the Cost column, click the Sort box list arrow and select Descending. Next you have to use the Top Values list to specify the number of top values you want to be displayed in your query results. Click the Top Values list arrow on the toolbar and select 5, as shown in figure. This will display the five most expensive tickets. You're ready to run the query. Click the Run button on the toolbar. Access displays the results of the query, similar to figure. Click the View button on the toolbar to display the query in Design view. You can also use the Top Values feature to display the top or bottom values from a calculation. Let's modify the query so that it calculates the total sales of nonsmoking tour sales. If the Total row is not already displayed, click the Totals button on the toolbar. The Total row appears. You need to specify which field you want to use to group the query, which field(s) you want to be calculated, and which fields are used to limit the number of records displayed in the query. You want to group records using the TourName field, and since its Total row already displays Group By, you can leave it as it is. You will need to tweak the Total row for both the Cost and Smoker fields, however. Click the Total row in the Cost column, click the list arrow, and select Sum from the list. This will total the Cost field. The Smoker field is used as criteria to limit the records displayed to only nonsmokersit shouldn't be included in the query results. Move on to the next step to tell Access this. Click the Total row in the Smokers column, click the list arrow, and select Where from the list. One more change before we run the querythis time instead of displaying the top values for the Cost field we want to display its bottom values. In the Cost column, click the Sort box list arrow and select Ascending. This will summarize the five tours with the lowest sales (for nonsmokers). Let's see the results... Click the Run button on the toolbar. Access displays the results of the query, as shown in figure. Save the query as qryBottomTours and then close the query. Page 2Click the qryTourSales query and then click the Design button. As always, you need to add the field that you want to group data by onto the design grid. This time we want to create a query that summarizes total employee sales. Here are the fields that we will use in the query: In the tblEmployees field list, double-click the FirstName, LastName, and State fields, and in the tblCustomerTours field list, double-click the Cost and Date fields. Next you need to tell the query that you want to group and summarize the query. If necessary, click the Totals button on the toolbar. The Total row appears. You need to specify which field(s) you want to use to group the query, which field(s) you want to be calculated, and which field(s) you want to use to limit the number of records displayed in the query. You want to group records using the FirstName, LastName, and State fields and find the total of the Cost field. Click the Cost column's Total row, click the list arrow, and select Sum from the list. This will total the Cost field. We will use the Date field as criteria to limit the records to those that fall between two dates. Instead of entering a criteria expression with two fixed date values, such as "Between 1/1/00 and 3/31/00," we will create two parameters that will prompt the user to enter the two date values each time they run the query. Click the Date column's Criteria row and type Between [Enter start date] and [Enter end date]. You've just created two parametersthe [Enter start date] parameter and the [Enter end date] parameter. Since the Date field is only being used as a criteria field, you need to select the "Where" option from its Total row. Click the Date column's Total row, click the list arrow, and select Where from the list. The "Show" check box automatically unchecks itself, indicating that the Date field will not appear in the query results. Let's add one more parameterone that prompts the user to enter the state where the tour was sold. Click the State column's Criteria row and type [Enter the state]. This will prompt the user to enter the name of the state. Since the State field is only being used as a criteria field, you need to select the "Where" option from its Total row. Click the State column's Total row, click the list arrow, and select Where from the list. Let's test our query. Click the Run button on the toolbar. Access prompts you to enter the first parameter, as shown in figure. You want to summarize records from Washington. Type WA and click OK. Access prompts you for the next parameterthe start date. Type 1/1/00 and click OK. Access prompts you for the last parameterthe end date. Type 6/30/00 and click OK. Access displays the results of the parameter query, as shown in figure. The order of your query may be different if you put the FirstName field on the design grid before the LastName field. Save the query as qryParameter and then close the query. Page 3The helps you find records that have the same value in one or more fields. So when would you need to use a Find Duplicates Query?  Step Two: Select the field or fields that contain the duplicate values.The query displays those records that have duplicate values in both the LastName and FirstName fields.Here are a few scenarios:
Access provides a wizard to make creating a query that finds duplicate information in a snap. "Arrr-rrgh!" you shout as you realize that you and another co-worker have just entered the same customers into a database. Fortunately, you can find the duplicated records by using the Find Duplicates Query Wizard.
To use the find duplicates query wizard:
Page 4The helps you find the records in one table that do not have matching records in another table. Specify the field that will join the records in the first table to the records in the second table.The query displays those records in the Customers table that do not have any matching records in the tblCustomerTours table.Some scenarios when you might need to create such a query include:
In this lesson you will use the Find Unmatched Query Wizard to create a query to find customers who have never booked a tour.
To use the find unmatched query wizard:
|
Create and run a Top values query in Design view
zusammenhängende Posts
Urheberrechte © © 2024 wiewird Inc.
