Show the three newest records in a multiple records form

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
Is it possible to only show the three newest records in a multiple items form? I only want to show three records along with the new record spot for entering of course new records.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
You can if you have something to identify the newest records. This could be either the unique ID if it's a counter, or a creation date field.

Say you have a table X having a counter field named ID. To have only the newest 3 records on your form, you should set the forms recordset to something like:

Select Top 3 Field1, Field2, etc From TableX Order By ID DESC.
 
Upvote 0
As I don't have any information regarding your database, I can only give you a general description.

For the example we use a fictive table, named StockItems having the fields (ID, StockNumber, StockDescription) The ID field is a counter (autoincremental).
We want to create a continuous form to enter data in the table StockItems.
The first step would be creating a query that will be used as the forms recordsource.

To display ALL records and fields on the form, the query has to be written as:

Code:
Select ID, StockNumber, StockDescription From StockItems

We save the query as qALLStockItems

In the design mode of the form, we set the recordsource to qALLStockItems.
So far, this is shouldn't be so hard to understand as this is the basics of creating a form with a recordsource.

Now let's focus on the query to retrieve only the 3 newest records. As we have an auto counter field as the ID, we know that the newest record has the highest ID. This means, that if we sort the records by the ID field in descending order, we have the newest records on top.
In SQL you can use the TOP [Number] operator to fetch any number of records from the top of a recordset, in your case 3, so take in mind (TOP 3).

The next SQL statement retrieves the last 3 records from the table StockItems.

Code:
Select TOP 3 ID, StockNumber, StockDescription From StockItems Orderby ID DESC

You could use this directly as the recordsource for the form, but, for data entry it will be al little confusing to have the newest record on top. So what you really want is to re-order the records from descending to ascending. Only this can't be done in the same step as shown above, because then you'll retrieve the oldest records instead of the newest.
Therefore, one more step is required. By only retrieving the ID field with the TOP operator, you can use the recordset as a Where clause. This technique is know as a correlated sub-query. It's not the most effective technique when it'comes to performance, but for not to big recordsets you'll be OK (actually you won't have any troubles unless you query multible tables with millions of records).
Correlated queries have an outer query and inner query (this one is used as the where condition).
The inner query in this example is as follows:

Code:
Select TOP 3 ID From StockItems Orderby ID DESC

Returning the ID's from the 3 newest records.

The outer query, is actually the first query we made, only extented with a Where Clause. Regarding the Where clause, as we need multiple matches from a unique field, the IN operator comes in.

Code:
Select ID, StockNumber, StockDescription From StockItems WHERE ID [B]IN[/B]

The last step to built the query is putting in the inner query, and the ORDER BY for the outer query, resulting in:

Code:
Select ID, StockNumber, StockDescription From StockItems WHERE ID [B]IN (Select TOP 3 ID From StockItems Orderby ID DESC) ORDER BY ID ASC[/B]
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top