Update query is asking for a Parameter Value

[BittenApple]

Hello team,
I have this update query:

Update Mytable INNER JOIN YourTable ON MyTable.Hos_ID=YourTable.Hos_ID
SET Mytable.Activity_Name =YourTable.Name
WHERE MyTable.Activity_Name Is NoT Like "this String";

This one gives Error: Syntax Error

Or

Update Mytable INNER JOIN YourTable ON MyTable.Hos_ID=YourTable.Hos_ID
SET Mytable.Activity_Name =YourTable.Name
WHERE MyTable.Activity_Name <> "this String"; Enter Parameter values.

I have been working on this since morning; I have run so many update queries and they have gone well, but this one gives trouble.
Activity_Name field in the table has a caption as Name.

Any help is greatly appreciated!

Regards,
BitBit

[HansV]

In the first place, you should use

Not Like

instead of

Is Not Like

Without wildcard character * or ? in your string, Not Like and <> are equivalent.

Which field does the parameter prompt ask for?

[BittenApple]

Hans,
I resolved that one, literally, I have been working on it since morning and I did the same thing over and over and it didn't work, eventually it went through.

Now I have a table as this it has two rows only :

table_ID	Name	myField	yourfield	Status
1	average	1	34	distributed
2	average	22	43	notdistributed

------------------------------------
the second table is has many rows

ID	hosId	hosName	myfield	yourfield	Status
1	1	hosA	33	35	Distributed
2	1	Average
3	2	hosB			NotDistributed
4	2	Average

==============================
1-I want to update the second table for hosName when hosName has average; If Status for hosA has distributed then Average needs to be filled with distributed; if hosName has NotDistributed, Status field for Average record should be filled with NonDistributed.

2-I want to fill out the Average record with the values of first table for MyField and Your field only.

My query updates only 0 records.
I joined the first table and last table by the hosName and in the Where clause, I put where hosName="Average"

Regards,
Bitbit

[HansV]

Can you show what the result should be in the above example?

[BittenApple]

Hans,
With the first query, I will fill out status for Average record based on the values of previous record on Status field. Having said that if Status on hos A records is filled with the word Distributed, the next record which has Average has to have the Status filled with the word distributed. Each record on Status field follows the previous record values on status field. Does this make sense?

On the second query: I want to join these two tables on hosName and Status to be able to update the values for myField and yourField on the destination table. Average line gets the values from the table which has two rows: if it is distributed, the myfield and yourfield has the values from the first table and first record and if it is undistributed, the destination table gets its values from second record of the first table.

Hope this make sense.

This is what I have which is not correct
update second table
Set secondtable.Status="Distributed"
WHERE Secondtable.Status is Null and secondtable.hosName="Average"

Maybe, status and average are not good fields for joining.

This fills out everything.
Regards,
BitBit

[HansV]

Try this as the first query:

UPDATE [Table2] AS A INNER JOIN [Table2] AS B ON A.hosId = B.hosId SET A.Status = .[Status]
WHERE (((A.hosName)="Average") AND ((B.hosName)<>"Average"));

where Table2 is the name of the second table.

[HansV]

The second query would be

UPDATE [Table1] AS A INNER JOIN [Table2] AS B ON (B.Status = A.Status) AND (A.Name = B.hosName) SET B.myfield = [A].[myField], B.yourfield = [A].[yourField];

where Table1 is the name of the first table, and Table2 is the name of the second table.

[BittenApple]

Hans,

I did the first query and I created the second one before I saw your second query. They both went well, but I came to do something or I thought my second update didn't go well. I deleted all the records. I redid everything until some certain point, I am going to attend to the second update query later. I am sure if I do something right now, everything will be ruined.
Thanks for all!

BitBit

[BittenApple]

Hello Hans,
These queries did so much for me and it was a real solution. Thanks for all!
BitBit