Table Loop and New Column in the same Table

[orchid67]

Table 1
ID Title Object 1
1 Book 1 Home Alone
2 Book 2 Forest Gump
3 Book 3 James Bond

Goal is to loop thru the table and use an if statement to rename the Object 1 and create a field (New) on the table.

If Book 1 is Like “*Home”, then Children’s book
Book 2 is like “*Forest” , then Horror book

Final Output

Table 1

ID Title Object 1 New Field
1 Book1 Home Alone Children’s book
2 Book2 Forest Gump Horror book

[PhilOfWalton]

I think you're on a hiding to nothing.
How many Key words are you going to search for (probably not many fewer than the number of book titles)?
How are you going to deal with a book title like "My home in the forest"?

I know it's not what you want to hear, but I would create a table of book types,

TblBookTypes
   BookTypeID      Autonumber
   BookType        No Duplicates
 

Include BookTypeID in your book table, and have a combo box to manually assign the book type to each book.

Phil

[nico5038]

I concur with Phil, typing Titles automatically will never be 100% accurate.
You could use an update statement to set the Type field initially, but the downside is that overlooking a wrong type between hundreds of entries will be harder as typing them manually. Thus many applications use a table as described by Phil and let the user select the type. Don't forget to add a type "Other" or allow new types to be added.

Nic;o)

[PhilOfWalton]

Actually, thinking further, the more titles there are, the more keywords are needed to prevent the type being "Other".

The more keywords there are, the more often there are going to be 2 or 3 or more keywords in a title.

Next problem is how do you create the keyword table? Only by manually reading the titles and manually selecting the keyword and adding it to the table.

Don't get me wrong, what you want to do is very easy but the results will be so inaccurate that it's not worth the effort.

In the example of "My home in the Forest", we could easily set that to Children's book as it found the word "Home"

If the title was "The Forest's my home", then the classification would be horror.

Phil

[zmbd]

I concur...
also there is the issue of proper normalization to consider.