![]() In the dialog box, click the table that you want to use in the query, click Add to place the table in the upper section of the designer, and then click Close.ĭouble-click the table, and then click Close. On the Create tab, in the Queries group, click Query Design. If you want, you can enter this sample data into a new table manually, or you can copy this sample table to a spreadsheet program, such as Microsoft Excel, and then use Access to import the resulting worksheet into a table. The steps use the data in the following sample table. The first section demonstrates the basic steps in creating a top values query, and the second section explains how to find the next few employee birthdays by adding criteria. The steps in this section explain how to create a basic top values query that uses a sort order, plus a more advanced query that uses expressions and other criteria. Top of Page Find the most recent or earliest date A complete discussion of filters is beyond this topic.įor more information about creating and using filters, see the article Apply a filter to view select records in an Access database. For example, to see the dates for sales between April and July, you apply a filter. If you want to return all the records where the date matches, is prior to, or later than a specific date, you use a filter. If you want to return the records with the most recent or latest dates in a field, and you do not know the exact date values, or they don't matter, you create a top values query. To determine whether you should create a top values query or apply a filter, choose one of the following: Choosing between a top values query and a filter In addition, if you want to use a totals query, your data fields must include category information, such as a city or country/region field. The queries that this article describes fail if you try to run them against date values in a Text field. In addition, the date values must reside in a field set to the Date/Time data type. In that case, the cities become categories (you need to find the data per city), so you use a totals query.Īs you proceed, remember that, regardless of query type, your queries must use fields that contain descriptive data, such as customer names, and also a field that contains the date values that you want to find. For example, suppose that you need to find the sales numbers for a given date for each city in which your company operates. You use totals queries and aggregate functions only when you need to find data that falls into a set of groups or categories. If you convert the query, you then use an aggregate function, such as Max or Min to return the highest or lowest value, or First or Last to return the earliest or latest date. Depending on the results that you want, you apply either a sort order to the query, or you convert the query into a totals query. You create a top value query by first creating a select query. Rules for creating and using top values queries Who has the next birthday, or the next n birthdays? When did a customer last place an order? If a customer has not placed an order for a given period of time, you may want to move the customer to an inactive list. ![]() When did an employee last make a sale? The answer can help you identify a most-productive or least-productive employee. You can then use the data to answer several types of business questions, such as: You use a top value query when you need to find records that contain the latest or earliest dates in a table or group of records. Understand how top value queries work with datesįind the latest or earliest dates for records in categories or groupsįind the most recent and the earliest dates ![]() You can use the information that this type of query returns to answer a variety of business questions, such as when a customer last placed an order. This article explains how to use a top value query to find the most recent or earliest dates in a set of records. Access for Microsoft 365 Access 2021 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007 More.
0 Comments
Leave a Reply. |