×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Query update issue

Query update issue

Query update issue

(OP)
I am running an update query. I had help with another one recently and it worked. This one is a bit different and I tried to mimic the earlier versions.

Earlier working version:

CODE -->

UPDATE [IPSS Classes] SET [IPSS Classes].Complete = Yes
WHERE ((([IPSS Classes].IPSSClassID) In (SELECT [Related Entity ID] FROM Breakout_IPSS_End))); 

The new version:

CODE -->

UPDATE [IPSS_Complete_Tbl] SET [IPSS_Complete_Tbl].Engine = [Eng_Test_Distinct].Eng2
WHERE ((([IPSS_Complete_Tbl].[Related Enity ID]) In (SELECT [Related Entity ID] FROM Eng_Test_Distinct)));
; 

The difference being in the Set statement. When I run the code it returns "Enter Parameter Value? Eng_Test_Distinct!Eng2"

Can you help me with my, not so obvious to me, mistake.

Thanks,

RE: Query update issue

In my opinion, the problems with your Update statements originate from wrong data base design. Looks to me that you keep the same pieces of data in multiple places in different tables, and you try to keep them 'synchronized' somehow, so the set of information in one table matches some information in the other table. If I am right, this is a very bad approach - you will NEVER keep them in-sync.

Tell me I am wrong, please....


---- Andy

There is a great need for a sarcasm font.

RE: Query update issue

(OP)
I think you are incorrect in that assumption. I have been requested to add another field to the "...Complete" table and the new data has been selected and formatted into the other table. This will be a one time load and then will be discarded. I have added the new field to my import Append query so, in the future, all new information will contain the data for the new field.

I hope this helps.

Thanks,

John

RE: Query update issue

(OP)
I still am looking for a solution...please

John

RE: Query update issue

(OP)
OK. I found the solution. I was using an action query as the source of the update. Once I created a temp table from the action query and then use the table as the source everything worked well. I was able to update my target table and deleted the other update components. I now have the one table with the necessary data.

RE: Query update issue

I know you have solved your issue, but here is (untested) statement you may want to try:

UPDATE IPSS_Complete_Tbl
SET Engine = 
    (SELECT Eng2 
    FROM Eng_Test_Distinct, Eng_Test_Distinct
    WHERE IPSS_Complete_Tbl.[Related Enity ID] = Eng_Test_Distinct.[Related Entity ID])
 
(If 'inner' select returns only one record per ID)


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close