Top values in an update query

[aziz rasul]

I have an update query in which I only want the top 20 to be updated. Is this possible?

I have currently worked around the problem by creating a select query with the top 20 values and linked it to my update query. However I was wondering whether I could do the whole thing in the update query itself without creating the select query.

 

[raskew]

Create the query as a select query, then click on the QueryType icon on the toolbar to convert your select query to an update query.

HTH - Bob

 

[aziz rasul]

But when I do that I no longer can set the Top value parameter. When I go to datasheet view when I'm in the Update Query stage I get more than 20 records!

 

[raskew]

Aziz -

Hmmm. I'm not experiencing that. Would you post your query SQL.

Bob

 

[aziz rasul]

This is my original select query: -

SELECT TOP 20 tblGroupSessions.groupID, tblGroupSessions.groupsessionID, tblGroupSessions.sessionstartdate
FROM tblGroupSessions
WHERE (((tblGroupSessions.groupID)=67) AND ((tblGroupSessions.sessionstartdate)>=Date()+11));

and this is when I change it to an update query: -

UPDATE tblGroupSessions SET tblGroupSessions.groupsessionID = [groupsessionID]+1
WHERE (((tblGroupSessions.groupID)=67) AND ((tblGroupSessions.sessionstartdate)>=Date()+11));

 

[Rabbie]

Why dont you do this in two stages. Firstly use your Select query to find the twenty records you are interested in and then write a second query to update these records.

 

[aziz rasul]

That's what I'm doing. I was just asking whether it was possible to do it in 1 query alone.

 

[honda882000]

You should be able to use a select statement in your WHERE clause, but from what I have seen, Access does it faster if you break it into 2 steps for some reason.

Anyways, this will update the top 3 records of Table1:

UPDATE Table1 SET field = "test" WHERE ID IN (SELECT TOP 3  id FROM Table1);

Hope this helps.

 

[aziz rasul]

Thanks for that. I'll try that.