Avoiding One to One Tables

First to clarify, this is all about structure, not VBA. And, structure
is really about making something that works well for YOU, not making it
easier for Access.

If you are interested in my thread towards really solving your problem, this
will get tedious, but I'm game if you're game. If so......


Could you describe, as thoroughly as you can, and example that involves an
Agreement, Requirement, Certificate, and a validation. When onle involves
more than on of the other, mention that, but go into detail on only one of
the other. For example, if an agreement has/creates many requirements,
mention that, but just go into detail on one requirement.


Fred

Fred, I appreciate your willingness to look into this. I will try to
describe what I am doing.

We construct large civil projects, mainly highways. We issue numerous
contractual agreements (subcontracts, purchase orders, trucking agreements,
and addenda thereto). Tracking these documents through the process of
drafting, reviewing, and signing is the main purpose of our application, and
it has been working well for us. I am now trying to add the tracking of
Insurance Certificates.

There is one record in the Agreements Table for each agreement. Each record
contains foreign keys identifying the Firm and the Project, as well as data
fields such as agreement type, description, total dollar amount, etc.

Some of our agreements, (e.g. subcontracts), require the subcontracting firm
to carry insurance. We have a fairly well-standardized set of requirements,
or parameters, or specifications, or details, describing the insurance
coverage needed. Earlier in this thread I listed some examples of these
details.

Normally, our insurance requirements are similar for all our subcontracts,
but since it is possible for any particular requirement on any particular
subcontract to be waived or modified, it is vital that each subcontract have
its own set of insurance requirement values. Thus my initial solution was to
have a table of requirements with one record for each subcontract agreement
(one-to-one relationship), and a field for each parameter.

Subcontractors are required to furnish a Certificate of Insurance as proof
of coverage. If the devil is in the details, then getting a certificate that
complies with subcontract requirements is our hell. We want a relatively
unskilled person to be able to enter information from the Certificate into
our database so that we can programmatically check it against the previously
defined set of requirements. There will be only one Certificate per
subcontract. Thus my solution was to have a table of certificates with one
record for each Certificate (i.e. one record for each subcontract agreement)
and one field for each value we desire to capture from the certificate.

Once we have a Requirements record and a Certificate record stored in their
respective tables, we would need some sort of VBA procedure to pull up the
two records and compare appropriate fields to see whether the certificate is
valid. My thought was to have a third table to hold the results of this
validation check. There would be one record for each Certificate, and fields
as needed for each insurance parameter being checked.

All this looked pretty ugly to me, but seemed necessary to get the job done.
I did not think Access would be challenged by having these three tables with
long records and lots of fields. After all, computers are supposed to be
good at handling much larger stuff than this, I thought. My biggest worry
was and is writing the validation procedure. My experience so far with
Access and VBA is that it's easier done than said. Once you know how, it's
easy, but asking for directions and getting straightforward answers is tough.

Thanks,
Pew

"Fred" wrote:

First to clarify, this is all about structure, not VBA. And, structure
is really about making something that works well for YOU, not making it
easier for Access.

If you are interested in my thread towards really solving your problem, this
will get tedious, but I'm game if you're game. If so......


Could you describe, as thoroughly as you can, and example that involves an
Agreement, Requirement, Certificate, and a validation. When onle involves
more than on of the other, mention that, but go into detail on only one of
the other. For example, if an agreement has/creates many requirements,
mention that, but just go into detail on one requirement.


Fred

Hello Pew,

To start with, long story short, I'm going to go only by your 9/11/09 post
except possibly to glance back at the others for a few factoids where your
new posts brings up a quesiton or has missing info.

First, I'm going to ask a couple of questions and discuss them and then
temporarily presume an answer.

Question#1 Can you assume that the types coverage requirements that need
this type of databasing come from a standardized list (lets call it
ListOfCoverageTypes) That all other variable attributes (e.g. required dates
of coverage) are implied

Question #2 Can you assume that each coverage requirement that needs this
type of databasing can be defined by specifying one item from the
ListOfCoverageTypes plus ONE number (limits)?

