On Tue, 15 Sep 2009 14:34:05 -0700, oldblindpew wrote:

Gina and Fred,
Yes, I hear you. Naming is a problem because structure is a problem. With
the spreadsheet approach, I knew I needed a Requirements table, a
Certificates table, and a Validation table. Once I departed from this and
tried to normalize, it led to confusion about what my entities were and
therefore what they should be named. It doesn't help that there seems to be
a lack of good candidates for names.

I think that insurance elements, or entities or "things", outside the
context of any specific Agreement or Certificate, should be called Insurance
Parameters, and should go in a table of Insurance Parameters with one record
for each parameter.

Joining a Parameter with an Agreement and adding a "Required Value" field
gives you an Insurance Requirement, so the first impluse was to call the join
table an Insurance Requirements table.

However, adding a "Provided Value" field makes this same join table begin to
look like a collection of Certificate of Insurance items, with built-in
references back to the Required Value imposed by the Agreement. So my second
impulse was to call the join table a Certificates table. This was also
somewhat motivated by difficulty in getting people to understand what I meant
by "Requirements" as an entity.

More recently I have been thinking of it as a "Validation" table, because
each record ultimately brings together a Requirement and a Certificate
offering, to see if they agree.

-Pew

"Gina Whipp" wrote:

Just peeking in...

Thank you Fred!

Peanut Gallery... Certificates = Requirements??? Using the same term is
VERY important. Could be one of the reasons we are all getting confused and
have to keep asking more questions.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Fred" wrote in message
...
Hello Pew,

A couple of notes - this is a complex application with a lot a good ideas
in
a lot of threads. I really am not able to spend the time to absorb them
all.

Gina is an overall Access Goddess. You can't go wrong by listening to
her.
Just make sure you communicate cleqrly by defining and consistently using
your you-specific terms.

My strength is structure, and heavy use of Access in things that I run
(companies) or rund data for (organizations). I'm not a developer.

First, there's one area of confusion.

Now you said: joins these together. You suggested calling it
"Requirements"; I called it "Certificates".

This conflicts with what I think that you said previously (and which I was
going by) which was the each agreement/subcontract has ONE certificate
and
many requirements.

In my method, you only combined coverage into the same requirement record
when the TYPE matched. Otherwise they are seperate until reconciled or
combined. Dates of coverage can be added fields in both....just use
consistend definitions.

Again, I think that in this case going by the book (normalizing) is the
best
way to serve YOU and YOUR NEEDS. Nothing to do with making it easy for
Access or being a purist.



This thread has been going on a long time, so I thought I would add my 2 cents worth:

CREATE TABLE Firms (
firm_no LONG NOT NULL,
firm_name TEXT(50) NOT NULL,
PRIMARY KEY (firm_no)
);
CREATE TABLE Agreements (
agreement_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
agreement_date DATETIME NOT NULL,
start_by_date DATETIME NOT NULL,
finish_by_date DATETIME NOT NULL,
PRIMARY KEY (agreement_no)
);
CREATE TABLE [Insurance Options Master List] (
option_no LONG NOT NULL,
option_name TEXT(50) NOT NULL,
PRIMARY KEY (option_no)
);
CREATE TABLE [Agreement Options] (
agreement_no LONG NOT NULL
REFERENCES Agreements (agreement_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (agreement_no, option_no)
);
CREATE TABLE [Insurance Policies] (
policy_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
issuer_name TEXT(50) NOT NULL,
effective_date DATETIME,
termination_date DATETIME,
PRIMARY KEY (policy_no, firm_no)
);
CREATE TABLE [Policy Options] (
policy_no LONG NULL,
firm_no LONG NOT NULL,
FOREIGN KEY (policy_no, firm_no)
REFERENCES [Insurance Policies] (policy_no, firm_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (policy_no, firm_no, option_no)
);