The Requirement has been defined by the User as follows :-

I am in the employee benefits business.
I broker Health plans, savings plans, disability insurance, life insurance plans to companies that
purchase these plans for their employees.
I also provide plans to the private individuals and families.

I have a 'Zip Code Database', which has several fields that have all of the
Zip Codes, City, State, County and telephone area codes in the United States.
I use County information in my work for proposals.
County information is not always readily available and the data file helps a great deal.
I am also currently using the zip code information to pull up a clients City, State Zip Code
and it works pretty good.

My question was regarding the telephone area code which is part of the phone number.
In Access if I separate the area code from the rest of the phone number into two different
fields, I am concerned that I will not be able to use it to fax and or auto dial in the
future form the computer or export that to another data tool in the format with the data in one field.

I am thinking that I can combine the two fields (area code and phone number)
using a query. Is that correct?

The first draft model looks good, but it includes one thing regarding DIVISIONS (B.3) which
I believe I can handle in the employee census data with just one field (referring to
"B. The things of Interest").
Regarding Part C, would be that a client can have none or a number of Off-Site LOCATIONS.

I also have individual clients who are not companies, I keep track of
specific but different information for the members of the individuals family.
Spouse and individual children information includes (Name, DOB, Social Security #) the
individual clients would also be a CONTACT.
If you have any suggestions on this I would appreciate your advice.

So now I come to the beginning. If you consider that I have two types of Clients:
1. Employer or Corporate Clients and then the sub data files
2. Individual Clients plus a data file that has different sub data type files.

Both 1 & 2 sub sets all filtering down to a common CONTACTS file.
Where I am quite confused is what is the father data ?
I would assume it is the customer file data that assigns a CUSTOMER ID and
is based on a Date Field.

However I do annual contracts and renew these contracts at the same time each
year (that date can change for the same client (but not very often) and I can
have a number of Renewal Dates for each product sold (that ideally does not happen).

I do not record sales or any data on a daily basis but I have the same companies I
work with only one time a year or at the most two times a year (with the exception
of servicing the account).
So basing the CUSTOMER ID by date may not be the best answer, otherwise I will
be duplicating the same clients information. Is one option to base it off
of the policy month and year, then I can generate a history file from one
product renewal date month and year to the next?


So am I looking at one Customer ID and re-enter new client information from
year to year for the same client? or can I create a Customer ID and then
generate a Benefit renewal month / year?

Thanks for your help.

Allen

What is the best way to setup a database with corporate clients who have different off site locations and
both corporate contacts and division contacts ?

Not all companies have off site locations and I must keep track of the number on off site locations a
particular client has.

I also have a postal code database so that I don't have to keep records of all cities, Regions/States and
Districts/Counties in each contact (to speed up my data) and I don't have to spend time finding this
information when I do not have all of it.

In the US we have phone area codes, my postal code database has the area codes then the phone number.
I would like to be able to dial send a fax via the system, so having the area code and phone number may
create a problem down the road.

It is also nice for the system to tell me an area code with just the zip code information I have.

My goal has more to do with keeping things efficient.

I don't always need the off Site locations information, but I still want a record of it.

The address for corporate could be with the contact information, but I need to know which division the contact is
with and which company the off site division is with.

I hope I am being clear.

Thank you for your help!!!

Allen.

More details follow.



The Area being Modelled is :-
A Corporate and Individuals who purchase Benefits Plans, such as Health Plans, Savings Plans, etc...

A. DESIGN NOTES :
A.1 The Detail reflects the Requirements.

B. The Things of Interest, ('THINGS'), include :-
B.1 Clients,(both Companies and Individuals).
B.2 Companies
B.3 Contracts
B.4 Divisions
B.5 Employees
B.6 Families
B.7 Locations.
B.8 Products
B.9 Zip Codes

C. These THINGS are Related as follows :-
C.1 A COMPANY can have a number of On-Site and Off-Site LOCATIONS.
C.2 A CLIENT can be either an EMPLOYEE or a private INDIVIDUAL.
C.3 A FAMILY can have two or more FAMILY MEMBERS.
C.4 A CONTRACT is drawn up when a CLIENT purchases a PRODUCT.
C.5 A FAMILY can be associated with one or many CONTRACTS.

D. Other Characteristics of these THINGS include :-
D.1 A CLIENT can have one or many CONTACTS.
D.2 A CONTACT must be at one and only one LOCATION.
D.3 A LOCATION can be associated with zero, one or many CONTACTS.

E. Sample Data includes :-
E.1 To be determined

F. Typical Enquiries include :-
F.1 To be Determined