Solved

Updating a value in a column

Posted on 2013-06-10
7
260 Views
Last Modified: 2013-06-10
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
0
Comment
Question by:geeta_m9
  • 3
  • 3
7 Comments
 
LVL 84
ID: 39235684
Can you switch to SQL view and show the full value of the SQL statement?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 39235685
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
 

Author Comment

by:geeta_m9
ID: 39235700
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39235724
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
 

Author Comment

by:geeta_m9
ID: 39235745
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39235768
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
 

Author Comment

by:geeta_m9
ID: 39235776
That's ok. Your suggestions helped point me in the right direction.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sort Time by AM and PM in query 2 18
Error in Rs.MoveNext & Duplication of Record 14 25
MS Printer Preview Searchs for Printers 16 15
TSQL previous 5 21
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now