Questions #3 Can you assume that the data entered can be split up the
certificate into individual instances of coverage, specifically the same as
Question #1 & #2? I.E. each entered as a type from the ListOfInsuranceTypes,
plus one number (limits).

I'll temporarily assume that the answers to all three are "yes". If not
true for #1 or #2, then it would be "back to the drawing board" regarding
structure. If not true for #3, then you will need a team of lawyers and
insurance experts :-) to do the split up and definition described in Q#3

If so, then here's my idea on a way to do it. Change/shorten the names as
desired.


Table: ListOfInsuranceTypes PK = InsuranceType_ID

Table of Agreements , PK = Agreement_ID. (a subcontract is an agreement)

Table: InsuranceRequirements:

FK = Agreement_ID. Also include an integer numerical field "RequiredLimit"
default value = 0
PK = InsuranceRequirement_ID
FK = RequiredInsuranceType_ID

Table: InsuranceCertificates.
PK: Certificate_ID
FK: Agreement_ID

Next we'll be talking about InstancesOfCoverageFurnished, as fufilled by the
Insurance certificates. Here we hit a fork in the road.

If, for every entry, they can immediately pair it with an insurance
requirement, then just add the following fields to your InsuranceRequirements
table:

- FK FurnishedInsuranceType_ID
- LimitProvided (integer)
- FK Certificate_ID

If you need to be able to record coverage furunished that has not been
paired with a requirement, then you can go the intellectually complex but
structurally simple route of still making the above additions to your
InsuranceRequirements table, but rename it
"InsuranceRequirementsAndOrInsuranceItemsProvi ded" (of course shorten that)

The above is normalized not because it fulfills a normalizaiton religion
commandment, and not because it makes it easire for Access or your computer.
It's normalized because I think that it will make it easirer for you.
Including, I think, that you will be able to use a simple query (not that
anticipated complex VBA) to test that a certificate fufills all insurance
requirements.

I just wrote the above, I didn't test it (for errors , but I think that the
concept is good for your requirements, provided that the temporary answer to
the above 3 questions were right.

Gina,
I'm sorry, I just now realized you had embedded your replies down in the
past entries.

Gina wrote:
***I understand this which is why you need a join table OR add FirmID to
tblAgreements?
Pew responds:
FirmID has always been a foreign key in my tblAgreements. I'm sorry if I
didn't make this clear. I'm not sure how we got into a discussion of the
relationship between Firms and Agreements, as I'm not aware of any question
or problem in this area.

Gina wrote:
tblAgreementsRequirements ***This is a join table, not meant to store
anything but the two tables it is joining.
Pew responds:
I was not aware of any reason why a join table could not also contain data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table, and
wouldn't that table also have to include a field for Quantity?

Gina wrote:
arInsuranceRequirementValue - ***REMOVE from here to
tblInsuranceRequirements
Pew responds:
To extend the Retailing example given above, what you are suggesting sounds
like marrying Quantity to Product. In my case, the "item" we are "buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary from
one "order" to the next. Now, I could redefine the "item" as "$1m GL per
Occurrence", thus combining quantity with description, but this would require
creating separate items for every conceivable dollar amount. This in itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if we
required $1m and the certificate provided $2m, we could easily tell that we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

Gina wrote:
arCertificateValue - ***If this is part of Insurance Requirements move to
that table.
Pew responds:
The CertificateValue is NOT an Insurance Requirement, but is what is being
OFFERED in order to meet the requirment. In the above example, we required a
$1m policy, but the certificate provided $2m worth of coverage. Again, in a
retailing context, this might be analogous to Quantity Ordered vs Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is that my
insurance items are not all measured in dollars. Some are dates, some are
yes/no, and some are multiple choice answers.

Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that there are
not going to be any clear answers and that I just need to work this out for
myself.

--Pew

"Gina Whipp" wrote:

OBP,

Answers in line...

--
Gina Whipp

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

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

"oldblindpew" wrote in message
...
Hello, Gina.

