Adding iif to query or table

[Profector]

Hello folks first post here. I tried reading through the boards to find someone with a similiar question, but not luck yet. I have been working with a basics of Access for about a year. I did take take a bit of programming in college but I don't remember much of it, so I looking for a little help. I can usually Google for answers but I cannot seem to find the write key words this time.

A while back I created a table that tracks employees' data and recently I started working with the insurance data I have gathering for sometime.

It goes something like

Field Name Data Type

SS# Number (primary key)
Insurance $ Number
PPO YES/NO
HMO YES/NO
Self YES/NO
Spouse YESNO
Children YES/NO
Family YES/NO
Dental Self YES/NO
Dental Spouse.......etc.


What I'm trying to do is turn the yes/no's into total dollars for each person. I have done this in Excel but the fields were differnet and I had to use a many, many if.'s or a lookup table(data array)

PPO doesn't = a dollar amount by itself, but PPO + Self = $19.03 or PPO + Self + Dental Self = $23.00. Or the person can just have Dental Spouse = 8.86. Pretty much the people can take a piece and leave another.

"Insurance $" is another field name and is where I am trying to get the total to go.

I was thinking of a lookup table but I don't know how to do that in outside of Excel.

I am thankful for any help or even a nudge in the right direction.

~Profector

 

[GUIDO22]

The way that I would consider doing this would be to use the Form_AfterUpdate() event.

When the user has set the field values (presumably from a Form) - after the Save event fires - you could run VBA code in the AfterUpdate() event to retrieve a Recordset for the record entered and check the values of each of the parameters you have referred to calculating the Insurance$ total as you go - then simply update the Recordset with the total calculated.

If you do not know how to do this - post a 2000/2002 copy of your DB here and I will have a look at it for you.

 

[Profector]

View attachment New.zip

I attached a DB minus unrelated form, queries etc.. Thanks for taking a look.

 

[GUIDO22]

Try this - should give the idea.....

 

[Profector]

I think i see what you were doing with the subs. I'll see if I can recreate it tonight and post the outcome. Thank you very much.