UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Select Last 100 Records, Any Versions    
 
   
BruceM
post Jul 22 2019, 11:52 AM
Post#1


UtterAccess VIP
Posts: 7,985
Joined: 24-May 10
From: Downeast Maine


SQL Server 2016, Access 365

I have an Access front end with SQL Server back end. I want to show the 100 most recent records in a data entry form. The 100 is somewhat arbitrary, but the question is about technique. It is often necessary to revisit recent records. For older records, a search for a specific record (or several records) is sufficient.

I could SELECT TOP 100, and ORDER BY DESC. However, I want the latest 100 records sorted in ascending order. It seems there are several ways I could approach this. For instance, I could use an Access query to SELECT TOP 100 DESC from another query, and sort the result ASC. The other query could be named, or it could be a SELECT statement within another query.

Or I could use an Access query to select from a SQL Server View that selects TOP 100...DESC. Sorting a view is a little dicey, as I understand, but this approach seems to work.

BTW, sorting is done on a number field that is incremented via code.

Is there a best practice for what I am trying to do?
Go to the top of the page
 
theDBguy
post Jul 22 2019, 11:57 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,566
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure if it's best practice or not, but I would probably settle with something like:
CODE
SELECT * FROM
(SELECT TOP 100...
FROM...
ORDER BY [FieldName] DESC)
ORDER BY [FieldName]

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
BruceM
post Jul 22 2019, 01:49 PM
Post#3


UtterAccess VIP
Posts: 7,985
Joined: 24-May 10
From: Downeast Maine


That's what I kept coming back to. I'll try it as a 100% Access query rather than involving a SQL Server view. Thanks, DBGuy!
Go to the top of the page
 
theDBguy
post Jul 22 2019, 02:18 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,566
Joined: 19-June 07
From: SunnySandyEggo


Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 05:51 AM