There is not a many-to-many join between Firms and Agreements. The
Agreement is a purchase agreement, and the Firm is the supplier. We can
have
many orders (purchase agreements) with one firm, but each order will have
only one supplier fulfilling it.

***I understand this which is why you need a join table OR add FirmID to
tblAgreements?


This is what I am thinking:

tblFirms
fFirmID (PK)
fFirmName
etc...

tblAgreements
aAgreementID (PK)
etc...

tblInsuranceRequirements
irInsuranceRequirementID (PK)
irInsuranceRequirementValue
etc...

tblAgreementsRequirements ***This is a join table, not meant to store
anything but the two tables it is joining.
arAgreementRequirementID (PK)
arAgreementID (FK)
arInsuranceRequirementID (FK)
arInsuranceRequirementValue - ***REMOVE from here to
tblInsuranceRequirements
arCertificateValue - ***If this is part of Insurance Requirements move to
that table.

In this model, the term "Requirement" may be too restrictive. The table
of
Insurance Requirements is actually a table of Insurance Parameters. One
of
those parameters would be the Limit of Liability per Each Occurrence for
the
General Liability Policy. The join table would tell us that for a
particular
agreement, this particular parameter has a required value of, say, $1m,
and
the value supplied on the Certificate of Insurance is, say, $2m, so this
would be acceptable.

The problem is that my Insurance Parameters do not all have the same kind
of
values. Some are Yes/No, Text, Date, Currency, or what have you. I don't
see how one field in the join table can hold all those different types of
values.

That would be different fields that store different values. Each parameter
would be in a seperate field. Are there not a 'set' of parameters?


Thanks for your help.
--Pew

"Gina Whipp" wrote:

OBP,

Does the below jive with what you are doing or trying to do?

tblFirm
fFirmID (PK)
fFirmName
etc...

tblAgreements
aAgreementID (PK)

tblFirmAgreements
faFirmID (FK)
faAgreementID (FK)

tblRequirements
rRequirementID (PK)
etc...

tblAgreementRequirements
arAgreementRequirementsID (PK)
arAgreementID (FK)
arRequirementID (FK)

tblCertificatesOfAgreement
coaCertificatesOfAgreementID (PK)
coaFirmID (FK)


--
Gina Whipp

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

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

"oldblindpew" wrote in message
...
Fred,
Thanks for your reply. I wish I understood what you are talking about,
because I'm sure it's very important.

What do you mean by "entity"? In my mind, an entity is exemplified by
a
record in a table. A Firm is an entity; we have a table of Firms. An
Agreement is an entity; we have a table of Agreements. A Certificate
of
Insurance is an entity. It is a piece of paper describing the
insurance
coverage which a Firm has in force, to insure themselves against loss.
The
Certificate of Insurance is what we are trying to track here via our
Access
database application. The sample fields listed in my original post
came
directly from a certificate of insurance. We want to know 1) has a
Certificate has been furnished, 2) is it valid (i.e. meets or exceeds
all
the
requirements) and 3) if invalid, at what points is it non-compliant.

This is really all the information there is. I don't know how to
describe
what we're doing any more clearly; it isn't rocket science.

Could you possibly restate the final paragraph of your previous post?
I
couldn't make grammatical sense of it.

Thanks,
O.B.P.


"Fred" wrote:

Hello O.B.P

Here my take on it.....

You have to start by thinking /deciding what the fundamental ENTITIES
are
that you are going to database. You'll need to make the distinction
between such entities, and information which is merely an attribute of
an
entity. And you should have a unique name for each type of entity.

I believe that you have the above process still all scrambled
up......without fixing that, you have no foundation to build your
application
on. Including that you you are using the word "requirement" to refer
to
multiple fundamentally different things. And I don't mean just that
there
are different types of requirements (which would not be a problem) I
mean
these fundamentally different things:

- Something which is not an entity, but merely an attribute about an
agreement (e.g. expiration date)

- Something which is on a "pick list" of types of insurance (e.g.
Explosion/Collapse/Underground)

