Query update issue

[puforee]

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:

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

The new version:

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,

 

[Andrzejek]

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.

 

[puforee]

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

 

[puforee]

I still am looking for a solution...please

John

 

[puforee]

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.

 

[Andrzejek]

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

[pre]
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])
[/pre]
(If 'inner' select returns only one record per ID)


---- Andy

There is a great need for a sarcasm font.