Show All

Hide All

Finding records based on the order in which they were entered involves two things - sorting the records in natural order, and optionally, limiting the view to display only the records that were first or last. When records are sorted in natural order, the records entered first appear at the top and the records entered most recently appear at the bottom.

For example, the following illustration shows the Products table in the Northwind.mdb sample database in natural order. The record for the product Chai was entered first so it appears on top. The record for the product Original Frankfurter grüne Soße was entered last, so it appears at the bottom.

Records are displayed in natural order in datasheets and forms, until you apply a sort order to change the order of records. To revert back to the natural sort order, all you need to do is remove any sort orders that are explicitly applied to the records. For step-by-step information, see the section View records in natural order. This section also describes how to quickly navigate to the last or first record after the records have been sorted in a datasheet or form.

If you don't want to see all the records, then you can limit your view to include just the oldest or most recently entered record. For example, you can choose to see just the Product ID of the newest product record added to the Products table. The following illustration shows the ID of the newest product record.

You can also choose to see the entire first or last record. The following illustration shows the newest product record.

To sort and then filter records based on natural sort order, you need to create a Totals query, optionally, a select query. For step-by-step information, see the section Display only the oldest or most recent record.

Let's take this one step further - what if you want to group the records, and then find the record that was entered first or last in each group? This is useful if when there are several records, and records must be grouped first. For example, let's say you want to find the oldest or newest product in each category.

You can group records by one or more fields, such as the Category field, and then select a record based on natural order within each group, by creating a Totals query. For step-by-step information, see the section Find the oldest or most recent record in each group.

Finding the oldest or most recent record is also useful when dealing with duplicate records. If you have more than one record for each product, then you might be interested in seeing just the original (first entered) or most recent (last entered) record for each product. The following illustration shows the two records for the product Chef Anton's Cajun Seasoning and the two records for the product Mishi Kobe Niku. Even though the product names are spelled differently, it is obvious that they are duplicates.

If you don't want to see multiple records for each product, you can create a Totals query that includes all the unique records in the table, and only the original or latest record for products that have duplicate records. You can also take steps to consolidate or delete duplicate records. For more information about how to find and tackle duplicate records, see the topic Find, eliminate, or hide duplicate records in Access.

View records in natural order

To view records in the order they were orginally entered, simply remove all sort orders from a datasheet or a form.

1. In datasheet or form view, in the Records menu, point to Filter, and then click Advanced Filter/Sort.
2. In the grid, clear the Sort row of all the columns.

   

3. Click Apply Filter to see the records sorted in the order they were entered.

The underlying table or form will show the records in the order they were entered. If the object that is open in datasheet view is a query, then you need to do some additional steps.

Additional steps for displaying records in a query in natural order

The records entered first will appear on top and the newest records will appear at the bottom. To quickly move to the first or last record, use the navigation buttons at the bottom of the window.

Click the Last button to move to the last record. To see the second last record, click Last, then click Previous. To go back to the first record, click First.

Display only the oldest or most recent record

Let's first create a Totals query to see the ID of the newest product, in other words, the ID of the product whose record was entered last in the Products table.

1. In the Database window, under Objects, click Queries, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design view, and then click OK.
3. In the Show Table dialog box, double-click the Products table, and then click Close to close the dialog box.
4. Add the ProductID field.

    Note   Do not add any more fields to the grid.

5. Click Totals on the Query Design toolbar. This adds the Total row to the grid — this row displays Group By in the ProductName column.
6. Change the Total row to Last to see the newest product ID. You can set the row to First to see the ID of the product whose record was entered first in the table.

   

    Note   Do not add any other fields. If you are interested in seeing the name, category, or other details of the last or first record, you need to create a second query. The steps for creating the second query are listed after this procedure.

7. Click View to switch to Datasheet view.

   LastOfProductID
   77

   You see the ID of the last entered record in the Products table.

    Tip   To change the column name to something different, switch back to design view. In the Field row of the corresponding column, specify the name you want in the format (new name): (field name). For example, to display Last ProductID as the column name, edit the Field row of the ProductID column to Last ProductID: ProductID.

8. Save the query as Last ProductID query.

If you want to see the entire record and not just a value from the first or last record, you need to create a second query that builds on the query you just created.

Let's create a query to display the entire last record, not just the ID of the record.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. In the Show Table dialog box, click the Both tab, and then double-click the Last ProductID query that you just created and the Products table.
4. Join the totals query to the Products table. Joining helps the new query bring the record from the Products table that corresponds to the ID returned by the totals query. Create a join by dragging the Last ProductID field in the totals query to the ProductID field in the Products table.

   

5. Drag the asterisk (*) from the Products table to include all the fields in the table.

   

6. Click View on the toolbar.

   You will see the record that was entered last in the Products table.

   ProductID	ProductName	Supplier	Category	...
   77	Original Frankfurter grüne Soße	Plutzer Lebensmittelgroßmärkte AG	Condiments	...

Find the oldest or most recent record in each group

Let's find the newest product in each category. This involves creating a Totals query that groups product records by CategoryID, sorts the records within each category in natural order, and then selects the first (oldest) or last (newest) record within each group.

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. In the Show Table dialog box, double-click the Products table, and then close the Show Table window.
4. Click Totals on the Query Design toolbar to add the Totals row to the design grid.
5. Add the CategoryID field. This is the field that contains the values that will be used for grouping the records. The Total row displays Group By. This implies that the records will be grouped by values in this field.
6. Add the ProductName field. This is the field that help us identify the newest product in each category. The Total row displays Group By, but you don't want to group on this field. To see only newest products in this column, change the Total row to display Last. You can set this row to First to see the oldest product in each category.

   art

    Note   Do not add any other fields. A Totals query can only include fields on which you are grouping, and the field that helps you identify the first or last record.

7. Click View on the toolbar.

   You will see one product name per category. The names correspond to the newest product introduced in each category.

   Category	LastOfProductName
   Beverages	Lakkalikööri
   Condiments	Original Frankfurter grüne Soße
   Confections	Scottish Longbreads
   Dairy Products	Mozzarella di Giovanni
   Grains/Cereals	Wimmers gute Semmelknödel
   Meat/Poultry	Pâté chinois
   Produce	Longlife Tofu
   Seafood	Röd Kaviar

    Tip   To change a column name, switch back to design view. In the Field row of the corresponding column, specify the name you want in the format (new name): (field name). For example, to display Newest Product as the column name, edit the Field row of the ProductName column to Newest Product: ProductName.