Update top 100 values?

S

Sheila D

Hi

I have a query which extracts 100 top records which is linked to Word mail
merge to produce letters. I then want to update those records to show a
letter has been sent.

The update query does not support top n values so at the moment I'm using
Find & Replace which is fiddly and not accurate as after the first replace
the dataset changes to reflect new info!

Any suggestions much appreciated, I'm sure there must be an easy way (not
really a VB person so simple answers would be best!!) Thanks

Sheila
 
M

Michel Walsh

Hi,


In Jet (Access), TOP N is updateable. The top 100 should not be the real
cause of your problem if your query is not updateable.


In MS SQL Server, TOP N is not updateable, but getting the primary key in a
WHERE clause should do:

SELECT ... FROM ... WHERE pk IN( SELECT TOP 100 pk FROM ... ORDER BY .... )


Hoping it may help,
Vanderghast, Access MVP
 
S

Sheila D

Hi

Thanks for your answer which put me on the right track - using the original
query as a sub query for the Update query did the trick!

Sheila
 
Top