CODEWITHSUNDEEP
sqlms-access
Rashu
Rashu

Reputation: 21

MS Access : Filling column values conditionally

I have read a few Q&A's here about filling in condition-based values in Access using Switch and Iff statement. I am relatively new to Access, so a little help would be much appreciated.

I have 2 tables, Owner and Pet.

Table Owner has following fields: 

OwnerID , OwnerName , PetType , PetCommonName , OwnsAGarden

Table Pet has following fields:

Species , CommonName , NeedsAGarden

Table Pet maps the scientific names of pet animals to the generic, commonly known ones. Table Owner has values for OwnerID, OwnerName, PetCommonName and OwnsAGarden existing in the table. Column PetType needs to be updated based on some condition from Table Pet.

I want something like:

if Pet.CommonName = 'Dog' AND Pet.NeedsAGarden = 'Yes', then Owner.PetType = 'Outgoing dog', else if Pet.CommonName = 'Dog' AND Pet.NeedsAGarden = 'No', then Owner.PetType = 'Indoor dog', else if Pet.CommonName = Owner.PetCommonName, then Owner.PetType = corresponding vale from Pet.Species.

I have tried using the switch statement. the first two conditions get evaluated and the table is getting updated perfectly. however, the third condition doesn't seem to give the desired results.

Upvotes: 1

Views: 1675

Answers (1)

Rashu
Rashu

Reputation: 21

After a lot of tinkering with alternatives, I have managed to solve my problem using the IIF statement..

I changed the code to

Update Pet INNER JOIN Owner
ON Pet.CommonName = Owner.PetCommonName
SET Owner.PetType = IIF(Pet.PetCommonName = 'Dog', IIF(Pet.NeedsAGarden = 'Yes', 'Outgoing dog', 'Indoor Dog'), Pet.Species);

It is working.. Thanks guys for all the earlier posts to help me come up with this..

Upvotes: 1

Related Questions