Help Normalize My Database

[bhipwell via AccessMonster.com]

Hello,

I have developed an employee benefits database that currently contains over
3000 employees and 70+ companies. Having started the database as a really
simple solution for our clients, the last six months have demanded more
complexity and thus much of the recent work has been patch work to say the
least. I finally hit the "too many fields defined error" and now is the time
to go back and rebuild part of the database resulting from the last few
months of patchwork. The first two tables are simple:

1. Employee Info (name, address, gender, etc.)
2. Company Info (name, address, contact info, etc.)

From here, I don't have the best solution on where to go. Here is where I a
struggling:

- An employee can be categorized in to one of up to 7 classes.
- Some employers only have one class of employee, others 2, some 3, and so on
and so forth
- The employer will pay different amounts of health insurance premiums
depending on what class the employee is (some classes are paid 100%, others
50%, etc.)
- An employer may offer up to 4 different health insurance plans, each with
their own rates
- Each employer has different payroll schedules (weekly, bi-monthly, etc.)
- Each health plan has four premium elections (employee only, employee /
spouse, employee / children and family)

Basically, I need to set up my tables so I can run a query to determine what
the particular employee's payroll deduction is. So the logic is this:

- What company does the employee belong to?
- What is the company's payroll frequency?
- How many classes does the employer have?
- If more than one class, what class is the employee?
- Of the four premium elections, what election did the employee choose?
- How many health plans does the employer offer?
- If more than one health plan, what plan did the employer choose?
- What is the monthly premium for the election and plan the employee choose?
And the end result we are seeking...
- From the information above, what is the employee's deduction per payroll?

Mind you, this setup will be duplicated to handle dental, life, disability,
etc. products as well. Right now, I have query's (and forms/reports) doing
some pretty heafty formula calculations to determine the answer to the final
question. This alone is a red flag that the database queries are limited
because of a non-normalized database.

I am looking for some thoughts and feedback on how others like you would go
about building such a database. Thanks in advance!

B

--
Message posted via http://www.accessmonster.com

[lyle fairfield]

My thought is that you are in way over your head and should buy pre-
existing software (if there is any) or hire a professional developer.

On Aug 2, 10:41*pm, "bhipwell via AccessMonster.com" <u30281@uwe>
wrote:

Hello,

I have developed an employee benefits database that currently contains over
3000 employees and 70+ companies. *Having started the database as a really
simple solution for our clients, the last six months have demanded more
complexity and thus much of the recent work has been patch work to say the
least. *I finally hit the "too many fields defined error" and now is the time
to go back and rebuild part of the database resulting from the last few
months of patchwork. *The first two tables are simple:

1. *Employee Info (name, address, gender, etc.)
2. *Company Info (name, address, contact info, etc.)

From here, I don't have the best solution on where to go. *Here is where I a
struggling:

- An employee can be categorized in to one of up to 7 classes.
- Some employers only have one class of employee, others 2, some 3, and so on
and so forth
- The employer will pay different amounts of health insurance premiums
depending on what class the employee is (some classes are paid 100%, others
50%, etc.)
- An employer may offer up to 4 different health insurance plans, each with
their own rates
- Each employer has different payroll schedules (weekly, bi-monthly, etc.)
- Each health plan has four premium elections (employee only, employee /
spouse, employee / children and family)

Basically, I need to set up my tables so I can run a query to determine what
the particular employee's payroll deduction is. *So the logic is this:

- What company does the employee belong to?
- What is the company's payroll frequency?
- How many classes does the employer have?
- If more than one class, what class is the employee?
- Of the four premium elections, what election did the employee choose?
- How many health plans does the employer offer?
- If more than one health plan, what plan did the employer choose?
- What is the monthly premium for the election and plan the employee choose?
And the end result we are seeking...
- From the information above, what is the employee's deduction per payroll?

Mind you, this setup will be duplicated to handle dental, life, disability,
etc. products as well. *Right now, I have query's (and forms/reports) doing
some pretty heafty formula calculations to determine the answer to the final
question. *This alone is a red flag that the database queries are limited
because of a non-normalized database. *

I am looking for some thoughts and feedback on how others like you would go
about building such a database. *Thanks in advance!

B

--
Message posted viahttp://www.accessmonster.com

[Allen Browne]

