Updating Field with multiple criteria using Update Query (1 Viewer)

Apples241

Registered User.
Local time
Today, 02:05
Joined
Aug 29, 2016
Messages
54
Hello,

I am updating the StatusID field in a table using an update query. Can I update one field with different values in the Criteria field and the Update To: field? I have to update the values if StatusID says "Complete" update to "1", if StatusID says, etc. (pic below)

https://drive.google.com/open?id=0B9IyKJSJ52ghRHlCNTYxdjNiU2s

I tried adding StatusID field 3 times and putting the Criteria on the same row but my query was blank (b/c I believe is saying I'm look for Complete AND Not Started AND, etc.) But I tried adding the Criteria staggered in the OR fields (pictured above) and I came up with results that I'm not comfortable with hitting Run to Update the table. (see below)

https://drive.google.com/open?id=0B9IyKJSJ52ghRFJDTzMySkFXWnM

How do you update a single field with multiple criteria and multiple update to: values using a query (if you know in SQL that is fine, as well, but also I would like to know in a query, please)?

Thank you.
 

plog

Banishment Pending
Local time
Today, 04:05
Joined
May 11, 2011
Messages
11,613
This smells like you are trying to do the right thing the wrong way.

My guess is that you created a Status table where 1=Complete, 2=Not Started and 3=Rescheduled. Correct? I mean why move from text to numbers?

Speaking of words to numbers, you are not doing that. You are not replacing text with numbers you are replacing text with more text. "1" is not a number. 1 is a number. My guess is StatusID should be a numeric field. Currently its text if its storing values like "Completed".

Please confirm that you are moving the Status values to a new table and I can make this simple.
 

Apples241

Registered User.
Local time
Today, 02:05
Joined
Aug 29, 2016
Messages
54
plog, Yes, then that must be why I'm having so much trouble populating my combo box for the StatusID on my form. It is b/c the numbers I'm updating that StatusID field to is not a number b/c of this query.

What I'm trying to do is change my text values, like Completed, Not Started, etc. in the StatusID field of my table to the foreign key ID fields numbers, 1 for Complete, 2 for Not Started etc., b/c I have a StatusID table that has 1 for Complete, 2 for Not Started etc. in it. I imported this table I'm doing the query on, tblNode, into Access from Excel and I need to change the text values to numbers to match the ID fields in my StatusID table. The node table and the StatusID table are a one to many relationship.

After the update query, I had to go back into the tblNode and change my StatusID field from short text to number, but the combo box I have created on my form for StatusID is not showing Complete, Not Started, etc.

I know how to set up combo boxes, so how do I get Access to see the numbers as numbers in my StatusID field that I just updated? Thank you.

P.S. and to answer your last question, I'm not moving the StatusID field values to a new table. I did the update query of the StatusID field within the node table where the StatusID field resides.
 

plog

Banishment Pending
Local time
Today, 04:05
Joined
May 11, 2011
Messages
11,613
I'm lost. Do you or do you not have a table for statuses? Do you have a table that says 1=Complete, 2=Not started, etc.?
 

Apples241

Registered User.
Local time
Today, 02:05
Joined
Aug 29, 2016
Messages
54
Yes, I do have a table for Status' that has complete, not complete, etc.

And I'm trying to put the primary key value from that table into my node table, which has the text, complete, not complete.

Here is my node table with the statusID after the update query with what I thought were the numbers (primary keys) seen in my Status table. You can also see my Status table opened there. After my update query, Access saw my numbers as text, as you helpfully pointed out.

https://drive.google.com/open?id=0B9IyKJSJ52ghOU5wQzNpeEJlcUU
 

plog

Banishment Pending
Local time
Today, 04:05
Joined
May 11, 2011
Messages
11,613
And I'm trying to put the primary key value from that table into my node table, which has the text, complete, not complete.

Now, I'm super lost. The image you just posted shows that StatusID in your Node table contains the number from tblStatus. Sounds like everything is done.
 

Apples241

Registered User.
Local time
Today, 02:05
Joined
Aug 29, 2016
Messages
54
(Yes, I'm sorry, I got off track b/c my 1s,2s, 3s, etc. for the Status field I thought was converted to text and so it wasn't populating my Status combo box on my form. I think I was actually looking in a record that wasn't supposed to have values. I'm sorry about that.)

Yes, though I have already used the update query to update my field, I originally wanted to see how to update the Status field in my Node table at one time with the criteria: if field says "Complete", then put 1, if field says "Not Complete", then put 2, etc.--all using an update query.

I wanted to find a way to update my Node table at one time without having to run 4 different update queries. I don't know if you can help with that or not. Thank you for your replies.
 

plog

Banishment Pending
Local time
Today, 04:05
Joined
May 11, 2011
Messages
11,613
You would have used your Status table.

1. Create a new query based on Node and tblStatus.

2. Link them via Node.StatusID to tblStatus.Status

3. Bring Node.StatusID into the bottom of the query.

4. Change the query to an UPDATE query.

5. Have Node.StatusID update to tblStatus.StatusID

6. Run the query.
 

Apples241

Registered User.
Local time
Today, 02:05
Joined
Aug 29, 2016
Messages
54
Oooh! I wish I thought of that!! Thank you, plog. I appreciate your help.
 

Users who are viewing this thread

Top Bottom