Having hard time with database structure

[Tandy]

Hi! I just started a new database called Benefits. It is going to be used to
calculate the rates of employees and their families paid by the company. I
was basically given a format for a report and asked to make it possible. For
the last couple of days I have been trying and trying to structure the tables
and queries to make my report possible, but I am no closer now than when I
started. I know it is possible, but don't seem to have the skills to do it
myself. Help would be greatly appreciated! Here is what I need...

1. The report needs to be divided into 2 sections: Employee and Provider.

2. Each section needs to list out the Employees or Providers and each of
their family members being covered, indicate what they are (Employee,
Provider, Spouse, First Child, Second Child, etc.) and their birthdate.

Here is where is gets tricky...
3. Each Employee/Provider and family member has different premiums. Here
they are:
Employee/Provider: $324
Spouse: $340
First Child: $129.5
Second Child: $178
2+ Children: 0

4. The company pays different percents on the premiums for Employee and
Provider's families. Here they are:
Employee/Provider Percent: 100%

Provider's Spouse: 100%
Provider's First Child: 100%
Provider's Second Child and on: 0%

Employee's Spouse: 0%
Employee's First Child: 0%
Employee's Second Child and on: 0%

5. These premiums and rates will change so I want a Date Premium Assigned
and Date Premium Percent Assigned.

 

[John Vinson]

Hi! I just started a new database called Benefits. It is going to be used to
calculate the rates of employees and their families paid by the company. I
was basically given a format for a report and asked to make it possible. For
the last couple of days I have been trying and trying to structure the tables
and queries to make my report possible, but I am no closer now than when I
started. I know it is possible, but don't seem to have the skills to do it
myself. Help would be greatly appreciated! Here is what I need...

Stop... step back and reconsider your approach.

Designing a table structure to fit a report is almost CERTAINLY going
to give you a non-normalized, overly convoluted design.

Go the other way - use the report *only* as an information source
identifying the "entities" and fields (attributes) which pertain to
them. Once you have a logical set of tables, then you can design a
report to fit the tables, and present the data that you need in the
format you want it.

The Entities that I can see in your application might include:

Employees
FamilyMembers
Providers
Benefits
Premiums

The Premiums table would be related both to Providers and to
FamilyMembers so that each person can have a customized premium.

John W. Vinson[MVP]

 

[Don]

Tandy,

A relational database as John indicated is not one table where you can store
all of your data like a spreadsheet. Each table must contain data that
belongs only to that table and has a column that relates to columns in other
data tables with data that belong only to their tables. These tables are
joined to each other by primary keys and foriegn keys(more on this later).
You would not put the percentage of cost and family information in the
employee table. By doing so, this makes the table as John says of a
"non-normalized, overly convoluted design"

Examples of Tables

Some of the columns in an Employee table would might be First Name, Last
Name, EmployeeID, Title, Phone Number, Department and Health Plan ID. In this
Table the Primary key may be the EmployeeID. This column can contain only
unique data for each employee record (primary key is unique number or text
for each record). The column Health Plan ID would be a foreign key in the
Employee table. This foreign key in the Employee table would be a primary key
in the HealthPlans table. In a table you can repeat Foreign keys a column in
many records but you may only have one unique primary key in a column.

As John said you would want a family table. In the family table you would
want columns with family data which may have columns like First Name, Last
Name, Relationship Type, Relationship, Age or Date Of Birth and EmployeeID.
EmployeeID would also be a foreign key (many) in the family table and would
be joined to the matching primary key (one) in the employee table thus
relating the records in the employee table with the same primary key.
Example, employeeID "58" relates to records in the family table with matching
foreign keys of "58". This is known as a one to many relationship where
there is (one employee) Jim Bob primary key "58" in the employee table and
(many family members) Betty, Bobby and Beth that have foreign keys of "58" in
the family table.

Relational database tables can relate one to many, one to one and in some
unique cases many to many. I explain all of this to help you understand that
your task is indeed doable but you may want to get a book to help you
understand more about how these relational databases work. One great book is
Microsoft Access 2000/Visual Basic for Applications Fundamentals -- by Evan
Callahan; Paperback you can get it at amazon.com for the following prices.
Buy new: $26.39 -- Used & new from: $12.95 I bought mine used and I use it
all the time. I spent about 15 hours going through it and it continues to
help me. I tell you this to encourage not discourage you. You should continue
to post your questions and these generous and knowledgable people like John
Vinson and Allen Browne (great resources) will respond and help you solve
your problems. Also one other tip is make your tables and columns one word
abbreviations like Employeetbl for Employee table and EmployeeID not Employee
Number. Try not to use this type of column name Employee# as this sometimes
makes for problems in some expressions or code. Hope this helps.

fellow access hack,

Dennis