One-To-One Tables

I agree with John's response. One thing that I'll add is what happens to all
your forms, reports, and queries when someone adds to the standard set of
categories? They will all need to be redesigned. It's better to plan for such
possible changes in your design now than to deal with them piecemeal in the
future.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

Thanks for your reply. I don't think the one-to-many is correct. It is true
that one Agreement imposes many requirements, however, the requirements
consist of a standard set of categories. There will be a set group of fields
making up the Requirements Table, with the values in those fields varying
from one Agreement to the next.

This is really about subcontractor's insurance, but to simplify, let's say
we're talking about furnishing cars. One Agreement may require a Yellow,
2-door, Chevy. Another Agreement may require a Red, 4-door, Ford. The
Requirements table would have fields for Color, Number of Doors, and Make.
Field values would be dictated by each Agreement. A Certificate would be
furnished describing the actual car being offered in fulfillment of the
Agreement. The Validation would show how close a match was achieved between
the Requirement and the Offering.


"Jerry Whittle" wrote:

Seems to me if an Agreement imposes a set of Requirements, then you have a
One-to-Many relationship. Is this correct? Hopefully you aren't putting
Requirements in a table with fields something like Req1, Req2, and so on.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

This may prove to be an open-ended question, but here goes. I'm anticipating
having three tables with one-to-one relationships back to another table. All
of this information could therefore be placed in one massive table, but
somehow I feel it would be more manageable if separated. The question is,
should I key all three tables back to the main table, or should I key the
main to the first, the first to the second, and the second to the third?
Does it matter?

More info: The main table is Agreements. The sub-tables are Requirements,
Certificates, and Validations. Each Agreement imposes a set of Requirements.
A Certificate lists what is being offered to meet those Requirements, and a
Validation checks what is being offered against what is being required, field
by field.

So you could say Agreements result in Requirements, which result in
Certificates, which result in Validations. Or, you could say for each
Agreement there is a Requirement, a Certificate, and a Validation. There is
truth in both statements as all of these are interrelated. I'm just afraid
this will be another one of those cases where you have a choice, and it
doesn't really seem to matter, but one day long afterwards you find out that
despite all your caution you made the wrong choice.

You got three initial responses, Jerry, myself (Steve) and John.

Steve


"oldblindpew" wrote in message
...
I'm sorry, but I don't know who you are or what responses your are
referring
to.
--OBP

"Steve" wrote:

You refused to respond back to my response so I am withdrawing from
further
response to you!!!!

Steve


"oldblindpew" wrote in message
...
This may prove to be an open-ended question, but here goes. I'm
anticipating
having three tables with one-to-one relationships back to another
table.
All
of this information could therefore be placed in one massive table, but
somehow I feel it would be more manageable if separated. The question
is,
should I key all three tables back to the main table, or should I key
the
main to the first, the first to the second, and the second to the
third?
Does it matter?

More info: The main table is Agreements. The sub-tables are
Requirements,
Certificates, and Validations. Each Agreement imposes a set of
Requirements.
A Certificate lists what is being offered to meet those Requirements,
and
a
Validation checks what is being offered against what is being required,
field
by field.

So you could say Agreements result in Requirements, which result in
Certificates, which result in Validations. Or, you could say for each
Agreement there is a Requirement, a Certificate, and a Validation.
There
is
truth in both statements as all of these are interrelated. I'm just
afraid
this will be another one of those cases where you have a choice, and it
doesn't really seem to matter, but one day long afterwards you find out
that
despite all your caution you made the wrong choice.

On Wed, 26 Aug 2009 11:32:47 -0400, "Steve" wrote:

You got three initial responses, Jerry, myself (Steve) and John.

