Updating Table

kirkm

Registered User.
Local time
Tomorrow, 00:05
Joined
Oct 30, 2008
Messages
1,257
I've run a delete query on my table and now want to update its count Field (to number 1 to recordcount).
Can an update query do this? I'm a bit stumped how to construct the sql.

Or should I just iterate through a recordset and edit each record ?
 
You can do an update based on a ranking query.
Or it is just as easy to loop a recordset.
 
Thanks Maj. After reading the link the latter method seems way easier !
 
Here is a real simple one. I just did the rank query. But the problem is that query is not updateable, so you are back to doing it with a recordset anyways.
 

Attachments

Thanks. A lot easier to follow like that.
 
With three Queries The Customers Table can be updated with the Rank Values, taking your qryRank Query as Source (taking MajP's attachment as an example), rather than manually updating each record.

SELECT Query1 SQL: SELECT Customers.*, qryRank.CustomerRank AS Rank
FROM Customers INNER JOIN qryRank ON Customers.CustomerID = qryRank.CustomerID;

Make Table Query2 SQL: SELECT Query1.* INTO CustomersOut FROM Query1;

Update Query3 SQL: UPDATE CustomersOut SET CustomersOut.CustomerRank = [CustomersOut].[Rank];
 

Users who are viewing this thread

Back
Top Bottom