Update Top N records in Access 2007?

Aldred@office

unread,

Jan 6, 2010, 2:24:03 AM1/6/10

to

Hi all,
I have googled and there is Update top N in SQL 2005. However, looks like
it is not in Access 2007. Can someone please suggest me what to do to
archive the same result in Access 2007?

My Query looks like this and Access complains it is wrong.

UPDATE top 7 tDelivered SET wanted = true where wanted = false

Thanks.

XPS350

unread,

Jan 6, 2010, 4:31:50 AM1/6/10

to

Access knows UPDATE but unlike with SELECT you can not use the
predicate TOP.

Note also that your statement is at least ambiguous.
Do you want to update the top 7 records (but only records with the
value false) or do you want to update the top 7 of records with the
value (in this case you will always update 7 records)? I assume the
first.

You may create two queries. The first (Q1) to select records:
SELECT TOP 7 Wanted FROM tDelivered
The second to update:
UPDATE Q1 SET Wanted=True WHERE Wanted= False

Groeten,

Peter
http://access.xps350.com

John Spencer

unread,

Jan 6, 2010, 8:50:18 AM1/6/10

to

PERHAPS what you want is the following

UPDATE tDelivered
SET Wanted = True
WHERE tDelivered.PrimaryKey IN
(SELECT TOP 7 PrimaryKey
FROM tDelivered
WHERE Wanted = False)

Since I don't know exactly what you are attempting to do I can't say that the
above will do what you wish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Duane Hookom

unread,

Jan 6, 2010, 10:16:01 AM1/6/10

to

There should be some type of ORDER BY clause that determines how TOP is
defined.

--
Duane Hookom
Microsoft Access MVP

"John Spencer" wrote:

> .
>

John Spencer

unread,

Jan 6, 2010, 11:00:28 AM1/6/10

to

Yes I agree that an order by clause should be included, but it is not
required. AND the poster did not say what the exact requirements were. I
really should have used ORDER BY PrimaryKey in this instance.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Aldred@office

unread,

Jan 7, 2010, 1:55:30 AM1/7/10

to

Thanks all about the possible solutions.
My initial attempt is to tick the wanted checkbox if it is not ticked. We
really don't mind which one was ticked since the users will just pick at
most 8 records at a time for manual processing. 7 when the user specified 1
of those and Access automatically pick another 7 for him.

Looks like putting order by is a good idea to avoid possible starvation in
the long run. But even I put Order by, the query still doesn't work in
Access 2007. The software complains that Syntax Error in Update.

Update top 7 tDelivered SET wanted = true where wanted = false order by
Ddate

"John Spencer" <spe...@chpdm.edu> 在郵件
news:uyGteluj...@TK2MSFTNGP02.phx.gbl 中撰寫...

Aldred@office

unread,

Jan 7, 2010, 1:58:59 AM1/7/10

to

Yes, it works just as well a I wanted.

Thank you John.

"John Spencer" <spe...@chpdm.edu> 在郵件
news:O0Tnvctj...@TK2MSFTNGP04.phx.gbl 中撰寫...

vanderghast

unread,

Jan 7, 2010, 6:39:07 AM1/7/10

to

UPDATE savedQuery SET wanted=true WHERE wanted = false

with savedQuery:

SELECT TOP 7 ...

would also do the job.

Vanderghast, Access MVP

"Aldred@office" <aldred> wrote in message
news:3C33B2FC-08EA-4AB0...@microsoft.com...

Aldred@office

unread,

Jan 8, 2010, 1:13:40 AM1/8/10

to

This is a stored procedure in Access?

"vanderghast" <vanderghast@com> 嚙箭嚙締嚙踝蕭
news:C3786A3E-FC50-4733...@microsoft.com 嚙踝蕭嚙踝蕭嚙篇...

John W. Vinson

unread,

Jan 8, 2010, 12:43:37 PM1/8/10

to

On Fri, 8 Jan 2010 14:13:40 +0800, "Aldred@office" <aldred> wrote:

>This is a stored procedure in Access?
>

>"vanderghast" <vanderghast@com> ?b?l??
>news:C3786A3E-FC50-4733...@microsoft.com ?????g...

>> UPDATE savedQuery SET wanted=true WHERE wanted = false

No, Access does not have Stored Procedures (unlike SQL/Server); this is the
SQL view of a Query.
--

John W. Vinson [MVP]

David W. Fenton

unread,

Jan 8, 2010, 7:27:54 PM1/8/10

to

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:9lrek550agtt0erob...@4ax.com:

If you create them with DDL using ADO or SQL 92 mode in Access, they
are called procedures. And if you think about it, outside of Access,
it really works out like this:

Access SELECT/CROSSTAB query = SELECT statement = VIEW
Access Action Query = INSERT/DELETE/UPDATE statement =
SPROC

Now, a stored procedure in a server database can do a lot more than
just contain a DDL statement, but on a server database, persistent
DDL statements are stored as stored procedures.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/