Here's a starting point for the tables you will need. It's not the whole
story (clearly we can't take you there), but hopefully it will get you on
the track.

tblPeriod
- PeriodID PK
- PeriodFreq Number
- PeriodType "d" or "m"
(Use the 2 fields together for pay period, e.g. every 14 days or every 1
month. The PeriodType entries are valid intervals for DateAdd().)

tblCompany (employers):
- CompanyID PK
- PeriodID How often they pay.
- PayStart Date/Time
(Enter any paydate in the last field, and you can determine the company's
regular pay dates from then on.)

tblEmployee (people):
- EmployeeID PK

tblInsurer (benefit providers):
- InsurerID PK
(You might be able to include this in tblCompany.)

tblBenefitType (health, life, disability):
- BenefitTypeID PK

tblPlan (list of benefit plans available):
- PlanID PK
- InsurerID insurer

tblPlanDetail (benefits in each plan):
- PlanDetailID PK
- PlanID which plan this row belongs to.
- BenefitTypeID Type of benefit included
(+ other fields indicating level of benefit.)

tblCompanyPlan (which companies offer which plans to whom):
- CompanyID employer
- PlanID plan offered
- EmployeeClassID class of employee this plan is offered to
- Charge Currency

tblEmployeeClass (lookup for CompanyEmployee):

tblCompanyEmployee:
- CompanyID
- EmployeeID
- EmployeeClassID
- PlanID
- StartDate
- EndDate

tblPayDeduct (a record for each deduction each pay):
- CompanyID
- EmployeeID
- PayDate Date/Time
- PlanID
- Charge Currency
- BatchID identifies the batch that created this record.

tblBatch (one record for each time your code runs to insert records into
tblPayDeduct):
- BatchID

tblCount (a record for each number from 0 to thousands.)
- CountID Number primary key

The last table can be used to calculate the pay dates for companies. You add
it to a query with no join to any other table (a Cartesian product), and
then enter a calculated field like this:
DateAdd(tblPeriod.PeriodType, tblCount.CountID * tblPeriod.PeriodFreq,
tblCompany.PayStart)

With this in place, you can determine who is currently employed by whom and
on what plans (from tblCompanyEmployee), calculate the pay dates, get a new
batch number, and execute an Append query statement to insert the records
into tblPayDeduct. (The batch number provides a way to undo the last batch
if needed, and to debug which entries were created when.)

No doubt the tables you end up needing will be different, but hopefully you
can follow the logic of the suggestion and adapt it to your needs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bhipwell via AccessMonster.com" <u30281@uwewrote in message
news:8818c927c167b@uwe...

>
I have developed an employee benefits database that currently contains
over
3000 employees and 70+ companies. Having started the database as a really
simple solution for our clients, the last six months have demanded more
complexity and thus much of the recent work has been patch work to say the
least. I finally hit the "too many fields defined error" and now is the
time
to go back and rebuild part of the database resulting from the last few
months of patchwork. The first two tables are simple:

1. Employee Info (name, address, gender, etc.)
2. Company Info (name, address, contact info, etc.)

From here, I don't have the best solution on where to go. Here is where I
a
struggling:

- An employee can be categorized in to one of up to 7 classes.
- Some employers only have one class of employee, others 2, some 3, and so
on
and so forth
- The employer will pay different amounts of health insurance premiums
depending on what class the employee is (some classes are paid 100%,
others
50%, etc.)
- An employer may offer up to 4 different health insurance plans, each
with
their own rates
- Each employer has different payroll schedules (weekly, bi-monthly, etc.)
- Each health plan has four premium elections (employee only, employee /
spouse, employee / children and family)

Basically, I need to set up my tables so I can run a query to determine
what
the particular employee's payroll deduction is. So the logic is this:

- What company does the employee belong to?
- What is the company's payroll frequency?
- How many classes does the employer have?
- If more than one class, what class is the employee?
- Of the four premium elections, what election did the employee choose?
- How many health plans does the employer offer?
- If more than one health plan, what plan did the employer choose?
- What is the monthly premium for the election and plan the employee
choose?
And the end result we are seeking...
- From the information above, what is the employee's deduction per
payroll?

Mind you, this setup will be duplicated to handle dental, life,
disability,
etc. products as well. Right now, I have query's (and forms/reports)
doing
some pretty heafty formula calculations to determine the answer to the
final
question. This alone is a red flag that the database queries are limited
because of a non-normalized database.

I am looking for some thoughts and feedback on how others like you would
go
about building such a database. Thanks in advance!

B

--
Message posted via http://www.accessmonster.com

[bhipwell via AccessMonster.com]

Thanks for your reply.

Trucking righ alone, I have run into a snag. I have a query that ultimately
provides me with the premium corralating to a set of variables. The simple
query asks the following on behalf of an employee:

What company is he with?
What plans does the company offer?
What plan did the employee choose?
How many classes does the company have?
What class is the employee?
What election of coverage did he choose (employee, employee and spouse, etc.)?
From the above, each employee gets a monthly premium kicked out. The
information for a particular employee may be similar too:

John Smith, ABC Company, BlueCross Plan A, Class 1, ES Election (employee and
spouse)

Where I am stuck is trying to calulate the premium the employee is
responsible for. In this scenario, the ES premium is $385 a month. The
premium for employee only is $205.

If the employer pays 100% for employee only and 50% of the spousal premium,
the monthly premium would be $300 a month ($205 + .5($385-$205)).

I am staring at my query crosseyed. What it seems I need to do is someone be
able to pull the employee premium out to work my calculation. But since the
employee choose ES, I don't know how to do this.

Thoughts?

B

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

[Allen Browne]

I'm not clear if you tried the structure I suggested?

If so, you have another variable, which is who pays what percent. That would
need an extra field in tblCompanyEmployee, to handle percentage (or perhaps
a related table to handle the 'many'-payee-for-one-plan-chosen scenario.)

If your query asks the questions you listed *each* time it's run, and you
are relying on the user to provide the correct answers each time (instead of
storing this info in the database and looking it up), I don't think it's
worth persuing the direction you are taking. The amount of time it will take
to enter the values for 3k employees in 70 companies, and the guarantee that
users will make mistakes, and the unverifiability/untracability of the
results you are storing will make it a financial disaster for you. Lyle's
suggestion of buying existing software would make better sense.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"bhipwell via AccessMonster.com" <u30281@uwewrote in message
news:8822a2b927b55@uwe...

Thanks for your reply.

Trucking righ alone, I have run into a snag. I have a query that
ultimately
provides me with the premium corralating to a set of variables. The
simple
query asks the following on behalf of an employee:

What company is he with?
What plans does the company offer?
What plan did the employee choose?
How many classes does the company have?
What class is the employee?
What election of coverage did he choose (employee, employee and spouse,
etc.)?
From the above, each employee gets a monthly premium kicked out. The
information for a particular employee may be similar too:

John Smith, ABC Company, BlueCross Plan A, Class 1, ES Election (employee
and
spouse)

Where I am stuck is trying to calulate the premium the employee is
responsible for. In this scenario, the ES premium is $385 a month. The
premium for employee only is $205.

If the employer pays 100% for employee only and 50% of the spousal
premium,
the monthly premium would be $300 a month ($205 + .5($385-$205)).

I am staring at my query crosseyed. What it seems I need to do is someone
be
able to pull the employee premium out to work my calculation. But since
the
employee choose ES, I don't know how to do this.

[lyle fairfield]

On Aug 3, 5:29*pm, "bhipwell via AccessMonster.com" <u30281@uwe>
wrote:

Thoughts?

When I read your posts I can't get beyond an image of a pale horse.

[bhipwell via AccessMonster.com]

The structure is just fine. Just needed to step away. The issue is the
necessity to access a variable in a table that may not be linked to the
employee. Let me explain. Rates for a health plan are:

EE - $100
ES - $225
EC - $195
ESC - $305

If the employee selects ESC, then his premium is $305. However, to calculate
what the employee is responsible, I will need to be able to pull the value of
EE as well.

Basically, the employee may be responsible for 25% of the EE rate and 75% of
the family portion of the ESC rate. Therefore, the formula would be ($100*25%
)+(($305-$100)*75%) = $178.75.

I do not know how to reference the EE rate while maintaining the tables to
accomodate all clients, rates, plans, etc.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

[Allen Browne]

"bhipwell via AccessMonster.com" <u30281@uwewrote in message
news:882a080ec36f1@uwe...

I do not know how to reference the EE rate while maintaining the tables to
accomodate all clients, rates, plans, etc.

Sorry: I can't help. I don't how how your tables are set up, so I can't
advise on how to access them.

For me, it's time to move on to help someone else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.