• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Update SQL in MS Access

Using MS Access 2002 SQL ...

I want to update the values of a field in table one, a text field with numeric values to their respective text equivilent located in another table (two) which consists of 2 fields, the text equivilent and its numberic id number.  Each time I attempt using an update SQL, I get one error or another.  The following is an example as I need to do many of these.

UPDATE [Future Homes] INNER JOIN playuda
ON [Future Homes].FUTURE_HOME_ID = playuda.FFHome
SET playuda.FFHome = [future  homes].[future_home_name]
WHERE (((playuda.FFHome)=[future homes].[future_home_id]));

FYI:
playuda.ffhome is a numeric value (text field)
future homes.future_home_id is a numeric value (text field)
future homes.future_home_name is a text value (text field)

Thank you.
0
ellenjbr
Asked:
ellenjbr
  • 6
  • 5
  • 4
2 Solutions
 
mbizupCommented:
try this instad:


UPDATE [Future Homes] , playuda
SET playuda.FFHome = [future  homes].[future_home_name]
WHERE playuda.FFHome=[future homes].[future_home_id];
0
 
harfangCommented:
duplicate question: http:/Q_22713191.html
0
 
harfangCommented:
This being said, your JOIN and your WHERE clauses are redundant. Use one or the other, but not both (as Miriam suggests)

You might also consider removing the field FFHome entirely. Since you can always bring it into a query when you need it, it's often a bad idea to store it again in another table. This creates data redundancy and causes all sorts of integrity problems.

You don't tell us what error messages you got. This is essential if you want us to find the problem. Perhaps you don't have the proper primary key defined, or you have a data conversion error, or you get a non-updatable query for any other reason. Perhaps it does update, but not the way you think it should. We really can't guess.

(°v°)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mbizupCommented:
>duplicate question
*sigh*
Good eyes, Markus.
0
 
ellenjbrAuthor Commented:
No error message with this .... I want the numbers to be replaced with the text equivilent - into the ffhome field in table one.  The text equivilent is located in the second table .... Thank you.
0
 
ellenjbrAuthor Commented:
mbizup ...

this resulted in the same, FYI ... it simply does not change the numbers in the ffhome field to the text equivilent .... thank you
0
 
mbizupCommented:
Is FF home a key field?
How about adding a new field to playuda?  FFHomeText

UPDATE [Future Homes] , playuda
SET playuda.FFHomeText = [future  homes].[future_home_name]
WHERE playuda.FFHome=[future homes].[future_home_id];

I noticed you're a new member here (welcome aboard).  EE allows a maximum of 500 points per question, so duplicate questions at 500 a piece exceeds that.  I'll post a note in Community Support on your behalf.  They may either delete one of the questions, or reduce the points on both questions.
0
 
ellenjbrAuthor Commented:
Hi and Thank you .... I didn't realize it duplicated, but I did have some problem with the browser when submitting ...

No, it is not a key field and yes, I had thought of an additional field, however, it seems as though it should work and I have a number of these to do so I want to know how to do it correctly and understand what any issues are ... Thank you.
0
 
ellenjbrAuthor Commented:
I tried it and it did nothing different ... it did not insert the new text info into the new field ... no 'updating' is occurring ... thank you
0
 
mbizupCommented:
No error... just no updating?
Have you verified that there are cases where
 
playuda.FFHome=[future homes].[future_home_id];
0
 
ellenjbrAuthor Commented:
Yes!  Thank you.

The syntax must be off  ... is there another SQL type that I can use to do the same within Access ... I only know of changto in paradox/objectpal that would do it ....
0
 
mbizupCommented:
I don't quite understand what you're asking.
Is the back-end (Data file) an Access database?

If the syntax is off, you would get an error message (but it is my understanding that you are not getting one)

Are the "numbers" possibly formatted differently?  001 vs 01 vs 1?

Maybe change the WHERE Clause like this:

WHERE Val(playuda.FFHome)= Val([future homes].[future_home_id]);

If you get any errors, please post the specific message...
0
 
harfangCommented:
Hello again.

I recreated your tables:
playuda ( ffhome [text 50] )
future homes ( future_home_id [text 50], future_home_name [text 50] )

I entered the numbers 12, 13, and 14 into ffhome and created 20 records in future homes, with names.

To my surprise, your query worked almost at the first trial. As it stands, the third mention of future homes contains one space too many:

    SET playuda.FFHome = [future  homes].[future_home_name]
    --------------------------------------^

But this gives an error message, if run from the interface or if run from VB. Is the query you posted an exact copy-paste from your query? In that case, you must have gotten the same message.

Anyway, after removing the extra space, the query worked as expected. I believed that the redundancy between the JOIN and the WHERE clauses could be problematic (they aren't) or that you needed a key field (you don't).

How do you run this query? If you simply use the first button of the toolbar, you get a "view mode", which deactivates the actual action query. You need to use [Run] or (Query | Run), or double-click the saved query.

(°v°)
0
 
ellenjbrAuthor Commented:
I am thinking that it was multiple issues.  Today, I changed from a 2000 to a 2002 format.  The primary table that I had issues with had been saved as a subdatasheet ... I got rid of that setting when I transfered the table into a new .mdb.  I think that these changes help to assist success today; though that does not account for my test tables yesterday which I had no issue with today - after re-creating them on a different PC.

Not truely knowing MS Access leaves me at a disadvantage with regard to its' quirks.  Thank you.
0
 
harfangCommented:
Welcome and good luck! -- (°v°)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now