MS Access 03 - muliplte "ifs" in one field of update query's criteria??

Question

Can I run an update query and in one single field run multiple updates like:

"joe doe" updated to 334 & "barry davis" updated to 456, etc

basically I want to convert the name to number (in my case it would be the employee ID#), and I have about 500 employees so one at a time is a little out of the question. the table is import from excel and i don't know how to do this pre-import ethier!

thanks for any help!

Answer 1

Whilst it is possible to use multiple Iif statements in Access, you probably want to do this using an update query in Access instead.

It's difficult to be certain without more detail of your data sources - I'm assuming here that you already have a table in your Access listing user names to their IDs (which I'm calling Users). If you don't already have an Access table listing user names and IDs, you need to get one.

I'm also assuming you've imported the data you want to update from Excel into a table called ExcelImport.

Obviously, you'll need to substitute the table names used in my example for your actual table names.

Rather than replacing the user name in the Excel data, it might be wiser to add a new column onto ExcelImport to hold the user ID.

1. Create a new query ("Create a new query in design view")

2. Add the Users and ExcelImport tables to your query

3. Create a link between the tables by clicking and dragging between the "user name" fields on the Users table, then releasing over the "user name" field on ExcelImport. This links the two tables together on this field.

4. Change the type of the query to Update (Query > Update Query)

5. Double-click on the ExcelImport user name field (or the additional user ID field, if you're using it) to target it for update. It should appear in column one of the bottom half of the query designer.

6. In the "Update to:" field of this column, type [Users].[UserID]

7. Run the query. A message box should appear asking you to confirm that you want to update n rows. Click OK to confirm.

Given that your data comes from Excel, you should check that all the user IDs have been correctly set, since it's possible that "Joe Doe" in the Excel data is stored as "Joseph Doe" in the users table - in which case, you'll need to manually edit the Excel data to get a match.

Answer 2

I have about 500 employees so one at a time is a little out of the question

Definitely :)

Consider this using a standard issue Sequence table of unique INTEGER values:

SELECT SWITCH(
              seq = 1, 1, 
              seq = 2, 2, 
              seq = 3, 3, 
              seq = 4, 4, 
              seq = 5, 5, 
              seq = 6, 6, 
              seq = 7, 7, 
              seq = 8, 8, 
              seq = 9, 9, 
              seq = 10, 10, 
              seq = 11, 11, 
              seq = 12, 12, 
              seq = 13, 13, 
              TRUE, 999
             ) AS seq_1
FROM Sequence;

Beyond 14 cases it generates an error, "Expression too complex in query expression". So I don't think you would get anywhere near 500 cases!

Better make this a lookup table.