Need help modifying a large number of specific entries at once-CodePudding

My company has a large Access database which lists every user that has ever existed for a particular client of ours. This database is curated manually. I have been asked to delete 500 users. This means I have to modify three columns for each user:

Status (must be changed to "deleted")
Date Deleted (to current date)
Date Revised (to current date)

Obviously, I don't want to have to Ctrl F and change these fields manually for over 500 users. What is the easiest way to go about doing this more quickly?

I have the list of users that need to be deleted in Excel. I tried to create a query that shows all of these users in one table so that I don't have to sort through the users who don't need to be modified. It looked something like this:

SELECT UserID, Status, Date Deleted, Date Revised
FROM [database name]
WHERE UserID = 'a'
OR UserID 'b'
//(and then 500  more OR statements for each UserID)
ORDER BY UserID;

I figured if I can at least do this, at least I'll have all the users I need to edit in front of me so that I don't have to Ctrl F. But this didn't work, because it exceeded the 1,024 character limit in Access. Any ideas for how I can accomplish this?

CodePudding user response：

Don't attempt to write 500 UserID values into your SQL statement. Instead, import the Excel list as a table into your Access database. Then you can use that list of UserID values to identify which rows of your main table should be updated.

UPDATE MainTable AS m
SET m.Status = 'deleted', m.[Date Deleted] = Date(), m.[Date Revised] = Date()
WHERE m.UserID IN (SELECT UserID FROM tblFromExcel)