- Something which records the (existence of and key information about)
a
need for coverage. E.G. recording that a particular agreement has a
need
for
$1M of Explosion/Collapse/Underground insurance.

In my opinion, until you complete / unscramble the above process, your
question is not only unanswerable but not really communicated.

- - - - -

But, if I were to take a pre-mature guess, if expanding your 18 item
list
to a few dozen items is sufficient to cover 99% of all requirements,
you
can
expand your 99.9% of your "requirements" (sic.) the one big flat
table,
with a field for each of those "requirements" would be a good choice,
and
put
the other 1/10th % into a free form "special requirements" field.


Hope that helps a little.

Fred

OBP,

No problem... these long posts can sometimes be confusing, so let's make it
easier...

1. Your answer...
FirmID has always been a foreign key in my tblAgreements. I'm sorry if I
didn't make this clear. I'm not sure how we got into a discussion of the
relationship between Firms and Agreements, as I'm not aware of any
question
or problem in this area.

*** My Answer...
I just wasn't sure where that field was so I mentioned that but I see you
have that solved.


2. You answer...
I was not aware of any reason why a join table could not also contain data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table,
and
wouldn't that table also have to include a field for Quantity?

My Answer...
I would join Order to Products but I think not the way you mean. I would
have a seperate OrderDetails table. (One Order to Many Order Details OR my
join table)

3. You answer...
To extend the Retailing example given above, what you are suggesting
sounds
like marrying Quantity to Product. In my case, the "item" we are "buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary from
one "order" to the next. Now, I could redefine the "item" as "$1m GL per
Occurrence", thus combining quantity with description, but this would
require
creating separate items for every conceivable dollar amount. This in
itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if we
required $1m and the certificate provided $2m, we could easily tell that
we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

My Answer...
All of the above would be in my seperate table OrderDetails

4. You answer...
The CertificateValue is NOT an Insurance Requirement, but is what is being
OFFERED in order to meet the requirment. In the above example, we
required a
$1m policy, but the certificate provided $2m worth of coverage. Again, in
a
retailing context, this might be analogous to Quantity Ordered vs Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is that
my
insurance items are not all measured in dollars. Some are dates, some are
yes/no, and some are multiple choice answers.

My Answer...
Okay then this too would be in my OrderDetails table.

5. Your Answer...
Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that there
are
not going to be any clear answers and that I just need to work this out
for
myself.

My Answer...
I understand why you feel fustrated. Trying to explain this to folks who
are not familar with this area can be fustrating and I am sorry you feel
that. Realize that I and a few others, are not familar with your area and
sometimes that means we ask alot of questions but all we are trying to do
understand to make sure we give you the best possible advice. Up to you
what you do from there...

--
Gina Whipp

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

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

"oldblindpew" wrote in message
...
Gina,
I'm sorry, I just now realized you had embedded your replies down in the
past entries.

Gina wrote:
***I understand this which is why you need a join table OR add FirmID to
tblAgreements?
Pew responds:
FirmID has always been a foreign key in my tblAgreements. I'm sorry if I
didn't make this clear. I'm not sure how we got into a discussion of the
relationship between Firms and Agreements, as I'm not aware of any
question
or problem in this area.

Gina wrote:
tblAgreementsRequirements ***This is a join table, not meant to store
anything but the two tables it is joining.
Pew responds:
I was not aware of any reason why a join table could not also contain data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table,
and
wouldn't that table also have to include a field for Quantity?

Gina wrote:
arInsuranceRequirementValue - ***REMOVE from here to
tblInsuranceRequirements
Pew responds:
To extend the Retailing example given above, what you are suggesting
sounds
like marrying Quantity to Product. In my case, the "item" we are "buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary from
one "order" to the next. Now, I could redefine the "item" as "$1m GL per
Occurrence", thus combining quantity with description, but this would
require
creating separate items for every conceivable dollar amount. This in
itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if we
required $1m and the certificate provided $2m, we could easily tell that
we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