I think you may have propagation problems, Steve: your response did not make
it to my server (and probably not to oldblindpew's either).

--

John W. Vinson [MVP]

Thanks for replying, John, and you are correct about Steve. I already stated
that I don't know who he is or what response he is referring to, so it should
be abundantly clear that I never saw his inital message.

I believe I understand what you are saying about normalization and
many-to-many relationships, in order to avoid saving the same values
repeatedly, as in a spreadsheet, and in order to avoid the age old problem of
discovering that you didn't put enough columns, or the correct columns, in
your table. However, I also know there are cases, as with Postal Codes,
where the benefits of normalization are not worth the trouble, and I am still
wrestling with that question here.

I want to make sure you are taking into account that it isn't just a simple
matter of a many-to-many relationship between Certificate and Requirement.
The Agreement dictates the Requirement set. The Certificate, on the other
hand, provides a set of what might be called Offerings in response to the
Requirements. Wouldn't I need a Agreement/Requirement join and a
Certificate/Offering join? Once I got that done, I'd need a way to compare
these two joins to see if the Offerings of the Certificate satisfy the
Requirements of the Agreement. I'm afraid I'll get so tangled up with
cross-references that it won't be worth the trouble.

I'm sorry for this half-baked response, but I didn't want to wait too long
to reply. I'm also finding that the reference books I'm using aren't all
that helpful. Their discussions of many-to-many relationships seem a bit
sketchy.
--OBP

"John W. Vinson" wrote:

On Tue, 25 Aug 2009 15:18:11 -0700, oldblindpew
wrote:

Thanks for your reply. See also my response to Jerry Whittle.

This is about Certificates of Insurance furnished by subcontractors. Much
of my design is driven by the standard certificate form commonly seen in the
industry. This certificate form has lots of fields on it, and in order to
validate the certificate, each field has to be checked. My approach has been
to create one table for the certificate information and another similar table
to store the results of the validation, if ever I can get there.

To check or validate the certificate, there must be requirements to check
against. Originally I had a third table consisting of three records defining
three standard sets of insurance requirements, because we took sort of a
"Small, Medum, Large" approach to our requirements. Each Agreement pointed
to one of these three standard sets of requirements. But since it is
possible for any given agreement to modify or waive any given insurance
parameter, it seems better to let each agreement have its own unique set of
insurance requirements. Thus I arrive at three tables each relating back to
the Agreements table in a one-to-one fashion.

In order to ease the task of establishing the insurance requirements for
each agreement, I'll need to figure out how to enable the user to copy a
requirements record from one of the three standards, or from an existing
agreement, and then make whatever modifications may be called for by the
Agreement.

I appreciate your taking time to consider this and offer advice on whether
it matters how the tables are linked. Or that the whole effort would be
better served by a totally different approach. --Thanks, OBP

I think it WOULD be better served by a totally differnent approach - a
normalized Access database. g

It sounds very much to me like you have a very straightforward Many to Many
relationship between Certificates and Requirements. Each certificate has many
requirements; each requirement may occur on many Certificates.

You're solving the problem using spreadsheet logic: one field (column) for
each requirement. This is not ideal in Access! If your "Small" template has
(say) seventeen requirements, then you could have seventeen *RECORDS* in a
CertRequirements table, linked to this particular subcontractor's certificate,
and to a table of all the possible requirements that might arise (seventeen of
them from seventeen different records, in this instance). This would let you
add new requirements, waive individual requirements, add modifying notes or
comments to a requirement, etc. etc.

--

John W. Vinson [MVP]

I think $teve was referring to an earlier thread from the same OP.

--
Duane Hookom
Microsoft Access MVP


"John W. Vinson" wrote:

On Wed, 26 Aug 2009 11:32:47 -0400, "Steve" wrote:

You got three initial responses, Jerry, myself (Steve) and John.

I think you may have propagation problems, Steve: your response did not make
it to my server (and probably not to oldblindpew's either).

--

John W. Vinson [MVP]

Nope. Steve said I got three initial responses to my question, one from
Jerry, one from Steve and one from John. I see only Jerry's and John's.
--OBP

"Duane Hookom" wrote:

I think $teve was referring to an earlier thread from the same OP.

--
Duane Hookom
Microsoft Access MVP


"John W. Vinson" wrote:

On Wed, 26 Aug 2009 11:32:47 -0400, "Steve" wrote:

You got three initial responses, Jerry, myself (Steve) and John.

I think you may have propagation problems, Steve: your response did not make
it to my server (and probably not to oldblindpew's either).

--

John W. Vinson [MVP]

Here is my response again ..........

Actually you don't have one-to-one relationships! You have a one-to-many
relationship between Agreements and Requirements. You have a one-to-many
relationship between Requirements and Certificates. You have a one-to-many
relationship between Requirements and Validation Checks. Your tables should
look like:
TblAgreement
AgreementID
Agreement Fields

TblRequirement
RequirementID
AgreementID
Requirement Fields

TblValidationCheck
ValidationCheckID
RequirementID
Validation Check Fields

This is just a basic outline since you gave no details about agreements,
requirements and validation checks. If you provide more detail, I will
expand upon the above.

Steve


"oldblindpew" wrote in message
...
Thanks for replying, John, and you are correct about Steve. I already
stated
that I don't know who he is or what response he is referring to, so it
should
be abundantly clear that I never saw his inital message.

I believe I understand what you are saying about normalization and
many-to-many relationships, in order to avoid saving the same values
repeatedly, as in a spreadsheet, and in order to avoid the age old problem
of
discovering that you didn't put enough columns, or the correct columns, in
your table. However, I also know there are cases, as with Postal Codes,
where the benefits of normalization are not worth the trouble, and I am
still
wrestling with that question here.

I want to make sure you are taking into account that it isn't just a
simple
matter of a many-to-many relationship between Certificate and Requirement.
The Agreement dictates the Requirement set. The Certificate, on the other
hand, provides a set of what might be called Offerings in response to the
Requirements. Wouldn't I need a Agreement/Requirement join and a
Certificate/Offering join? Once I got that done, I'd need a way to
compare
these two joins to see if the Offerings of the Certificate satisfy the
Requirements of the Agreement. I'm afraid I'll get so tangled up with
cross-references that it won't be worth the trouble.

I'm sorry for this half-baked response, but I didn't want to wait too long
to reply. I'm also finding that the reference books I'm using aren't all
that helpful. Their discussions of many-to-many relationships seem a bit
sketchy.
--OBP

"John W. Vinson" wrote:

On Tue, 25 Aug 2009 15:18:11 -0700, oldblindpew
wrote:

Thanks for your reply. See also my response to Jerry Whittle.

This is about Certificates of Insurance furnished by subcontractors.
Much
of my design is driven by the standard certificate form commonly seen in
the
industry. This certificate form has lots of fields on it, and in order
to
validate the certificate, each field has to be checked. My approach has
been
to create one table for the certificate information and another similar
table
to store the results of the validation, if ever I can get there.

To check or validate the certificate, there must be requirements to
check
against. Originally I had a third table consisting of three records
defining
three standard sets of insurance requirements, because we took sort of a
"Small, Medum, Large" approach to our requirements. Each Agreement
pointed
to one of these three standard sets of requirements. But since it is
possible for any given agreement to modify or waive any given insurance
parameter, it seems better to let each agreement have its own unique set
of
insurance requirements. Thus I arrive at three tables each relating
back to
the Agreements table in a one-to-one fashion.

In order to ease the task of establishing the insurance requirements for
each agreement, I'll need to figure out how to enable the user to copy a
requirements record from one of the three standards, or from an existing
agreement, and then make whatever modifications may be called for by the
Agreement.

I appreciate your taking time to consider this and offer advice on
whether
it matters how the tables are linked. Or that the whole effort would be
better served by a totally different approach. --Thanks, OBP

I think it WOULD be better served by a totally differnent approach - a
normalized Access database. g

It sounds very much to me like you have a very straightforward Many to
Many
relationship between Certificates and Requirements. Each certificate has
many
requirements; each requirement may occur on many Certificates.

You're solving the problem using spreadsheet logic: one field (column)
for
each requirement. This is not ideal in Access! If your "Small" template
has
(say) seventeen requirements, then you could have seventeen *RECORDS* in
a
CertRequirements table, linked to this particular subcontractor's
certificate,
and to a table of all the possible requirements that might arise
(seventeen of
them from seventeen different records, in this instance). This would let
you
add new requirements, waive individual requirements, add modifying notes
or
comments to a requirement, etc. etc.

--

John W. Vinson [MVP]

Again, here is my response .........

Actually you don't have one-to-one relationships! You have a one-to-many
relationship between Agreements and Requirements. You have a one-to-many
relationship between Requirements and Certificates. You have a one-to-many
relationship between Requirements and Validation Checks. Your tables should
look like:
TblAgreement
AgreementID
Agreement Fields

TblRequirement
RequirementID
AgreementID
Requirement Fields

TblValidationCheck
ValidationCheckID
RequirementID
Validation Check Fields

This is just a basic outline since you gave no details about agreements,
requirements and validation checks. If you provide more detail, I will
expand upon the above.

Steve


"oldblindpew" wrote in message
...
Nope. Steve said I got three initial responses to my question, one from
Jerry, one from Steve and one from John. I see only Jerry's and John's.
--OBP

"Duane Hookom" wrote:

I think $teve was referring to an earlier thread from the same OP.

--
Duane Hookom
Microsoft Access MVP


"John W. Vinson" wrote:

On Wed, 26 Aug 2009 11:32:47 -0400, "Steve"
wrote:

You got three initial responses, Jerry, myself (Steve) and John.

I think you may have propagation problems, Steve: your response did not
make
it to my server (and probably not to oldblindpew's either).

--

John W. Vinson [MVP]

Thanks, Steve, we meet at last. In my reply to Jerry I posed an analogy to
furnishing automobiles. One Agreement might specify that a Yellow, 2-door,
Chevrolet be furnished. "Yellow", "2-door", and "Chevrolet" would be
examples of Requirements. The list of requirements could obviously go on and
on. Therefore, yes, one Agreement has many requirements, however, any one
Requirement may be equally applicable to more than one different Agreement,
so strict normalization would require, as Jerry and John suggested, a
many-to-many relationship between Agreements and Requirements.--OBP

"Steve" wrote:

Here is my response again ..........

Actually you don't have one-to-one relationships! You have a one-to-many
relationship between Agreements and Requirements. You have a one-to-many
relationship between Requirements and Certificates. You have a one-to-many
relationship between Requirements and Validation Checks. Your tables should
look like:
TblAgreement
AgreementID
Agreement Fields

TblRequirement
RequirementID
AgreementID
Requirement Fields

TblValidationCheck
ValidationCheckID
RequirementID
Validation Check Fields

This is just a basic outline since you gave no details about agreements,
requirements and validation checks. If you provide more detail, I will
expand upon the above.

Steve


"oldblindpew" wrote in message
...
Thanks for replying, John, and you are correct about Steve. I already
stated
that I don't know who he is or what response he is referring to, so it
should
be abundantly clear that I never saw his inital message.

I believe I understand what you are saying about normalization and
many-to-many relationships, in order to avoid saving the same values
repeatedly, as in a spreadsheet, and in order to avoid the age old problem
of
discovering that you didn't put enough columns, or the correct columns, in
your table. However, I also know there are cases, as with Postal Codes,
where the benefits of normalization are not worth the trouble, and I am
still
wrestling with that question here.

I want to make sure you are taking into account that it isn't just a
simple
matter of a many-to-many relationship between Certificate and Requirement.
The Agreement dictates the Requirement set. The Certificate, on the other
hand, provides a set of what might be called Offerings in response to the
Requirements. Wouldn't I need a Agreement/Requirement join and a
Certificate/Offering join? Once I got that done, I'd need a way to
compare
these two joins to see if the Offerings of the Certificate satisfy the
Requirements of the Agreement. I'm afraid I'll get so tangled up with
cross-references that it won't be worth the trouble.

I'm sorry for this half-baked response, but I didn't want to wait too long
to reply. I'm also finding that the reference books I'm using aren't all
that helpful. Their discussions of many-to-many relationships seem a bit
sketchy.
--OBP

"John W. Vinson" wrote:

On Tue, 25 Aug 2009 15:18:11 -0700, oldblindpew
wrote:

Thanks for your reply. See also my response to Jerry Whittle.

This is about Certificates of Insurance furnished by subcontractors.
Much
of my design is driven by the standard certificate form commonly seen in
the
industry. This certificate form has lots of fields on it, and in order
to
validate the certificate, each field has to be checked. My approach has
been
to create one table for the certificate information and another similar
table
to store the results of the validation, if ever I can get there.

To check or validate the certificate, there must be requirements to
check
against. Originally I had a third table consisting of three records
defining
three standard sets of insurance requirements, because we took sort of a
"Small, Medum, Large" approach to our requirements. Each Agreement
pointed
to one of these three standard sets of requirements. But since it is
possible for any given agreement to modify or waive any given insurance
parameter, it seems better to let each agreement have its own unique set
of
insurance requirements. Thus I arrive at three tables each relating
back to
the Agreements table in a one-to-one fashion.

In order to ease the task of establishing the insurance requirements for
each agreement, I'll need to figure out how to enable the user to copy a
requirements record from one of the three standards, or from an existing
agreement, and then make whatever modifications may be called for by the
Agreement.

I appreciate your taking time to consider this and offer advice on
whether
it matters how the tables are linked. Or that the whole effort would be
better served by a totally different approach. --Thanks, OBP

I think it WOULD be better served by a totally differnent approach - a
normalized Access database. g

It sounds very much to me like you have a very straightforward Many to
Many
relationship between Certificates and Requirements. Each certificate has
many
requirements; each requirement may occur on many Certificates.

You're solving the problem using spreadsheet logic: one field (column)
for
each requirement. This is not ideal in Access! If your "Small" template
has
(say) seventeen requirements, then you could have seventeen *RECORDS* in
a
CertRequirements table, linked to this particular subcontractor's
certificate,
and to a table of all the possible requirements that might arise
(seventeen of
them from seventeen different records, in this instance). This would let
you
add new requirements, waive individual requirements, add modifying notes
or
comments to a requirement, etc. etc.

--

John W. Vinson [MVP]

Okay, Add another table to my suggested tables ...
TblAgreementRequirement
AgreementRequirementID
AgreementID
RequirementID
RequirementMet (Yes/No)

The RequirementMet field is your validation. You don't need a validation
table.

Steve



"oldblindpew" wrote in message
...
Thanks, Steve, we meet at last. In my reply to Jerry I posed an analogy
to
furnishing automobiles. One Agreement might specify that a Yellow,
2-door,
Chevrolet be furnished. "Yellow", "2-door", and "Chevrolet" would be
examples of Requirements. The list of requirements could obviously go on
and
on. Therefore, yes, one Agreement has many requirements, however, any one
Requirement may be equally applicable to more than one different
Agreement,
so strict normalization would require, as Jerry and John suggested, a
many-to-many relationship between Agreements and Requirements.--OBP

"Steve" wrote:

Here is my response again ..........

Actually you don't have one-to-one relationships! You have a one-to-many
relationship between Agreements and Requirements. You have a one-to-many
relationship between Requirements and Certificates. You have a
one-to-many
relationship between Requirements and Validation Checks. Your tables
should
look like:
TblAgreement
AgreementID
Agreement Fields

TblRequirement
RequirementID
AgreementID
Requirement Fields

TblValidationCheck
ValidationCheckID
RequirementID
Validation Check Fields

This is just a basic outline since you gave no details about agreements,
requirements and validation checks. If you provide more detail, I will
expand upon the above.

Steve


"oldblindpew" wrote in message
...
Thanks for replying, John, and you are correct about Steve. I already
stated
that I don't know who he is or what response he is referring to, so it
should
be abundantly clear that I never saw his inital message.

I believe I understand what you are saying about normalization and
many-to-many relationships, in order to avoid saving the same values
repeatedly, as in a spreadsheet, and in order to avoid the age old
problem
of
discovering that you didn't put enough columns, or the correct columns,
in
your table. However, I also know there are cases, as with Postal
Codes,
where the benefits of normalization are not worth the trouble, and I am
still
wrestling with that question here.

I want to make sure you are taking into account that it isn't just a
simple
matter of a many-to-many relationship between Certificate and
Requirement.
The Agreement dictates the Requirement set. The Certificate, on the
other
hand, provides a set of what might be called Offerings in response to
the
Requirements. Wouldn't I need a Agreement/Requirement join and a
Certificate/Offering join? Once I got that done, I'd need a way to
compare
these two joins to see if the Offerings of the Certificate satisfy the
Requirements of the Agreement. I'm afraid I'll get so tangled up with
cross-references that it won't be worth the trouble.

I'm sorry for this half-baked response, but I didn't want to wait too
long
to reply. I'm also finding that the reference books I'm using aren't
all
that helpful. Their discussions of many-to-many relationships seem a
bit
sketchy.
--OBP

"John W. Vinson" wrote:

On Tue, 25 Aug 2009 15:18:11 -0700, oldblindpew
wrote:

Thanks for your reply. See also my response to Jerry Whittle.

This is about Certificates of Insurance furnished by subcontractors.
Much
of my design is driven by the standard certificate form commonly seen
in
the
industry. This certificate form has lots of fields on it, and in
order
to
validate the certificate, each field has to be checked. My approach
has
been
to create one table for the certificate information and another
similar
table
to store the results of the validation, if ever I can get there.

To check or validate the certificate, there must be requirements to
check
against. Originally I had a third table consisting of three records
defining
three standard sets of insurance requirements, because we took sort
of a
"Small, Medum, Large" approach to our requirements. Each Agreement
pointed
to one of these three standard sets of requirements. But since it is
possible for any given agreement to modify or waive any given
insurance
parameter, it seems better to let each agreement have its own unique
set
of
insurance requirements. Thus I arrive at three tables each relating
back to
the Agreements table in a one-to-one fashion.

In order to ease the task of establishing the insurance requirements
for
each agreement, I'll need to figure out how to enable the user to
copy a
requirements record from one of the three standards, or from an
existing
agreement, and then make whatever modifications may be called for by
the
Agreement.

I appreciate your taking time to consider this and offer advice on
whether
it matters how the tables are linked. Or that the whole effort would
be
better served by a totally different approach. --Thanks, OBP

I think it WOULD be better served by a totally differnent approach - a
normalized Access database. g

It sounds very much to me like you have a very straightforward Many to
Many
relationship between Certificates and Requirements. Each certificate
has
many
requirements; each requirement may occur on many Certificates.

You're solving the problem using spreadsheet logic: one field (column)
for
each requirement. This is not ideal in Access! If your "Small"
template
has
(say) seventeen requirements, then you could have seventeen *RECORDS*
in
a
CertRequirements table, linked to this particular subcontractor's
certificate,
and to a table of all the possible requirements that might arise
(seventeen of
them from seventeen different records, in this instance). This would
let
you
add new requirements, waive individual requirements, add modifying
notes
or
comments to a requirement, etc. etc.

--

John W. Vinson [MVP]