Delete Field Value in a Tbl

[Mac1206]

I have a Table (A) which I need to delete a field value based off another filed in that table..Delete Field_Value = YES WHERE Field_Maryland = Toys Example:

Field_Maryland Field_Value
Toys Yes

So if my Field_Maryland = TOYS, DELETE Field_Value = YES...Thanks in Advance

 

[xenou]

Can you provide example data?
The description is confusing because the syntax is wrong and the names of the fields are very strange.

 

[Joe4]

I believe you are looking for an Update Query. But I am not quite clear what exactly you are trying to delete.

Is your criteria just that Field_Maryland is equal to "TOYS", or does the Field_Value must also be "YES"?
Then, which value are you trying to remove, the "TOYS" or the "YES"?

 

[Mac1206]

I believe you are looking for an Update Query. But I am not quite clear what exactly you are trying to delete.

Is your criteria just that Field_Maryland is equal to "TOYS", or does the Field_Value must also be "YES"?
Then, which value are you trying to remove, the "TOYS" or the "YES"?

Field_Maryland	Country	Field_Value
Toys	US	Yes
Toys	CA	Yes
Toys	MX	Yes
RESULT FOR ABOVE TBL
Field_Maryland	Country	Field_Value
Toys	US
Toys	CA
Toys	MX	Yes

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

Why is the "Yes" removed from the first two records but not the third?

 

[Mac1206]

Those are the results I'm trying to reach, I need to delete all Toys Field_Values that equal YES that are from US and CA....Thanks

 

[Joe4]

I need to delete all Toys Field_Values that equal YES that are from US and CA

OK. That second part is criteria you did not mention until just now. You want to make sure that you post a clear, complete question so we can see exactly what you are after. Otherwise, the answer you get might not work for and do exactly what you are looking for (since we would be working off of incomplete information).

It sounds like you just need a simple Update Query, where you add the three fields shown, using Criteria of "TOYS" for Field_Maryland and In ("US","CA") for Country, and Update the Field_Value field to Null.

The SQL code will look something like the following (though you should also be able to build this pretty easily using the Query Builder):

UPDATE TableA SET TableA.Field_Value = Null
WHERE (TableA.Field_Maryland="TOYS") AND (TableA.Country In ("US","CA"));

 

[Mac1206]

OK. That second part is criteria you did not mention until just now. You want to make sure that you post a clear, complete question so we can see exactly what you are after. Otherwise, the answer you get might not work for and do exactly what you are looking for (since we would be working off of incomplete information).

It sounds like you just need a simple Update Query, where you add the three fields shown, using Criteria of "TOYS" for Field_Maryland and In ("US","CA") for Country, and Update the Field_Value field to Null.

The SQL code will look something like the following (though you should also be able to build this pretty easily using the Query Builder):

UPDATE TableA SET TableA.Field_Value = Null
WHERE (TableA.Field_Maryland="TOYS") AND (TableA.Country In ("US","CA"));

Thank you, I'm still learning Joe but I greatly appreciate your teams patience with me...Exactly what I needed...

 

[Joe4]

No problem! Glad I was able to help!

With action queries (like Update, Append, Delete), I find the easiest way to do it is to first build a simple Select Query that returns all the records you want to take the action to.
Once you have that, then just change the query type, make the change to the appropriate field.

 

[Mac1206]

No problem! Glad I was able to help!

With action queries (like Update, Append, Delete), I find the easiest way to do it is to first build a simple Select Query that returns all the records you want to take the action to.
Once you have that, then just change the query type, make the change to the appropriate field.

Roger That...Thanks again Joe....