IIF statment?

[CThai]

Hello -

I'm not sure how to go about doing this --- I have a table and in the table i have a field name called "Type of useage" and there are 10 different type of usage (NNBC, OBD, IIS, IBSD,ORGIS, OTHER, etc..) 4 of these usage are for 5yrs time and the rest are for 10yrs time - is there a way i can create a new field and say IFF NNBC, and OBD and IIS put it in 5yrs time... and the rest put it in 10yrs time fields?


thank you!

 

[Phildaburn]

In your table, go to the design view and add the new field. Save and close the table. Now create a new query. Start off with it being a simple select query using the table. Include the "Type of useage" field and your new field and in a blank column, type this:

ValueTest: iif([Type of useage] in ('NNBC','OBD','IIS'), 5,10)

Run the query and if you're happy with the results, change it to an update query putting the iif statement in the Update To line of the update query. Run this update query and your table will be updated.

Note: you'll need to put the exact 4 values you're wanting to repesent as 5 years.

Hope this helps...
Phil...

 

[Joe4]

Run the query and if you're happy with the results, change it to an update query putting the iif statement in the Update To line of the update query. Run this update query and your table will be updated.

Usually, it is not necessary to do this, and doing so can actually violate the rules of data normalization.

Typically, a field that is dependent upon another field (i.e. is calculated or can be determined by the value in another field) should NOT be stored in a table. It should be determined in the query, and left at that. There usually isn't a need to write it to a table (unless you are creating some sort of historical table).

If it can be determined in the query, there is usually no need to write it back to the table. Queries can be used as the sources of Forms, Reports, Exports, etc. The danger in writing it back to the table is that you can undermine the data intergrity and dynamic nature of the table and database.

For example, what happens if you write it back to the table, but then somewhere down the road, someone changes the "usage" field on one of the records, but doesn't update the corresponding calculated field? Now you have bad data in your system. If left as a query calculation, the data changes will automatically be reflected in the calculated value too.

 

[Joe4]

For data normalization rules, check out this post here: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
This particular rule falls under the section named "Third Normal Form".