Gina wrote:
arCertificateValue - ***If this is part of Insurance Requirements move
to
that table.
Pew responds:
The CertificateValue is NOT an Insurance Requirement, but is what is being
OFFERED in order to meet the requirment. In the above example, we
required a
$1m policy, but the certificate provided $2m worth of coverage. Again, in
a
retailing context, this might be analogous to Quantity Ordered vs Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is that
my
insurance items are not all measured in dollars. Some are dates, some are
yes/no, and some are multiple choice answers.

Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that there
are
not going to be any clear answers and that I just need to work this out
for
myself.

--Pew

Gina,
Thanks for your patience.

I think we are both thinking the same thing. When I said "...join Orders
and Products to create an Order Details table", I meant that you create an
OrderDetails table for the purpose of joining Orders and Products.

When you said you would have a separate OrderDetails table with one Order
and many Order Details, I assume you meant "one Order and many Products".
When you said "...OR my join table" I asume you meant "OR" as in "i.e.", not
"OR" as in "alternatively".

The question still remains, how can a single field in my details table for
"requested value" (analogous to Quantity Ordered) hold values of differing
data types? In an earlier post you gave the only possible reply, that
different data types require different fields, and in response to my last
post, you stated that everything would go in my details table. This seems to
bring us back full circle to one record for each order, with a field for each
insurance parameter--the spreadsheet approach.

Could the answer lie in subdividing my insurance parameters down into at
least four different tables? Separate tables for yes/no-, text-, date-, and
currency-type parameters? Then I would need four join tables to link each of
those back to the Order table. It staggers my mind that Access would have so
much trouble dealing with large tables (i.e. having many fields) that it
would actually be better, perhaps even necessary, to create such a webwork of
linked tables.

--Pew


"Gina Whipp" wrote:

OBP,

No problem... these long posts can sometimes be confusing, so let's make it
easier...

1. Your answer...
FirmID has always been a foreign key in my tblAgreements. I'm sorry if I
didn't make this clear. I'm not sure how we got into a discussion of the
relationship between Firms and Agreements, as I'm not aware of any
question
or problem in this area.

*** My Answer...
I just wasn't sure where that field was so I mentioned that but I see you
have that solved.


2. You answer...
I was not aware of any reason why a join table could not also contain data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table,
and
wouldn't that table also have to include a field for Quantity?

My Answer...
I would join Order to Products but I think not the way you mean. I would
have a seperate OrderDetails table. (One Order to Many Order Details OR my
join table)

3. You answer...
To extend the Retailing example given above, what you are suggesting
sounds
like marrying Quantity to Product. In my case, the "item" we are "buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary from
one "order" to the next. Now, I could redefine the "item" as "$1m GL per
Occurrence", thus combining quantity with description, but this would
require
creating separate items for every conceivable dollar amount. This in
itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if we
required $1m and the certificate provided $2m, we could easily tell that
we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

My Answer...
All of the above would be in my seperate table OrderDetails

4. You answer...
The CertificateValue is NOT an Insurance Requirement, but is what is being
OFFERED in order to meet the requirment. In the above example, we
required a
$1m policy, but the certificate provided $2m worth of coverage. Again, in
a
retailing context, this might be analogous to Quantity Ordered vs Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is that
my
insurance items are not all measured in dollars. Some are dates, some are
yes/no, and some are multiple choice answers.

My Answer...
Okay then this too would be in my OrderDetails table.

5. Your Answer...
Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that there
are
not going to be any clear answers and that I just need to work this out
for
myself.

My Answer...
I understand why you feel fustrated. Trying to explain this to folks who
are not familar with this area can be fustrating and I am sorry you feel
that. Realize that I and a few others, are not familar with your area and
sometimes that means we ask alot of questions but all we are trying to do
understand to make sure we give you the best possible advice. Up to you
what you do from there...

--
Gina Whipp

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

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

"oldblindpew" wrote in message
...
Gina,
I'm sorry, I just now realized you had embedded your replies down in the
past entries.

