Adding iif or maybe a lookup table to db

[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

 

[GrahamT]

Hi Profector.

Firstly, one question - is the Total dollars ("Insurance $") a fixed amount or might it change with time? If it changes, might you want to keep a history of it? (OK, 2 questions).

GrahamT

 

[Pat Hartman]

1. Do not use SSN as the primary key. Use an autonumber and make a unique index for SSN. Using SSN as a pk leads to a number of problems which have been covered here several times.
2. It is poor practice to use special characters and embedded spaces in table and column names.
3. Instead of using yes/no fields, use currency fields and store the individual amounts. You can add yes/no fields to your form that add/remove values from the currency fields.
4. It is not necessary to store the sum of the amounts since they can be summed in a query.

 

[Profector]

Hi Profector.

Firstly, one question - is the Total dollars ("Insurance $") a fixed amount or might it change with time? If it changes, might you want to keep a history of it? (OK, 2 questions).

GrahamT

The cost will probaly change twice a year. Yes I would like to keep a history of it. What I do now is work with a summary of premium costs, or the plan costs. It is a pain to gather historic data and chart it so I haven't hold much with in other than on pen and paper once a year. I havn't advanced my Access skills enough to do this.

 

[Profector]

1. Do not use SSN as the primary key. Use an autonumber and make a unique index for SSN. Using SSN as a pk leads to a number of problems which have been covered here several times.
2. It is poor practice to use special characters and embedded spaces in table and column names.
3. Instead of using yes/no fields, use currency fields and store the individual amounts. You can add yes/no fields to your form that add/remove values from the currency fields.
4. It is not necessary to store the sum of the amounts since they can be summed in a query.

1. I'll look up and see what the problem were people ran into on SSNs and then look into what I can do to switch the primary key. The reason I choose the SSN as the primary keys was to make sure there would not be people entered twice.

2. I did run into a few concerns with that myself.

3. I get what your saying here, I think. If "this box" is check it stores a dollar amount instead of a yes/no.

4. Can the query drop the total back into into the form?

 

[Pat Hartman]

In the query you can add fields together:

Select ..., Nz(fld1,0) + Nz(fld2,0) + Nz(fld3,0) As SumAmt, ....

I used the Nz() function since it is possible for some fields to be null.

 

[GrahamT]

The cost will probaly change twice a year. Yes I would like to keep a history of it. What I do now is work with a summary of premium costs, or the plan costs. It is a pain to gather historic data and chart it so I haven't hold much with in other than on pen and paper once a year. I havn't advanced my Access skills enough to do this.

To track the history of Insurance Costs, I suggest you create another table say, tblCostHistory, which contains fields for:
A Primary Key / ID (as Autonumber)
Insurance$
DateOfInsurance
SS# (Long Integer, linked to SS# in present table with a 1-M relationship)

& remove Insurance$ from you present table.
(I've assumed you're now using Automumber for what was SS#. If you've changed SS# to something else, use that name instead.)

When you have sorted out your tables, created your forms and are running the database with live data, you will still need to have a function to compute the Insurance$ total - presumably by clicking a button to tell the database to use the Yes/No fields etc. in your main table, along with a table of current costs.

You will need this same function now in order to compute the Insurance$ totals that you are requiring. So, after refining your table structure, I suggest that is where you concentrate your efforts (which won't be wasted).

This function would be best done in code, rather than just queries, IMHO, and would be far more flexible.