Update Top N records in Access 2007?

[Aldred@office]

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]

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.

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]

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]

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

 

[John Spencer]

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]

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

 

[Aldred@office]

Yes, it works just as well a I wanted.

Thank you John.

 

[vanderghast]

UPDATE savedQuery SET wanted=true WHERE wanted = false



with savedQuery:

SELECT TOP 7 ...


would also do the job.

Vanderghast, Access MVP

 

[Aldred@office]

This is a stored procedure in Access?

UPDATE savedQuery SET wanted=true WHERE wanted = false



with savedQuery:

SELECT TOP 7 ...


would also do the job.

Vanderghast, Access MVP

 

[John W. Vinson]

This is a stored procedure in Access?

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

 

[David W. Fenton]

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

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.