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
likely compile this list manually. However, there’s an easier way.
the query in Design View.
Sort cell under the Total Sales field, select Descending.
the Query Design toolbar, and press [Enter].
achieved the top 10 values.
figures, follow these steps:
Sort cell under the Total Sales field, select Ascending.
press [Enter].
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:
Ascending: Displays bottom values.
Descending: Displays .
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 2
Click 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 3
The 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?
Here are a few scenarios:
To search for duplicate values in an Orders table to find out which customers have placed more than one order.
To search for duplicate values in several fields to search for data-entry errors. For example, if you and another user accidentally entered the same customers into a table, you could search for duplicate values in the LastName and FirstName fields to find and delete the duplicated records.
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.
If necessary, open the Lesson 6 database.
Now let's create a new query.
In the Database window, click the Queries icon in the Objects bar and click the New button.
The New Query dialog box appears.
Select the Find Duplicates Query Wizard and click OK.
The first step of the Find Duplicates Query Wizard appears, as shown in figure. Choose the table or query that you want to sift through for .
Select the tblCustomers table and click Next.
The second step of the Find Duplicates Query Wizard appears, as shown in figure. Her you will tell Access which field or fields might contain the duplicate information.
Since you are trying to find duplicate customers, you decide to search the FirstName and LastName fields for duplicate values.
Double-click the LastName and FirstName fields.
The LastName and FirstName fields appear in the "Duplicate-value fields" list.
Click Next.
The next step of the appears. You can select any field (other than the ones you specified in Step 4) that you want to be displayed in the query.
You decide to display the City and State fields so you can verify that the records are duplicates.
Double-click the City and State fields. Click Next when you're finished.
You're finished! Well, almost. You have to give your query a name, or you can accept the default name Access gives the query.
Click Finish.
Access saves the query with the name "Find duplicates for tblCustomers" and displays the results of the query, as shown in figure.
Close the query.
To use the find duplicates query wizard:
in the database window, click the queries icon in the objects bar and click the new button.
select find duplicates query wizard and click ok.
select the table or query you want to search and click next.
double-click the field(s) that may contain the duplicate values and click next.
double-click any additional fields that you want to appear in the query results and click next.
click finish.
Page 4
The helps you find the records in one table that do not have matching records in another table.
Some scenarios when you might need to create such a query include:
To find customers who have never placed an order.
To find products that have never been purchased.
To find "orphan" records. If you haven't enforced referential integrity in your related tables, deleting a record in one table could leave one or more orphan records in a related table. For example, if you delete a customer record from a Customer table, you may leave several for that customer in an Order table.
In this lesson you will use the Find Unmatched Query Wizard to create a query to find customers who have never booked a tour.
In the Database window, click the Queries icon in the Objects bar and click the New button.
The New Query dialog box appears.
Select the Find Unmatched Query Wizard and click OK.
The first step of the Find Unmatched Query Wizard appears, as shown in figure. You need to choose the table or query whose values you want to display in the query. Since you want to find customers without any tour packages, you would select the tblCustomers table.
Select the tblCustomers table and click Next.
The second step of the Find Unmatched Query Wizard appears. Here you have to tell Access which table contains the related records. Let's select the tblCustomerTours table.
Select the tblCustomerTours table and click Next.
The third step of the Find Unmatched Query Wizard appears, as shown in figure. Here you have to specify the related field to join the records in the first table to the records in the second table. Once you have selected the matching record in both tables, click the
button to join the two tables.Verify that the CustomerID field is selected in both tables and click the button.
The Matching fields area displays the fields used to join the tables (CustomerID = CustomerID).
Click Next.
Almost there! Now you have to specify which fields you want to see in the query.
Double-click the LastName, FirstName, City, and State fields.
The LastName, FirstName, City, and State fields should all appear in the "Selected fields" list.
Click Next.
Here you can give your query a nameor you can accept the default name that Access gives the query.
Click Finish.
Access saves the query with the name "tblCustomers Without Matching tblCustomerTours" and displays the results of the query, as shown in figure.
Close the query.
To use the find unmatched query wizard:
in the database window, click the queries icon in the objects bar and click the new button.
select find unmatched query wizard and click ok.
select the table whose values you want to display and click next.
select the table that contains the related records and click next.
specify the related fields that join the two tables, then click the button to join the two tables and click next.
double-click any additional fields that you want to appear in the query results and click next.
click finish.