Updating a value in a column

I have an update query which I would like to run to update a value in a column for a particular set of records using the corresponding values from another table.

Currently in the "Update To" section of the update query (see attached), I am updating the value in the column, "Letter" with the corresponding value in the NSCPP_Students_Temp table. However, I would like to add an "N" before the value from the other table. So for instance, if the  NSCPP_Students_Temp table record has the value "C",  then I would like the value "NC" written to the column of NSCPP_Students. I tried using concatentation, i.e. "N" & [NSCPP_Students_Temp].Letter but it isn't working, i.e., it still writes the letter without the "N" in front.
Update-Query-No-Shows.jpg
geeta_m9Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
What does "isn't working" mean?  Normal syntax would be something like

Update yourTable
INNER JOIN yourOtherTable
ON yourTable.ID = yourOtherTable.ID
SET yourTable.[Letter] = "N" & yourOtherTable.[Letter]

However, depending on your primary keys, this may actually not be an updateable query.

Try writing it as a SELECT query:

SELECT yourTable.Letter, yourOtherTable.Letter as Letter2, "N" & yourOtherTable.Letter as Letter3
FROM yourTable
INNER JOIN yourOtherTable
ON yourTable.ID = yourOtherTable.ID

Does this query display the correct information?  Is it updateable?  Can you change a value in yourTable.Letter column from this SELECT query?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you switch to SQL view and show the full value of the SQL statement?
0
 
geeta_m9Author Commented:
Here is the SQL:

UPDATE [NSCPP Students] INNER JOIN NSCPP_Students_Temp ON [NSCPP Students].PID = NSCPP_Students_Temp.PID SET [NSCPP Students].Letter = "N" & [NSCPP_Students_Temp].[Letter];
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Dale FyeCommented:
And that is not working?

Did you try the SELECT statement?  Is the query updateable when you do it as a SELECT?

Is PID the primary key in table [NSCPP Students]?

Is your data in Access or linked from some other relational database?
0
 
geeta_m9Author Commented:
If found out the error did not lie with the query but by my "Temp" table not having any records in it to begin with because the data wasn't being imported into the correct table. I should checked the code.

I fixed the problem and it is now working.

I appreciate your help and I am sorry for wasting your time.
0
 
Dale FyeCommented:
Trust me, we have all made that mistake at least once.

You solved your own problem, I don't have a problem with you withdrawing the points you awarded me.  Just click the "Request Attention" hyperlink on your original post and indicate you would like to reallocate points.

Dale
0
 
geeta_m9Author Commented:
That's ok. Your suggestions helped point me in the right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.