Problems with Update Query: Avoid changing data in source table

[fboehlandt]

Hello everyone,
I have a table within my database that denotes of whether or not a particular investment fund employs leverage. It is, however, not a YES/NO field. The datatype is text. The field values are:

- No
- Yes
- Yes(10%)
- Yes(50%)
- Yes(80%)
- Yes(FOREX)

I want to create a query where "Yes" is displayed for every entry that is not "No" (thus changing it to a YES/NO Field in that query). I don't want to change the data in the source table (thus I cannot use an Update query or statement), since I might need the more detailed information at a latter stage. Is their a way to 'update' the data for that query only?

 

[fboehlandt]

ps. without simply back-up saving the database

 

[khawar]

Use Sql Below

UPDATE [B]YourTable[/B] SET [B]YourTable.YourField[/B] = "Yes"
WHERE ((([B]YourTable.YourField[/B])<>"No"));

 

[MSAccessRookie]

Hello everyone,
I have a table within my database that denotes of whether or not a particular investment fund employs leverage. It is, however, not a YES/NO field. The datatype is text. The field values are:

- No
- Yes
- Yes(10%)
- Yes(50%)
- Yes(80%)
- Yes(FOREX)

I want to create a query where "Yes" is displayed for every entry that is not "No" (thus changing it to a YES/NO Field in that query). I don't want to change the data in the source table (thus I cannot use an Update query or statement), since I might need the more detailed information at a latter stage. Is their a way to 'update' the data for that query only?

Do you really need to save the value? You could calculate it each time with a query like "IIf(YourField = "No", YourField, "Yes")"

Example: SELECT IIf(YourField = "No", YourField, "Yes") from YourTable

 

[fboehlandt]

I didn't know you could use the IIf function nested in a SQL query. Including the IIf function in the select line does the trick. thanks MsAccessRookie

 

[MSAccessRookie]

I didn't know you could use the IIf function nested in a SQL query. Including the IIf function in the select line does the trick. thanks MsAccessRookie

My pleasure. I thought that might give you what you wanted.