Gina wrote:
***I understand this which is why you need a join table OR add FirmID to
tblAgreements?
Pew responds:
FirmID has always been a foreign key in my tblAgreements. I'm sorry if I
didn't make this clear. I'm not sure how we got into a discussion of the
relationship between Firms and Agreements, as I'm not aware of any
question
or problem in this area.

Gina wrote:
tblAgreementsRequirements ***This is a join table, not meant to store
anything but the two tables it is joining.
Pew responds:
I was not aware of any reason why a join table could not also contain data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table,
and
wouldn't that table also have to include a field for Quantity?

Gina wrote:
arInsuranceRequirementValue - ***REMOVE from here to
tblInsuranceRequirements
Pew responds:
To extend the Retailing example given above, what you are suggesting
sounds
like marrying Quantity to Product. In my case, the "item" we are "buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary from
one "order" to the next. Now, I could redefine the "item" as "$1m GL per
Occurrence", thus combining quantity with description, but this would
require
creating separate items for every conceivable dollar amount. This in
itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if we
required $1m and the certificate provided $2m, we could easily tell that
we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

Gina wrote:
arCertificateValue - ***If this is part of Insurance Requirements move
to
that table.
Pew responds:
The CertificateValue is NOT an Insurance Requirement, but is what is being
OFFERED in order to meet the requirment. In the above example, we
required a
$1m policy, but the certificate provided $2m worth of coverage. Again, in
a
retailing context, this might be analogous to Quantity Ordered vs Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is that
my
insurance items are not all measured in dollars. Some are dates, some are
yes/no, and some are multiple choice answers.

Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that there
are
not going to be any clear answers and that I just need to work this out
for
myself.

--Pew

OBP...

Answers below in line

--
Gina Whipp

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

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

"oldblindpew" wrote in message
...
Gina,
Thanks for your patience.

I think we are both thinking the same thing. When I said "...join Orders
and Products to create an Order Details table", I meant that you create an
OrderDetails table for the purpose of joining Orders and Products.
******Yeppers******

When you said you would have a separate OrderDetails table with one Order
and many Order Details, I assume you meant "one Order and many Products".
When you said "...OR my join table" I asume you meant "OR" as in "i.e.",
not
"OR" as in "alternatively".
******Yeppers******

The question still remains, how can a single field in my details table for
"requested value" (analogous to Quantity Ordered) hold values of differing
data types? In an earlier post you gave the only possible reply, that
different data types require different fields, and in response to my last
post, you stated that everything would go in my details table. This seems
to
bring us back full circle to one record for each order, with a field for
each
insurance parameter--the spreadsheet approach.

Could the answer lie in subdividing my insurance parameters down into at
least four different tables? Separate tables for yes/no-, text-, date-,
and
currency-type parameters? Then I would need four join tables to link each
of
those back to the Order table. It staggers my mind that Access would have
so
much trouble dealing with large tables (i.e. having many fields) that it
would actually be better, perhaps even necessary, to create such a webwork
of
linked tables.

******OR 1 table with that field set as text BUT on the form set your
varying parameters. It is not the hard time dealing with large tables, it's
normalization AND when it's time to display the data to insure the table
will allow for *proper* display. That being said I have *broken* the
*rules* when the situation warrants it, sometimes and let me say that again
SOMETIMES the rules have to be broken to get what you need. In your case,
they might need some breaking BUT bare in mind that is the exception not the
rule.******

******AND THANK YOU for your patience.******


--Pew


"Gina Whipp" wrote:

OBP,

No problem... these long posts can sometimes be confusing, so let's make
it
easier...

1. Your answer...
FirmID has always been a foreign key in my tblAgreements. I'm sorry if
I
didn't make this clear. I'm not sure how we got into a discussion of
the
relationship between Firms and Agreements, as I'm not aware of any
question
or problem in this area.

*** My Answer...
I just wasn't sure where that field was so I mentioned that but I see you
have that solved.


2. You answer...
I was not aware of any reason why a join table could not also contain
data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table,
and
wouldn't that table also have to include a field for Quantity?

My Answer...
I would join Order to Products but I think not the way you mean. I would
have a seperate OrderDetails table. (One Order to Many Order Details OR
my
join table)

