Solved

Update Top n Records

Posted on 2008-06-23
4
1,199 Views
Last Modified: 2008-06-23
Hello Experts,

I have a table (tblReg) that I wish to Update a field named "flag" to Yes. I only want to Update the TOP 100 records. I realize that the property Top Values is available only for a Select query not available for an Update query.

How can I simply Update this field from No to Yes with the criteria being only the first 100 records?
It does not matter which records, just 100 of them.

Thanks    
Jim
0
Comment
Question by:JimK31
  • 2
4 Comments
 
LVL 75
ID: 21847436
Create a TOP 100 Select query first to limit the recs, then ... create a Update query that Joins on that query - so that only 100 recs will be affected.  Or, use a WHERE clause with the IN operator to 'join' on that query.

mx
0
 
LVL 11

Accepted Solution

by:
CMYScott earned 250 total points
ID: 21847454
I'd try something like this...

(replace someField with a unique id column-name and orderField by the field you want to order by for the TOP)

UPDATE tblReg SET Flag=True WHERE tblReg.someField IN (SELECT TOP 100 someField FROM tblReg ORDER BY orderField)
0
 

Author Comment

by:JimK31
ID: 21847597
Thanks. Have not used a WHERE  IN statement. Thanks for spelling in out.
0
 
LVL 75
ID: 21847649

:-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now