Updating Table (1 Viewer)

[kirkm]

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 ?

 

[MajP]

You can do an update based on a ranking query.

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

Or it is just as easy to loop a recordset.

 

[kirkm]

Thanks Maj. After reading the link the latter method seems way easier !

 

[MajP]

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.

 

[kirkm]

Thanks. A lot easier to follow like that.

 

[apr pillai]

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];