3. You answer...
To extend the Retailing example given above, what you are suggesting
sounds
like marrying Quantity to Product. In my case, the "item" we are
"buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary
from
one "order" to the next. Now, I could redefine the "item" as "$1m GL
per
Occurrence", thus combining quantity with description, but this would
require
creating separate items for every conceivable dollar amount. This in
itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if
we
required $1m and the certificate provided $2m, we could easily tell
that
we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

My Answer...
All of the above would be in my seperate table OrderDetails

4. You answer...
The CertificateValue is NOT an Insurance Requirement, but is what is
being
OFFERED in order to meet the requirment. In the above example, we
required a
$1m policy, but the certificate provided $2m worth of coverage. Again,
in
a
retailing context, this might be analogous to Quantity Ordered vs
Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is
that
my
insurance items are not all measured in dollars. Some are dates, some
are
yes/no, and some are multiple choice answers.

My Answer...
Okay then this too would be in my OrderDetails table.

5. Your Answer...
Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that
there
are
not going to be any clear answers and that I just need to work this out
for
myself.

My Answer...
I understand why you feel fustrated. Trying to explain this to folks who
are not familar with this area can be fustrating and I am sorry you feel
that. Realize that I and a few others, are not familar with your area
and
sometimes that means we ask alot of questions but all we are trying to do
understand to make sure we give you the best possible advice. Up to you
what you do from there...

--
Gina Whipp

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

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

"oldblindpew" wrote in message
...
Gina,
I'm sorry, I just now realized you had embedded your replies down in
the
past entries.

Gina wrote:
***I understand this which is why you need a join table OR add FirmID
to
tblAgreements?
Pew responds:
FirmID has always been a foreign key in my tblAgreements. I'm sorry if
I
didn't make this clear. I'm not sure how we got into a discussion of
the
relationship between Firms and Agreements, as I'm not aware of any
question
or problem in this area.

Gina wrote:
tblAgreementsRequirements ***This is a join table, not meant to
store
anything but the two tables it is joining.
Pew responds:
I was not aware of any reason why a join table could not also contain
data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table,
and
wouldn't that table also have to include a field for Quantity?

Gina wrote:
arInsuranceRequirementValue - ***REMOVE from here to
tblInsuranceRequirements
Pew responds:
To extend the Retailing example given above, what you are suggesting
sounds
like marrying Quantity to Product. In my case, the "item" we are
"buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary
from
one "order" to the next. Now, I could redefine the "item" as "$1m GL
per
Occurrence", thus combining quantity with description, but this would
require
creating separate items for every conceivable dollar amount. This in
itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if
we
required $1m and the certificate provided $2m, we could easily tell
that
we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

Gina wrote:
arCertificateValue - ***If this is part of Insurance Requirements
move
to
that table.
Pew responds:
The CertificateValue is NOT an Insurance Requirement, but is what is
being
OFFERED in order to meet the requirment. In the above example, we
required a
$1m policy, but the certificate provided $2m worth of coverage. Again,
in
a
retailing context, this might be analogous to Quantity Ordered vs
Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is
that
my
insurance items are not all measured in dollars. Some are dates, some
are
yes/no, and some are multiple choice answers.

Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that
there
are
not going to be any clear answers and that I just need to work this out
for
myself.

--Pew

Hi Fred,
Your solution echoes what has been under discussion since 8/27. Here is a
snippet from that date:

TblAgreements
AgreementID
Agreement fields

TblInsParameters
InsParameterID
InsParameterDescrip
Other fields, if any

TblCertificates
CertificateID
AgreementID
InsRequirementID (same as InsParameterID)
InsOfferingID (same as InsParameterID)

Your questions we
Can we have a standardized list of Insurance Parameters? (Yes).
Can each parameter be defined by a text description plus a numeric value?
(No, I don't think so).

See also exchanges with Gina in this thread. The idea is to have a table of
Agreements and a table of Insurance Parameters (your "Types"). A third table
joins these together. You suggested calling it "Requirements"; I called it
"Certificates". If the same Parameter key appears in the join record as both
required and provided (offered), then the Certificate is valid for that
parameter.

I soon realized, however, that this model allows only for exact match
between required and offered parameters, whereas I need a "meets or exceeds"
standard. It occurred to me to add a numeric field to the join table to
contain the "value" of the parameter, just as you suggest. Of course, two
fields would be needed, one for the value as required, and another for the
value as offered.

But what about expiration date? The value of the expiration parameter is a
date, not a number; do I add another field to the join table for this? A
field which will remain empty for most records? This led to questioning the
whole idea of normalizing this data. At first, normalization seems to
simplify, but then you have to add in and tack on all the stuff that was
removed via normalization, so in the end it seems like you have more fields
than before, plus all the keys and indexes needed to make it work. But
apparently, one-to-one tables are such a horrifying prospect to experienced
Access developers as to make them worth going to almost any lengths to avoid.
(Sylvain Lafontaine offered a dissenting view elsewhere in this thread, but
his advice boils down to "maybe, maybe not").

Gina suggested storing the Parameter Values as text and then converting them
as needed to what they actually are, for display purposes. I'm not sure I
understood her correctly, but this sounds like another instance of having to
stand on one leg and sing "Doo-Dah" to get Access to work.

--Pew

"Fred" wrote:

Hello Pew,

To start with, long story short, I'm going to go only by your 9/11/09 post
except possibly to glance back at the others for a few factoids where your
new posts brings up a quesiton or has missing info.

First, I'm going to ask a couple of questions and discuss them and then
temporarily presume an answer.

Question#1 Can you assume that the types coverage requirements that need
this type of databasing come from a standardized list (lets call it
ListOfCoverageTypes) That all other variable attributes (e.g. required dates
of coverage) are implied

Question #2 Can you assume that each coverage requirement that needs this
type of databasing can be defined by specifying one item from the
ListOfCoverageTypes plus ONE number (limits)?

Questions #3 Can you assume that the data entered can be split up the
certificate into individual instances of coverage, specifically the same as
Question #1 & #2? I.E. each entered as a type from the ListOfInsuranceTypes,
plus one number (limits).

I'll temporarily assume that the answers to all three are "yes". If not
true for #1 or #2, then it would be "back to the drawing board" regarding
structure. If not true for #3, then you will need a team of lawyers and
insurance experts :-) to do the split up and definition described in Q#3

If so, then here's my idea on a way to do it. Change/shorten the names as
desired.


Table: ListOfInsuranceTypes PK = InsuranceType_ID

Table of Agreements , PK = Agreement_ID. (a subcontract is an agreement)

Table: InsuranceRequirements:

FK = Agreement_ID. Also include an integer numerical field "RequiredLimit"
default value = 0
PK = InsuranceRequirement_ID
FK = RequiredInsuranceType_ID

Table: InsuranceCertificates.
PK: Certificate_ID
FK: Agreement_ID

Next we'll be talking about InstancesOfCoverageFurnished, as fufilled by the
Insurance certificates. Here we hit a fork in the road.

If, for every entry, they can immediately pair it with an insurance
requirement, then just add the following fields to your InsuranceRequirements
table:

- FK FurnishedInsuranceType_ID
- LimitProvided (integer)
- FK Certificate_ID

If you need to be able to record coverage furunished that has not been
paired with a requirement, then you can go the intellectually complex but
structurally simple route of still making the above additions to your
InsuranceRequirements table, but rename it
"InsuranceRequirementsAndOrInsuranceItemsProvi ded" (of course shorten that)

The above is normalized not because it fulfills a normalizaiton religion
commandment, and not because it makes it easire for Access or your computer.
It's normalized because I think that it will make it easirer for you.
Including, I think, that you will be able to use a simple query (not that
anticipated complex VBA) to test that a certificate fufills all insurance
requirements.

I just wrote the above, I didn't test it (for errors , but I think that the
concept is good for your requirements, provided that the temporary answer to
the above 3 questions were right.

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.

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.