Avoiding Redundant Records

It is my understanding that surrogate keys are generally recommended to 
ensure uniqueness of records.  Is it not true that using surrogate keys 
implies taking extra precautions to prevent duplicate records?  I mean, with 
surrogate keys there is nothing to prevent the proliferation of multiple 
records all containing the same data, but each having a unique key.

I would appreciate your help with this in the following context:

AGREEMENTS
AgrmtID (PK)
InsuredID
Agrmt fields…

CERTS
CertID (PK)
AgrmtID
ProducerID
Cert fields…

POLICIES
PolicyID (PK)
InsuredID
PolicyTypeCode
ProviderID
Policy fields…

CERTSPOLICIES
CertsPoliciesID (PK)
CertID
PolicyID

Note:  Any fieldname ending in “ID” is a surrogate key.

An Agreement can have zero or more Certs; a Cert pertains to only one 
Agreement, so this is a one-to-many relationship.  Each Cert can have one or 
more Policies; the same Policy can be on different Certs, so this is a 
many-to-many relationship, hence these two tables are joined by the 
CertsPolicies table.

We don’t want the same Policy to appear more than once on the same Cert.  I 
believe this can be accomplished fairly easily by setting up CertID and 
PolicyID as a multi-field unique index in the junction table.

We also have to ensure that the user doesn’t inadvertently relate any one 
Policy twice to the same Agreement through the use of a second Cert.  In 
other words, we do not want to see the same Policy on two different Certs for 
the same Agreement.  How would this be accomplished?

A fundamental assumption is that no Insured will ever have more than one 
Policy of a given type.  How would I guarantee that not more than one Policy 
of a given type (PolicyType Code) ever appears on any Cert?  How would I 
guarantee the same thing for any two Certs assigned to the same Insured?

Thanks,
OldBlindPew

0
Utf
1/21/2010 6:27:01 PM
access.tablesdbdesign 510 articles. 0 followers. Follow

13 Replies
862 Views

Similar Articles

[PageSpeed] 34

On Jan 21, 12:27=A0pm, oldblindpew
<oldblind...@discussions.microsoft.com> wrote:
> It is my understanding that surrogate keys are generally recommended to
> ensure uniqueness of records. =A0Is it not true that using surrogate keys
> implies taking extra precautions to prevent duplicate records? =A0I mean,=
 with
> surrogate keys there is nothing to prevent the proliferation of multiple
> records all containing the same data, but each having a unique key.
>
> I would appreciate your help with this in the following context:
>
> AGREEMENTS
> AgrmtID (PK)
> InsuredID
> Agrmt fields=85
>
> CERTS
> CertID (PK)
> AgrmtID
> ProducerID
> Cert fields=85
>
> POLICIES
> PolicyID (PK)
> InsuredID
> PolicyTypeCode
> ProviderID
> Policy fields=85
>
> CERTSPOLICIES
> CertsPoliciesID (PK)
> CertID
> PolicyID
>
> Note: =A0Any fieldname ending in =93ID=94 is a surrogate key.
>
> An Agreement can have zero or more Certs; a Cert pertains to only one
> Agreement, so this is a one-to-many relationship. =A0Each Cert can have o=
ne or
> more Policies; the same Policy can be on different Certs, so this is a
> many-to-many relationship, hence these two tables are joined by the
> CertsPolicies table.
>
> We don=92t want the same Policy to appear more than once on the same Cert=
.. =A0I
> believe this can be accomplished fairly easily by setting up CertID and
> PolicyID as a multi-field unique index in the junction table.
>
> We also have to ensure that the user doesn=92t inadvertently relate any o=
ne
> Policy twice to the same Agreement through the use of a second Cert. =A0I=
n
> other words, we do not want to see the same Policy on two different Certs=
 for
> the same Agreement. =A0How would this be accomplished?
>
> A fundamental assumption is that no Insured will ever have more than one
> Policy of a given type. =A0How would I guarantee that not more than one P=
olicy
> of a given type (PolicyType Code) ever appears on any Cert? =A0How would =
I
> guarantee the same thing for any two Certs assigned to the same Insured?
>
> Thanks,
> OldBlindPew

You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table.  Nothing wrong with that.  Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
0
Piet
1/21/2010 8:17:46 PM
CertsPoliciesID is autonumber and therefore the unique primary key for the 
junction table.  A unique index on the combination of CertID and PolicyID 
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs.  It is 
acceptable for an Agreement to have more than one Cert, but not that the same 
Policy should appear on more than one of their Certs.  Enforcing Cert/Policy 
uniqueness alone doesn't prevent this, and the uniqueness of the 
CertsPoliciesID key adds nothing.   

Similarly, I am concerned to prevent improper combinations resulting from 
policy types.  No Insured party is going to carry two General Liability 
Policies.  If we try to attribute two different GL policies to the same 
Insured, either by assigning the two policies to the same Cert, or by 
assigning them to two different Certs that are in turn tied to the same 
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:

> You could create a unique index on the combination of (CertID,
> PolicyID) in the CertsPolicies table.  Nothing wrong with that.  Then
> if your CertsPoliciesID is an autonumber and set to be unique, you
> should have everything, right?
> .
> 
0
Utf
1/21/2010 10:53:01 PM
It sounds like you are describing the "business rules" of your operation. 
It wouldn't matter if you were using Access or Excel or paper and pencil, 
those rules would apply (e.g., no customer carries more than one GL policy).

I'm not aware of any built-in business rule enforcer in MS Access.  I 
believe you'll need to add the validation checks to enforce those rules.

In some of your situations, using a unique index on multiple fields could be 
a way to use Access features to enforce your business rules ... but that's 
just plain lucky!  You'll probably need to figure out some edits/validation 
tests for your form, to prevent the users from doing something your business 
doesn't permit.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message 
news:CC037104-E3A4-4B28-BEFE-22E286558AA8@microsoft.com...
> CertsPoliciesID is autonumber and therefore the unique primary key for the
> junction table.  A unique index on the combination of CertID and PolicyID
> would prevent redundant Cert/Policy pairs.
>
> But I am also concerned with redundant Agreement/Policy pairs.  It is
> acceptable for an Agreement to have more than one Cert, but not that the 
> same
> Policy should appear on more than one of their Certs.  Enforcing 
> Cert/Policy
> uniqueness alone doesn't prevent this, and the uniqueness of the
> CertsPoliciesID key adds nothing.
>
> Similarly, I am concerned to prevent improper combinations resulting from
> policy types.  No Insured party is going to carry two General Liability
> Policies.  If we try to attribute two different GL policies to the same
> Insured, either by assigning the two policies to the same Cert, or by
> assigning them to two different Certs that are in turn tied to the same
> Agreement, something is wrong.
>
> Thanks,
> oldblindpew
>
> "Piet Linden" wrote:
>
>> You could create a unique index on the combination of (CertID,
>> PolicyID) in the CertsPolicies table.  Nothing wrong with that.  Then
>> if your CertsPoliciesID is an autonumber and set to be unique, you
>> should have everything, right?
>> .
>> 


0
Jeff
1/22/2010 12:35:58 AM
On Jan 21, 4:53=A0pm, oldblindpew
<oldblind...@discussions.microsoft.com> wrote:
> CertsPoliciesID is autonumber and therefore the unique primary key for th=
e
> junction table. =A0A unique index on the combination of CertID and Policy=
ID
> would prevent redundant Cert/Policy pairs.
>
> But I am also concerned with redundant Agreement/Policy pairs. =A0It is
> acceptable for an Agreement to have more than one Cert, but not that the =
same
> Policy should appear on more than one of their Certs. =A0Enforcing Cert/P=
olicy
> uniqueness alone doesn't prevent this, and the uniqueness of the
> CertsPoliciesID key adds nothing. =A0
>
> Similarly, I am concerned to prevent improper combinations resulting from
> policy types. =A0No Insured party is going to carry two General Liability
> Policies. =A0If we try to attribute two different GL policies to the same
> Insured, either by assigning the two policies to the same Cert, or by
> assigning them to two different Certs that are in turn tied to the same
> Agreement, something is wrong.
>
> Thanks,
> oldblindpew
>
> "Piet Linden" wrote:
> > You could create a unique index on the combination of (CertID,
> > PolicyID) in the CertsPolicies table. =A0Nothing wrong with that. =A0Th=
en
> > if your CertsPoliciesID is an autonumber and set to be unique, you
> > should have everything, right?
> > .

Another way of doing the validation is in the BeforeInsert event of
the form.  You could do the checks there and if no rules are violated,
allow the insert.  Other than that, I'm out of ideas.
0
Piet
1/22/2010 12:55:09 AM
I think I see your point, although at first reading I was a bit dumbfounded.  
Conversation via email can be so difficult.  At first it sounded like you 
were surprised I was actually trying to design my application around business 
rules!  Further, that it was going to be up to my users, not my application, 
to enforce our business rules.  Finally, it sounded like you were saying that 
if any Access features proved helpful in this task, it would be purely 
accidental!

I believe you were actually saying that, right offhand, there is nothing I 
can do to the structure of my tables or their relationships to prevent 
unwanted records of the sorts I described.  Rather, these illegal operations 
must be prevented by traps in my code or by using data validation rules.

A perhaps easier example would be in retail sales.  Let's say we offered a 
product for sale with the condition: limit one per customer.  This would mean 
that for any instance of this product in the OrdersProducts join table, the 
Quantity would have to be limited to 1 each.  Also, we would have to prohibit 
multiple separate instances of the same product on the same order.  Further, 
we would have to prevent multiple orders for the same product from the same 
customer.  These kinds of constraints would not be enforced through table 
structure, except to the extent of making sure we placed a field to our 
Products table for flagging such products. 

Regards, 
OldBlindPew

"Jeff Boyce" wrote:

> It sounds like you are describing the "business rules" of your operation. 
> It wouldn't matter if you were using Access or Excel or paper and pencil, 
> those rules would apply (e.g., no customer carries more than one GL policy).
> 
> I'm not aware of any built-in business rule enforcer in MS Access.  I 
> believe you'll need to add the validation checks to enforce those rules.
> 
> In some of your situations, using a unique index on multiple fields could be 
> a way to use Access features to enforce your business rules ... but that's 
> just plain lucky!  You'll probably need to figure out some edits/validation 
> tests for your form, to prevent the users from doing something your business 
> doesn't permit.
> 
> Good luck!
> 
> Regards
> 
> Jeff Boyce
> Microsoft Access MVP
> 
> -- 
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
> 
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
> 
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
> 
> "oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message 
> news:CC037104-E3A4-4B28-BEFE-22E286558AA8@microsoft.com...
> > CertsPoliciesID is autonumber and therefore the unique primary key for the
> > junction table.  A unique index on the combination of CertID and PolicyID
> > would prevent redundant Cert/Policy pairs.
> >
> > But I am also concerned with redundant Agreement/Policy pairs.  It is
> > acceptable for an Agreement to have more than one Cert, but not that the 
> > same
> > Policy should appear on more than one of their Certs.  Enforcing 
> > Cert/Policy
> > uniqueness alone doesn't prevent this, and the uniqueness of the
> > CertsPoliciesID key adds nothing.
> >
> > Similarly, I am concerned to prevent improper combinations resulting from
> > policy types.  No Insured party is going to carry two General Liability
> > Policies.  If we try to attribute two different GL policies to the same
> > Insured, either by assigning the two policies to the same Cert, or by
> > assigning them to two different Certs that are in turn tied to the same
> > Agreement, something is wrong.
> >
> > Thanks,
> > oldblindpew
> >
> > "Piet Linden" wrote:
> >
> >> You could create a unique index on the combination of (CertID,
> >> PolicyID) in the CertsPolicies table.  Nothing wrong with that.  Then
> >> if your CertsPoliciesID is an autonumber and set to be unique, you
> >> should have everything, right?
> >> .
> >> 
> 
> 
> .
> 
0
Utf
1/22/2010 5:18:01 PM
Sorry if I gave you a start, there.  Yes, Access (and many other tools, 
including Excel, paper/pencil, etc.) can be used to handle business rules 
.... BUT! ... you have to do most of the work the handle the rules, using the 
features/functions of your tool.

Your example (retail sales, limit: one per customer) is excellent.  While 
there may be nothing built in to prevent many of those situations, you can 
certainly add in "traps" (?validation checks) to accomplish that.  Or, if 
you're lucky, using something like a unique index (again, a feature of your 
tool) might help you reinforce the business rule.  You still have to set the 
unique index, though!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message 
news:F27C1C3B-8F0B-457F-A1F0-293B37CB02CA@microsoft.com...
>I think I see your point, although at first reading I was a bit 
>dumbfounded.
> Conversation via email can be so difficult.  At first it sounded like you
> were surprised I was actually trying to design my application around 
> business
> rules!  Further, that it was going to be up to my users, not my 
> application,
> to enforce our business rules.  Finally, it sounded like you were saying 
> that
> if any Access features proved helpful in this task, it would be purely
> accidental!
>
> I believe you were actually saying that, right offhand, there is nothing I
> can do to the structure of my tables or their relationships to prevent
> unwanted records of the sorts I described.  Rather, these illegal 
> operations
> must be prevented by traps in my code or by using data validation rules.
>
> A perhaps easier example would be in retail sales.  Let's say we offered a
> product for sale with the condition: limit one per customer.  This would 
> mean
> that for any instance of this product in the OrdersProducts join table, 
> the
> Quantity would have to be limited to 1 each.  Also, we would have to 
> prohibit
> multiple separate instances of the same product on the same order. 
> Further,
> we would have to prevent multiple orders for the same product from the 
> same
> customer.  These kinds of constraints would not be enforced through table
> structure, except to the extent of making sure we placed a field to our
> Products table for flagging such products.
>
> Regards,
> OldBlindPew
>
> "Jeff Boyce" wrote:
>
>> It sounds like you are describing the "business rules" of your operation.
>> It wouldn't matter if you were using Access or Excel or paper and pencil,
>> those rules would apply (e.g., no customer carries more than one GL 
>> policy).
>>
>> I'm not aware of any built-in business rule enforcer in MS Access.  I
>> believe you'll need to add the validation checks to enforce those rules.
>>
>> In some of your situations, using a unique index on multiple fields could 
>> be
>> a way to use Access features to enforce your business rules ... but 
>> that's
>> just plain lucky!  You'll probably need to figure out some 
>> edits/validation
>> tests for your form, to prevent the users from doing something your 
>> business
>> doesn't permit.
>>
>> Good luck!
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Access MVP
>>
>> -- 
>> Disclaimer: This author may have received products and services mentioned
>> in this post. Mention and/or description of a product or service herein
>> does not constitute endorsement thereof.
>>
>> Any code or pseudocode included in this post is offered "as is", with no
>> guarantee as to suitability.
>>
>> You can thank the FTC of the USA for making this disclaimer
>> possible/necessary.
>>
>> "oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message
>> news:CC037104-E3A4-4B28-BEFE-22E286558AA8@microsoft.com...
>> > CertsPoliciesID is autonumber and therefore the unique primary key for 
>> > the
>> > junction table.  A unique index on the combination of CertID and 
>> > PolicyID
>> > would prevent redundant Cert/Policy pairs.
>> >
>> > But I am also concerned with redundant Agreement/Policy pairs.  It is
>> > acceptable for an Agreement to have more than one Cert, but not that 
>> > the
>> > same
>> > Policy should appear on more than one of their Certs.  Enforcing
>> > Cert/Policy
>> > uniqueness alone doesn't prevent this, and the uniqueness of the
>> > CertsPoliciesID key adds nothing.
>> >
>> > Similarly, I am concerned to prevent improper combinations resulting 
>> > from
>> > policy types.  No Insured party is going to carry two General Liability
>> > Policies.  If we try to attribute two different GL policies to the same
>> > Insured, either by assigning the two policies to the same Cert, or by
>> > assigning them to two different Certs that are in turn tied to the same
>> > Agreement, something is wrong.
>> >
>> > Thanks,
>> > oldblindpew
>> >
>> > "Piet Linden" wrote:
>> >
>> >> You could create a unique index on the combination of (CertID,
>> >> PolicyID) in the CertsPolicies table.  Nothing wrong with that.  Then
>> >> if your CertsPoliciesID is an autonumber and set to be unique, you
>> >> should have everything, right?
>> >> .
>> >>
>>
>>
>> .
>> 


0
Jeff
1/22/2010 9:32:21 PM
Not meaning to sound ungrateful, but the clear purpose of my original post 
was to ask for suggestions on how to use the features and functions of Access 
to implement certain specific business rules in my application.  We seem to 
have circumnavigated the barn only to arrive at the door.  Folks were eager 
to tell me I needed to use surrogate keys and to normalize my tables, but 
when I ask about some of the basic ramifications of those choices, I often 
get vague replies and shrugs of shoulders.

After drawing a blank here, I scabbed onto another recent thread entitled 
"Multi-Field Primary Keys", which deals with this same subject, and got a 
reply indicating there are indeed specific ways I can set up table 
relationships using multi-field indexes that might prove helpful.  The 
response was perhaps a bit over my head, and this in itself is probably the 
crux of the matter.

Designing an Access application is not easy, and there is only so much 
people can do on a voluntary basis, from a distance.  There are many ways to 
go about it, and even experts sometimes disagree on the best approach.  
Ultimately, designing an application is not something that can be done well 
by someone "driving from the back seat".  Please know that I appreciate your 
interest and help.  It appears I just need to continue slogging on in the 
slow and painful business of gaining wisdom by trial and error.

With Sincere Thanks,
OldBlindPew

"Jeff Boyce" wrote:

> Sorry if I gave you a start, there.  Yes, Access (and many other tools, 
> including Excel, paper/pencil, etc.) can be used to handle business rules 
> .... BUT! ... you have to do most of the work the handle the rules, using the 
> features/functions of your tool.
> 
> Your example (retail sales, limit: one per customer) is excellent.  While 
> there may be nothing built in to prevent many of those situations, you can 
> certainly add in "traps" (?validation checks) to accomplish that.  Or, if 
> you're lucky, using something like a unique index (again, a feature of your 
> tool) might help you reinforce the business rule.  You still have to set the 
> unique index, though!
> 
> Regards
> 
> Jeff Boyce
> Microsoft Access MVP
> 
> -- 
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
> 
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
> 
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
> 
> "oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message 
> news:F27C1C3B-8F0B-457F-A1F0-293B37CB02CA@microsoft.com...
> >I think I see your point, although at first reading I was a bit 
> >dumbfounded.
> > Conversation via email can be so difficult.  At first it sounded like you
> > were surprised I was actually trying to design my application around 
> > business
> > rules!  Further, that it was going to be up to my users, not my 
> > application,
> > to enforce our business rules.  Finally, it sounded like you were saying 
> > that
> > if any Access features proved helpful in this task, it would be purely
> > accidental!
> >
> > I believe you were actually saying that, right offhand, there is nothing I
> > can do to the structure of my tables or their relationships to prevent
> > unwanted records of the sorts I described.  Rather, these illegal 
> > operations
> > must be prevented by traps in my code or by using data validation rules.
> >
> > A perhaps easier example would be in retail sales.  Let's say we offered a
> > product for sale with the condition: limit one per customer.  This would 
> > mean
> > that for any instance of this product in the OrdersProducts join table, 
> > the
> > Quantity would have to be limited to 1 each.  Also, we would have to 
> > prohibit
> > multiple separate instances of the same product on the same order. 
> > Further,
> > we would have to prevent multiple orders for the same product from the 
> > same
> > customer.  These kinds of constraints would not be enforced through table
> > structure, except to the extent of making sure we placed a field to our
> > Products table for flagging such products.
> >
> > Regards,
> > OldBlindPew
> >
> > "Jeff Boyce" wrote:
> >
> >> It sounds like you are describing the "business rules" of your operation.
> >> It wouldn't matter if you were using Access or Excel or paper and pencil,
> >> those rules would apply (e.g., no customer carries more than one GL 
> >> policy).
> >>
> >> I'm not aware of any built-in business rule enforcer in MS Access.  I
> >> believe you'll need to add the validation checks to enforce those rules.
> >>
> >> In some of your situations, using a unique index on multiple fields could 
> >> be
> >> a way to use Access features to enforce your business rules ... but 
> >> that's
> >> just plain lucky!  You'll probably need to figure out some 
> >> edits/validation
> >> tests for your form, to prevent the users from doing something your 
> >> business
> >> doesn't permit.
> >>
> >> Good luck!
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Access MVP
> >>
> >> -- 
> >> Disclaimer: This author may have received products and services mentioned
> >> in this post. Mention and/or description of a product or service herein
> >> does not constitute endorsement thereof.
> >>
> >> Any code or pseudocode included in this post is offered "as is", with no
> >> guarantee as to suitability.
> >>
> >> You can thank the FTC of the USA for making this disclaimer
> >> possible/necessary.
> >>
> >> "oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message
> >> news:CC037104-E3A4-4B28-BEFE-22E286558AA8@microsoft.com...
> >> > CertsPoliciesID is autonumber and therefore the unique primary key for 
> >> > the
> >> > junction table.  A unique index on the combination of CertID and 
> >> > PolicyID
> >> > would prevent redundant Cert/Policy pairs.
> >> >
> >> > But I am also concerned with redundant Agreement/Policy pairs.  It is
> >> > acceptable for an Agreement to have more than one Cert, but not that 
> >> > the
> >> > same
> >> > Policy should appear on more than one of their Certs.  Enforcing
> >> > Cert/Policy
> >> > uniqueness alone doesn't prevent this, and the uniqueness of the
> >> > CertsPoliciesID key adds nothing.
> >> >
> >> > Similarly, I am concerned to prevent improper combinations resulting 
> >> > from
> >> > policy types.  No Insured party is going to carry two General Liability
> >> > Policies.  If we try to attribute two different GL policies to the same
> >> > Insured, either by assigning the two policies to the same Cert, or by
> >> > assigning them to two different Certs that are in turn tied to the same
> >> > Agreement, something is wrong.
> >> >
> >> > Thanks,
> >> > oldblindpew
> >> >
> >> > "Piet Linden" wrote:
> >> >
> >> >> You could create a unique index on the combination of (CertID,
> >> >> PolicyID) in the CertsPolicies table.  Nothing wrong with that.  Then
> >> >> if your CertsPoliciesID is an autonumber and set to be unique, you
> >> >> should have everything, right?
> >> >> .
> >> >>
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
1/25/2010 4:38:01 PM
It sounds as if you are on the right track in a lot of ways.  You are
absolutely correct that a surrogate key does not guarantee uniqueness of
other data in the record.  For that you need to use multi-field indexes
and/or form-level validation, as it seems you understand.

As a point of terminology, you may do better not to think of your linking
fields as surrogate keys.  The combination of "real world" fields is what
guarantees a unique record, as you clearly understand.  The surrogate key
just provides a one-field representation of the unique record, in the same
way an Employee # identifies the employee uniquely in a single field.  The
linking fields, while they may not be "real" (i.e. they are arbitrary numbers)
derive their values from the surrogate key of the parent table, rather than
being surrogate keys themselves.  In any case, they are not surrogate primary
keys.

As I understand, you could have for an Agreement the following:

AgreementID = 1000
  Cert 100
    Policy A
    Policy B

  Cert 200
    Policy C
    Policy D

You want to assure Policy A is not available for Cert 200 under the umbrella
of Agreement 1000, yes?  If so, you do not have an index available to enforce
that rule (unless I am missing something) because the Policy record does not
have direct access to AgreementID.  Instead, you need to use other
capabilities of Access to enforce that rule.

If the Policy is selected from a combo box on the subform bound to the
junction table, it should be possible to devise a query for the Row Source
that excludes policies already selected.  I do not have time to devise an
example just now, and in any case I am not yet skilled enough at queries to
be sure I am pointing you in the right direction.  That said, the logic would
probably be to select Policies from tblPolicy where PolicyID has not already
been used in the current Agreement record.

I wish I could be of more help.  I have been watching this thread to see what
is suggested, as the problem interests me and I do not have a ready solution,
but as it has been a while since there have been any postings I am jumping in
to steer you away from an index-based solution, which I do not think you will
find for this situation. I expect the solution is SQL-based, particularly if
you are setting the Row Source for a combo box.  A SQL string can be devised
in VBA to set the Row Source.  If you wish to experiment further you could
try devising a query that returns all of the Policies used for an agreement.
This would probably involve joining the junction table to tblCert to
tblAgreement, restricting the tblAgreement record to just one.  You could
hard-code the criteria for AgreementID for starters.  That is, for a given
record make note of the number, then use that number as the AgreementID
criteria.  You should be able to return a list of Policies used for that
Agreement.  If you can do that you have made a good start.

Best suggestion other than that is to start a new thread.  Perhaps the Forms
Programming group would be a good choice, or maybe the Queries group.  A new
thread is likely to attract more attention to one that is several responses
deep.


oldblindpew wrote:
>Not meaning to sound ungrateful, but the clear purpose of my original post 
>was to ask for suggestions on how to use the features and functions of Access 
>to implement certain specific business rules in my application.  We seem to 
>have circumnavigated the barn only to arrive at the door.  Folks were eager 
>to tell me I needed to use surrogate keys and to normalize my tables, but 
>when I ask about some of the basic ramifications of those choices, I often 
>get vague replies and shrugs of shoulders.
>
>After drawing a blank here, I scabbed onto another recent thread entitled 
>"Multi-Field Primary Keys", which deals with this same subject, and got a 
>reply indicating there are indeed specific ways I can set up table 
>relationships using multi-field indexes that might prove helpful.  The 
>response was perhaps a bit over my head, and this in itself is probably the 
>crux of the matter.
>
>Designing an Access application is not easy, and there is only so much 
>people can do on a voluntary basis, from a distance.  There are many ways to 
>go about it, and even experts sometimes disagree on the best approach.  
>Ultimately, designing an application is not something that can be done well 
>by someone "driving from the back seat".  Please know that I appreciate your 
>interest and help.  It appears I just need to continue slogging on in the 
>slow and painful business of gaining wisdom by trial and error.
>
>With Sincere Thanks,
>OldBlindPew
>
>> Sorry if I gave you a start, there.  Yes, Access (and many other tools, 
>> including Excel, paper/pencil, etc.) can be used to handle business rules 
>[quoted text clipped - 109 lines]
>> 
>> .

-- 
Message posted via http://www.accessmonster.com

0
BruceM
1/26/2010 3:38:13 PM
Thanks for your response.

Yes, your point is well taken, assuming I understood it correctly, that the 
term "surrogate" makes sense only when applied to a primary key.  (I had said 
in my OP that any field ending in "ID" was a surrogate key).  When the same 
field name appears in another table, it is no longer a surrogate key, but 
simply the foreign key linking that table to the parent table.  The fact is, 
I did not know how else to say what I was trying to say.  I didn't want to 
say "autonumber", for the same reason, i.e., only the primary key is truly 
autonumbered.

You are correct in your grasp of the relationships between Agreements, 
Certs, and Policies.  If Agreement 1000 has Cert 100, which in turn has 
Policies A and B, and if Agreement 1000 also has Cert 200, then we do not 
want Policy A to appear on Cert 200.  Nor would we want any other Policy OF 
THE SAME TYPE as Policy A to appear on Cert 200.

If things weren't already complicated enough, there is another level below 
Agreements, Certs, and Policies, which is the Coverage Details.  If I can 
figure out how to manage the first three levels, the fourth will hopefully 
not present any essential complications.  Well..., except for the fact as 
mentioned in another thread, that I will have to use one field to hold values 
of different data types.  I believe this means I will have to settle on one 
data type, say Number, and then add another field to flag the value for what 
it is really meant to be.

You are also correct about the Policy record not having direct access to the 
AgreementID.  Since the same Insured firm, with the same Policy, can enter 
into more than one Agreement, the AgreementID cannot be built into the Policy 
record.

The common fact about Agreements, Certs, and Policies, is the Insured firm.  
I have added the InsuredID to the Certs table.  When setting up a new Cert or 
Certs for a new Agreement, the user should be presented with a list of 
existing policies for the Insured; there should not be more than one of each 
PolicyType.  The user should be able to select policies for the new Cert, and 
see them change from "available" to "already present on another Cert".  Of 
course for the Insured's very first Agreement, his Policies would have to be 
added to the Policies table before they could be associated with any Cert.

Thanks Again,
OBP


"BruceM via AccessMonster.com" wrote:

> It sounds as if you are on the right track in a lot of ways.  You are
> absolutely correct that a surrogate key does not guarantee uniqueness of
> other data in the record.  For that you need to use multi-field indexes
> and/or form-level validation, as it seems you understand.
> 
> As a point of terminology, you may do better not to think of your linking
> fields as surrogate keys.  The combination of "real world" fields is what
> guarantees a unique record, as you clearly understand.  The surrogate key
> just provides a one-field representation of the unique record, in the same
> way an Employee # identifies the employee uniquely in a single field.  The
> linking fields, while they may not be "real" (i.e. they are arbitrary numbers)
> derive their values from the surrogate key of the parent table, rather than
> being surrogate keys themselves.  In any case, they are not surrogate primary
> keys.
> 
> As I understand, you could have for an Agreement the following:
> 
> AgreementID = 1000
>   Cert 100
>     Policy A
>     Policy B
> 
>   Cert 200
>     Policy C
>     Policy D
> 
> You want to assure Policy A is not available for Cert 200 under the umbrella
> of Agreement 1000, yes?  If so, you do not have an index available to enforce
> that rule (unless I am missing something) because the Policy record does not
> have direct access to AgreementID.  Instead, you need to use other
> capabilities of Access to enforce that rule.
> 
> If the Policy is selected from a combo box on the subform bound to the
> junction table, it should be possible to devise a query for the Row Source
> that excludes policies already selected.  I do not have time to devise an
> example just now, and in any case I am not yet skilled enough at queries to
> be sure I am pointing you in the right direction.  That said, the logic would
> probably be to select Policies from tblPolicy where PolicyID has not already
> been used in the current Agreement record.
> 
> I wish I could be of more help.  I have been watching this thread to see what
> is suggested, as the problem interests me and I do not have a ready solution,
> but as it has been a while since there have been any postings I am jumping in
> to steer you away from an index-based solution, which I do not think you will
> find for this situation. I expect the solution is SQL-based, particularly if
> you are setting the Row Source for a combo box.  A SQL string can be devised
> in VBA to set the Row Source.  If you wish to experiment further you could
> try devising a query that returns all of the Policies used for an agreement.
> This would probably involve joining the junction table to tblCert to
> tblAgreement, restricting the tblAgreement record to just one.  You could
> hard-code the criteria for AgreementID for starters.  That is, for a given
> record make note of the number, then use that number as the AgreementID
> criteria.  You should be able to return a list of Policies used for that
> Agreement.  If you can do that you have made a good start.
> 
> Best suggestion other than that is to start a new thread.  Perhaps the Forms
> Programming group would be a good choice, or maybe the Queries group.  A new
> thread is likely to attract more attention to one that is several responses
> deep.
> 
> 
> oldblindpew wrote:
> >Not meaning to sound ungrateful, but the clear purpose of my original post 
> >was to ask for suggestions on how to use the features and functions of Access 
> >to implement certain specific business rules in my application.  We seem to 
> >have circumnavigated the barn only to arrive at the door.  Folks were eager 
> >to tell me I needed to use surrogate keys and to normalize my tables, but 
> >when I ask about some of the basic ramifications of those choices, I often 
> >get vague replies and shrugs of shoulders.
> >
> >After drawing a blank here, I scabbed onto another recent thread entitled 
> >"Multi-Field Primary Keys", which deals with this same subject, and got a 
> >reply indicating there are indeed specific ways I can set up table 
> >relationships using multi-field indexes that might prove helpful.  The 
> >response was perhaps a bit over my head, and this in itself is probably the 
> >crux of the matter.
> >
> >Designing an Access application is not easy, and there is only so much 
> >people can do on a voluntary basis, from a distance.  There are many ways to 
> >go about it, and even experts sometimes disagree on the best approach.  
> >Ultimately, designing an application is not something that can be done well 
> >by someone "driving from the back seat".  Please know that I appreciate your 
> >interest and help.  It appears I just need to continue slogging on in the 
> >slow and painful business of gaining wisdom by trial and error.
> >
> >With Sincere Thanks,
> >OldBlindPew
> >
> >> Sorry if I gave you a start, there.  Yes, Access (and many other tools, 
> >> including Excel, paper/pencil, etc.) can be used to handle business rules 
> >[quoted text clipped - 109 lines]
> >> 
> >> .
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
1/26/2010 11:42:09 PM
It is not clear to me if Agreements, Certs and Policies are standardized in 
and of themselves. Presumably they are. It appears that an Agreement can 
have one or more Certs and a Cert can have one or more Policies. It appears 
that a Cert is (established ??) by someone identified by ProducerID and it 
appears that a Policy is provided by someone identified by ProviderID. 
Finally it appears that an Agreement is executed with someone identified by 
InsuredID. If all the above is true, consider this table structure:
TblProducerID
ProducerID
Producer fields ...

TblProvider
ProviderID
Provider fields ...

TblInsured
InsuredID
Insured fields ....

TblAgreement
AgreementID
Agreement fields ...

TblCert
CertID
Cert fields ...

TblPolicy
PolicyID
Policy fields ...

TblCertPolicy
CertPolicyID
CertID
PolicyID

TblAgreementCertPolicy
AgreementCertPolicyID
AgreementID
CertPolicyID

TblAgreementCertPolicyToInsured
AgreementCertPolicyToInsured
AgreementCertPolicyID
InsuredID
IssueDate
etc

This table structure gives you a record of a specific Agreement containing a 
specific set of certs where each Cert contains a specific set of policies 
issued to a specific Insured identified by InsuredID.

Steve
santus@penn.com






"oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message 
news:B7EBDB74-78E6-4E35-93C9-A2361F82C5AD@microsoft.com...
> It is my understanding that surrogate keys are generally recommended to
> ensure uniqueness of records.  Is it not true that using surrogate keys
> implies taking extra precautions to prevent duplicate records?  I mean, 
> with
> surrogate keys there is nothing to prevent the proliferation of multiple
> records all containing the same data, but each having a unique key.
>
> I would appreciate your help with this in the following context:
>
> AGREEMENTS
> AgrmtID (PK)
> InsuredID
> Agrmt fields.
>
> CERTS
> CertID (PK)
> AgrmtID
> ProducerID
> Cert fields.
>
> POLICIES
> PolicyID (PK)
> InsuredID
> PolicyTypeCode
> ProviderID
> Policy fields.
>
> CERTSPOLICIES
> CertsPoliciesID (PK)
> CertID
> PolicyID
>
> Note:  Any fieldname ending in "ID" is a surrogate key.
>
> An Agreement can have zero or more Certs; a Cert pertains to only one
> Agreement, so this is a one-to-many relationship.  Each Cert can have one 
> or
> more Policies; the same Policy can be on different Certs, so this is a
> many-to-many relationship, hence these two tables are joined by the
> CertsPolicies table.
>
> We don't want the same Policy to appear more than once on the same Cert. 
> I
> believe this can be accomplished fairly easily by setting up CertID and
> PolicyID as a multi-field unique index in the junction table.
>
> We also have to ensure that the user doesn't inadvertently relate any one
> Policy twice to the same Agreement through the use of a second Cert.  In
> other words, we do not want to see the same Policy on two different Certs 
> for
> the same Agreement.  How would this be accomplished?
>
> A fundamental assumption is that no Insured will ever have more than one
> Policy of a given type.  How would I guarantee that not more than one 
> Policy
> of a given type (PolicyType Code) ever appears on any Cert?  How would I
> guarantee the same thing for any two Certs assigned to the same Insured?
>
> Thanks,
> OldBlindPew
> 


0
Steve
1/27/2010 12:58:17 AM
Thanks for your reply, Steve.

All of your perceptions are correct.  

I assume by "standardized" you mean that each of these entities has a 
standard set of fields.  Everything is pretty well standardized until you get 
down to the level of Coverage Details, which I had not mentioned until my 
previous reply.  At this level, coverage details differ by policy type, and 
are more subject to change over time.  The Normalized approach would be to 
make a master list (table) of CoverageDetails, (or CoverageItems?), with a 
many-to-many relationship between Policies and CoverageDetails.

It is more usual to say a Cert is "issued" (vs. "established") by a Producer.

Your solution is more like what I expected to receive from the outset: some 
sort of multiplicity of join tables.

I have asked before about the wisdom of combining or splitting Firms by 
type.  Presently, all Firms are in a single table, with several Type fields 
to indicate what types of work the firm does.  This seems to be the preferred 
approach.  In your model, is there any reason why Insureds, Producers, and 
Providers couldn't all be in the same table of Firms?

BTW, does anyone know why it is that if I search this forum for OldBlindPew, 
I only get some of my threads?

Thanks again,
OBP

"Steve" wrote:

> It is not clear to me if Agreements, Certs and Policies are standardized in 
> and of themselves. Presumably they are. It appears that an Agreement can 
> have one or more Certs and a Cert can have one or more Policies. It appears 
> that a Cert is (established ??) by someone identified by ProducerID and it 
> appears that a Policy is provided by someone identified by ProviderID. 
> Finally it appears that an Agreement is executed with someone identified by 
> InsuredID. If all the above is true, consider this table structure:
> TblProducerID
> ProducerID
> Producer fields ...
> 
> TblProvider
> ProviderID
> Provider fields ...
> 
> TblInsured
> InsuredID
> Insured fields ....
> 
> TblAgreement
> AgreementID
> Agreement fields ...
> 
> TblCert
> CertID
> Cert fields ...
> 
> TblPolicy
> PolicyID
> Policy fields ...
> 
> TblCertPolicy
> CertPolicyID
> CertID
> PolicyID
> 
> TblAgreementCertPolicy
> AgreementCertPolicyID
> AgreementID
> CertPolicyID
> 
> TblAgreementCertPolicyToInsured
> AgreementCertPolicyToInsured
> AgreementCertPolicyID
> InsuredID
> IssueDate
> etc
> 
> This table structure gives you a record of a specific Agreement containing a 
> specific set of certs where each Cert contains a specific set of policies 
> issued to a specific Insured identified by InsuredID.
> 
> Steve
> santus@penn.com
> 
> 
> 
> 
> 
> 
> "oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message 
> news:B7EBDB74-78E6-4E35-93C9-A2361F82C5AD@microsoft.com...
> > It is my understanding that surrogate keys are generally recommended to
> > ensure uniqueness of records.  Is it not true that using surrogate keys
> > implies taking extra precautions to prevent duplicate records?  I mean, 
> > with
> > surrogate keys there is nothing to prevent the proliferation of multiple
> > records all containing the same data, but each having a unique key.
> >
> > I would appreciate your help with this in the following context:
> >
> > AGREEMENTS
> > AgrmtID (PK)
> > InsuredID
> > Agrmt fields.
> >
> > CERTS
> > CertID (PK)
> > AgrmtID
> > ProducerID
> > Cert fields.
> >
> > POLICIES
> > PolicyID (PK)
> > InsuredID
> > PolicyTypeCode
> > ProviderID
> > Policy fields.
> >
> > CERTSPOLICIES
> > CertsPoliciesID (PK)
> > CertID
> > PolicyID
> >
> > Note:  Any fieldname ending in "ID" is a surrogate key.
> >
> > An Agreement can have zero or more Certs; a Cert pertains to only one
> > Agreement, so this is a one-to-many relationship.  Each Cert can have one 
> > or
> > more Policies; the same Policy can be on different Certs, so this is a
> > many-to-many relationship, hence these two tables are joined by the
> > CertsPolicies table.
> >
> > We don't want the same Policy to appear more than once on the same Cert. 
> > I
> > believe this can be accomplished fairly easily by setting up CertID and
> > PolicyID as a multi-field unique index in the junction table.
> >
> > We also have to ensure that the user doesn't inadvertently relate any one
> > Policy twice to the same Agreement through the use of a second Cert.  In
> > other words, we do not want to see the same Policy on two different Certs 
> > for
> > the same Agreement.  How would this be accomplished?
> >
> > A fundamental assumption is that no Insured will ever have more than one
> > Policy of a given type.  How would I guarantee that not more than one 
> > Policy
> > of a given type (PolicyType Code) ever appears on any Cert?  How would I
> > guarantee the same thing for any two Certs assigned to the same Insured?
> >
> > Thanks,
> > OldBlindPew
> > 
> 
> 
> .
> 
0
Utf
1/27/2010 4:05:01 PM
<<The Normalized approach would be to make a master list (table) of 
CoverageDetails, (or CoverageItems?), with a many-to-many relationship 
between Policies and CoverageDetails.>>
Yes! Your tables would look like:
TblPolicy
PolicyID
Policy fields ...

TblCoverageDetail
CoverageDetailID
CoverageDetail fields ....

TblPolicyCoverageDetail
PolicyCoverageDetailID
PolicyID
CoverageDetailID

When coverage details of a policy change, you need to add the new details to
TblCoverageDetail. This changes a policy so you also need to add a new 
record(s) to
TblPolicyCoverageDetail.

Using the tables I previously suggested, you can get the coverage details of 
an agreement in a query that includes TblPolicyCoverageDetail.


<<In your model, is there any reason why Insureds, Producers, and Providers 
couldn't all be in the same table of Firms?>>

If ALL (not most!!!) are firms with the same firm fields; yes, you can 
combine them into a TblFirm.

Steve
santus@penn.com



"oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message 
news:7DDD7C16-C534-417A-9FB0-CC5C84E8F49C@microsoft.com...
> Thanks for your reply, Steve.
>
> All of your perceptions are correct.
>
> I assume by "standardized" you mean that each of these entities has a
> standard set of fields.  Everything is pretty well standardized until you 
> get
> down to the level of Coverage Details, which I had not mentioned until my
> previous reply.  At this level, coverage details differ by policy type, 
> and
> are more subject to change over time.  The Normalized approach would be to
> make a master list (table) of CoverageDetails, (or CoverageItems?), with a
> many-to-many relationship between Policies and CoverageDetails.
>
> It is more usual to say a Cert is "issued" (vs. "established") by a 
> Producer.
>
> Your solution is more like what I expected to receive from the outset: 
> some
> sort of multiplicity of join tables.
>
> I have asked before about the wisdom of combining or splitting Firms by
> type.  Presently, all Firms are in a single table, with several Type 
> fields
> to indicate what types of work the firm does.  This seems to be the 
> preferred
> approach.  In your model, is there any reason why Insureds, Producers, and
> Providers couldn't all be in the same table of Firms?
>
> BTW, does anyone know why it is that if I search this forum for 
> OldBlindPew,
> I only get some of my threads?
>
> Thanks again,
> OBP
>
> "Steve" wrote:
>
>> It is not clear to me if Agreements, Certs and Policies are standardized 
>> in
>> and of themselves. Presumably they are. It appears that an Agreement can
>> have one or more Certs and a Cert can have one or more Policies. It 
>> appears
>> that a Cert is (established ??) by someone identified by ProducerID and 
>> it
>> appears that a Policy is provided by someone identified by ProviderID.
>> Finally it appears that an Agreement is executed with someone identified 
>> by
>> InsuredID. If all the above is true, consider this table structure:
>> TblProducerID
>> ProducerID
>> Producer fields ...
>>
>> TblProvider
>> ProviderID
>> Provider fields ...
>>
>> TblInsured
>> InsuredID
>> Insured fields ....
>>
>> TblAgreement
>> AgreementID
>> Agreement fields ...
>>
>> TblCert
>> CertID
>> Cert fields ...
>>
>> TblPolicy
>> PolicyID
>> Policy fields ...
>>
>> TblCertPolicy
>> CertPolicyID
>> CertID
>> PolicyID
>>
>> TblAgreementCertPolicy
>> AgreementCertPolicyID
>> AgreementID
>> CertPolicyID
>>
>> TblAgreementCertPolicyToInsured
>> AgreementCertPolicyToInsured
>> AgreementCertPolicyID
>> InsuredID
>> IssueDate
>> etc
>>
>> This table structure gives you a record of a specific Agreement 
>> containing a
>> specific set of certs where each Cert contains a specific set of policies
>> issued to a specific Insured identified by InsuredID.
>>
>> Steve
>> santus@penn.com
>>
>>
>>
>>
>>
>>
>> "oldblindpew" <oldblindpew@discussions.microsoft.com> wrote in message
>> news:B7EBDB74-78E6-4E35-93C9-A2361F82C5AD@microsoft.com...
>> > It is my understanding that surrogate keys are generally recommended to
>> > ensure uniqueness of records.  Is it not true that using surrogate keys
>> > implies taking extra precautions to prevent duplicate records?  I mean,
>> > with
>> > surrogate keys there is nothing to prevent the proliferation of 
>> > multiple
>> > records all containing the same data, but each having a unique key.
>> >
>> > I would appreciate your help with this in the following context:
>> >
>> > AGREEMENTS
>> > AgrmtID (PK)
>> > InsuredID
>> > Agrmt fields.
>> >
>> > CERTS
>> > CertID (PK)
>> > AgrmtID
>> > ProducerID
>> > Cert fields.
>> >
>> > POLICIES
>> > PolicyID (PK)
>> > InsuredID
>> > PolicyTypeCode
>> > ProviderID
>> > Policy fields.
>> >
>> > CERTSPOLICIES
>> > CertsPoliciesID (PK)
>> > CertID
>> > PolicyID
>> >
>> > Note:  Any fieldname ending in "ID" is a surrogate key.
>> >
>> > An Agreement can have zero or more Certs; a Cert pertains to only one
>> > Agreement, so this is a one-to-many relationship.  Each Cert can have 
>> > one
>> > or
>> > more Policies; the same Policy can be on different Certs, so this is a
>> > many-to-many relationship, hence these two tables are joined by the
>> > CertsPolicies table.
>> >
>> > We don't want the same Policy to appear more than once on the same 
>> > Cert.
>> > I
>> > believe this can be accomplished fairly easily by setting up CertID and
>> > PolicyID as a multi-field unique index in the junction table.
>> >
>> > We also have to ensure that the user doesn't inadvertently relate any 
>> > one
>> > Policy twice to the same Agreement through the use of a second Cert. 
>> > In
>> > other words, we do not want to see the same Policy on two different 
>> > Certs
>> > for
>> > the same Agreement.  How would this be accomplished?
>> >
>> > A fundamental assumption is that no Insured will ever have more than 
>> > one
>> > Policy of a given type.  How would I guarantee that not more than one
>> > Policy
>> > of a given type (PolicyType Code) ever appears on any Cert?  How would 
>> > I
>> > guarantee the same thing for any two Certs assigned to the same 
>> > Insured?
>> >
>> > Thanks,
>> > OldBlindPew
>> >
>>
>>
>> .
>> 


0
Steve
1/27/2010 7:58:31 PM
I will reply in this part of the thread, as it is the most recent.  It's
somewhat difficult to say whether firms should be in one table or split into
several.  As an example, Customers and Vendors are both companies with
addresses, etc., but are likely to contain substantially different types of
data, so it makes sense in most cases that they be in the same table.  On the
other hand, supply vendors and service vendors are both vendors, even though
you may need certain fields for one and not the other (shipping information
may not apply to a service vendor, for instance).  I part company here with
Steve's insistence that *all* of the fields need to be the same, but I think
it is true they should be substantially similar.

I do not really understand the concepts of Producer, Provider, and Insured as
they apply to your situation.  If the Insured is the customer and the other
two are suppliers, the tables should be split up to that extent, I would
think.  In any case, if you are using a combo or list box you probably want
to limit the list only to those who could by Providers or Producers or
Insureds.

Regarding the forums search, if you are using the Microsoft interface it can
be clunky.  A Google Groups search is more likely to return the information.

oldblindpew wrote:
>Thanks for your reply, Steve.
>
>All of your perceptions are correct.  
>
>I assume by "standardized" you mean that each of these entities has a 
>standard set of fields.  Everything is pretty well standardized until you get 
>down to the level of Coverage Details, which I had not mentioned until my 
>previous reply.  At this level, coverage details differ by policy type, and 
>are more subject to change over time.  The Normalized approach would be to 
>make a master list (table) of CoverageDetails, (or CoverageItems?), with a 
>many-to-many relationship between Policies and CoverageDetails.
>
>It is more usual to say a Cert is "issued" (vs. "established") by a Producer.
>
>Your solution is more like what I expected to receive from the outset: some 
>sort of multiplicity of join tables.
>
>I have asked before about the wisdom of combining or splitting Firms by 
>type.  Presently, all Firms are in a single table, with several Type fields 
>to indicate what types of work the firm does.  This seems to be the preferred 
>approach.  In your model, is there any reason why Insureds, Producers, and 
>Providers couldn't all be in the same table of Firms?
>
>BTW, does anyone know why it is that if I search this forum for OldBlindPew, 
>I only get some of my threads?
>
>Thanks again,
>OBP
>
>> It is not clear to me if Agreements, Certs and Policies are standardized in 
>> and of themselves. Presumably they are. It appears that an Agreement can 
>[quoted text clipped - 113 lines]
>> 
>> .

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201001/1

0
BruceM
1/28/2010 6:14:10 PM
Reply:

Similar Artilces:

Deleting Duplicate Records 03-20-08
Hi, I've got my duplicates records query and now I want to delete these 1100 dups in my table of 12,000+ records. Can you please explain how I can easily delete these duplicate records in my table now that I have found them in my query. Thanks very much. Dave dberger16@comcast.net Use a subquery to identify which duplicate records to delete, and which one to keep. This example assumes: - a table named Table1; - a primary key named ID; - "duplicate" is defined as same Company, Address, and City; - you want to keep the lowest ID value. DELETE FROM Table1 WHERE ID <>...

Time difference in created records.
Hi, When I create a note or record in CRM through the client, when i take the time at record creation, and after it has been saved to the database, the properties show that the record was created two hours earlier. Both the client and the server are in the same time zone, and both their clocks are identical. Any ideas? Thanks Is the server time set correctly? (Did somebody screw up daylight savings time, and go the wrong way?) >-----Original Message----- >Hi, > >When I create a note or record in CRM through the client, >when i take the time at record creation, and a...

subform displays no records when its recordsource is a query
I have a subform based on a query; for simplicity the query is just SELECT * FROM [MyTable]. When I test this subform standalone (i.e. not nested in the parent form where it will eventually need to go), it correctly queries the underlying table and I'm able to navigate to all the records in that table. Problem: when I drop this subform into it's parent form, it doesn't show any records! I've disabled all the master/child filtering options on the subform control, but it doesn't seem to make a difference. The subform shows no records. Here's the kicker: when ...

Restated: "Fields are expensive, records are cheap"
Hi, First let me apolozie for the empty question below. I hit the Post button my mistake and it posted a blank question. Sorry. Hi, I am restating my question because based upon the responses I receive I obviously stated my question incorrectly. So please let me try again. Hopefully I will be a bit more sucessful this time around. In a previous question, I stated that I added 30 fields the membership table (one of many in the system). The initial implementation was SO successful, that the user requested quite a few enhancements resulting in the in a HUGE increas...

Logging systems admin login activity at record-level on SQL2000
Can a single user account be isolated to cature record-level logging when the account is used? We use 8.00 gp44 on SQL2000. I heard from some SQL2000 experts that a script can be created that can do this, but it was a different application. Does anyone know if this is technically feasible with GP? Hi Russ, We accomplished this for another company using triggers SQL jobs. Feel free to email me snook@gofastpath.com and I can explains further details. "Russ D." wrote: > Can a single user account be isolated to cature record-level logging when the > account is used? We...

Help with sql which counts records
Could someone help me to extend this sql to include: 1. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 2. a count of txtmulti where the field is a YES/NO field and I want a count of where the answer is YES 3. a count of txtsole where the field is a YES/NO field and I want a count of where the answer is YES 4. a count of txtnbrparts where the field is a number field and I want a count of where the answer greater than 1 I think I need to extend the WHERE statement? SELECT tblhvdealspt1.txtablhybrid, Count(*) AS totals, tblhvdealspt...

Store similar types of records all in one table or separate tables?
Suppose you want to have four different types of records. Each of these records have numerous fields in common, and a few fields that are unique to each type of record. Most of the fields are related to other tables, but a few are simply text fields or Booleans. Which is better?: Keeping track of all 3 types of records in a single table. Or Creating separate tables for each type of record. Is one solution clearly better or is it just a matter of opinion? Thanks in advance, Tom On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit <tryit.ca@gmail.com> w...

What records does HITB save in SEE30303?
In the 10.0HITB.pdf manual, it says the following: 'The information that prints on the report is stored in the new HITB Inventory Transaction History Detail (SEE30303) table. All inventory transactions and cost adjustments will create records in the SEE30303 table.' Does this mean *all* inventory transactions will be written to this table going forward after the reset is run (ending up with a duplication between this table and the normal inventory transaction tables)? Or, is it only used to store the records for the report you're currently running? I'd hate to think this...

Want to create SQL query for updating records
I am using the Query below to extract data from a table: select refnbr,replace(reverse(left(reverse(url),charindex('%',reverse(url))-3)),'%',''),url from xep_aptran where url <> '' order by refnbr The data that is extracted when I run the query is as below Refnbr (No Coloum Name) URL 000199 CAON00000013 https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000013 000200 CAON00000011 https://www.ineedafile.com/MEDCO/popup/view.asp?TRMD8423%09sj60sdj2c%09CAON00000011 What I really want to ...

excel 2000 cannot read record 100 in SYLK files it itself writes
As in the subject, the problem is as follows: If Excel 2000 writes a sheet in SYLK format, it has problems opening the = ..SLK file. The message is: cannot read record 100 This happens even with an empty sheet. The latest updates are all installed. Excel runs on Windows 2000. How to proceed? Jaap. I've never used a .slk file in real life, but I just created a test file with "asdf" in A1:E200. I saved it as a .slk file, closed it and reopened it. I got the same error along with 103, 106, and 109. But the data looked ok. I put some unique data in those rows and did it aga...

Closing Balance figure to be carried as Opening Balance in a new record 02-14-10
I am working on a database for Accounts in which I need to carry the closing balance of one record as the opening balance of the next record. both the fields are "Number" type. Like MCCB in record No.x or y or z should be carried to the next new record as the opening balance fieldname MCOB. This carrying of CB as OB should take effect while working continuously, or after a closing the form and reloading after a one or two or when opened the next day or next working day. I want a solution to this problem of mine. Please help me -- Message posted via AccessMonster.com...

Email Current Record from Form
I would like to email only the record currently being viewed within the form. The ideal way for this to work is to click a button on the form to email the current record only to the specified address within the form. Can anyone help with this? SendObject can email a report. Unlike OpenReport, it does not have a WhereCondition, so the problem becomes, "How do I limit SendObject to just the current record?" One approach is to use a string variable to act as the filter for the report. You set this to the filter you want before you open the report (with SendObject), and in the r...

Automatic page scrolling to the last record
Hi, I wonder if someone can help me please. I'm using the code below on a subform where upon opening the form automatically scrolls to the last page on the form. The code is set on the 'On Current' event. 'DoCmd.GoToRecord , , acNewRec' The problem is, is that say for example I need to check a previously keyed record on the form it will not allow me to click on any field within that record. Has anyone any ideas please. Many thanks and regards Chris -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200801/1 You will...

Search all table records from filtered form
Hi. I'm hoping someone can help me with this search question... MT_Position / PK = PositionID MT_Candidate / PK = CandidateID MT_Interview / PK = FirstName, LastName, Email / FK = PositionID, CandidateID I have a tabbed form. On tab 1 is the main form (recordsource = MT_Position). On tab 2 is a sub form (recordsource = QryCandidateInterview). The subform displays all the Candidates who have applied to 1 Position. I would like to include on this subform a searable list box with all the Candidates (not just those related to the position). How can I do this? Any search I've b...

Avoid Printing Duplicates
I am running Outlook 2000 SP-3. I have several thousand emails in different folders that I need to print for a court case. In many cases, I filed a copy of the same message in two different folders. Is there an easy way to avoid printing the duplicate messages? Here is what I have tried: --Copy the messages from one folder into another temporary folder, and then export to a .pst file. Delete all the messages from the temp folder. --Copy the messages from a second folder to the temp folder. Export to the same .pst file, but click the "do not export duplicates" option. This d...

Move through records with self made buttons only
I'm currently busy with the following: I have made the navigation buttons on my forms invisible, and added my own buttons on the form instead. Future users (without any knowledge of access) can scroll between forms with these buttons. The problem I'm facing now: I can still navigate between records without using my buttons, using the scroll wheel on the mouse, as well as the tab-button on my keyboard. This can be distracting for future users of the form. My question: How do I make sure that navigating between records is ONLY possible using my self made buttons? Assumin...

Is there a next record in Publisher?
I am trying to mail merge in Publisher and I would like to have different merge areas on a page. I would like to have 4 different sets of information in 4 different sections. I am not able to see more than 1 set of information. In Word you can use «Next Record» to go to the next set of information. Does Publisher have a «Next Record» or something to indicate grab the next set of data, then grab the next set of data? I like Publisher for its freedom to have such a non-traditional page layout which Word does not. "EG Scott" wrote: > I am trying ...

MX Records not showing correctly
Could someone explain why I am getting the following and what I can do to resolve this problem. *** computername.domain.com can't find query=mx: Non-existent domain > query=mx domain.com Server: domain.com Addresses: 192.168.1.10, 192.168.0.10 *** domain.com can't find query=mx: Non-existent domain > query=mx 192.168.0.10 Server: [192.168.0.10] Address: 192.168.0.10 *** 192.168.0.10 can't find query=mx: Non-existent domain > Thanks for you help Terry You don't need MX records on internal DNS. What exactly are you trying to do? If you are trying to...

how to split records in Access report
Hi, I was wondering if anyone here might be able to help me here. This problem has been driving me up the wall. I have a report that shows records in it. Some of the records are split namely starting on one page finishing on the next. For example - Maintenance plan - Irrigation monitoring plan where "- Maintenance plan" is all one field and yet the "- Irrigation monitoring plan" would be printed at the top of the next page if this happened to print at the bottom of a page. What should happen if the two lines don't fit is that both lines get moved to the next page. ...

How To Send Record via email
I would like to know how best to send a few fields a newly entered data record in an Access Table via Outlook email. For example I would like the operator to enter/fill in a few fields in a newly created record. I have a command button which reads "Submit" and when they click that i would like to open a new email, populate the email with a few of the fields (say field 1, 2 and 3) and the address populated with an email address automatically (in the code). I have a command button with an Event proceedure started but using the wizard i can only get the code below to ...

Systematically add Records Through RecordsetClone
Hello, I was looking for some help. I am trying to add records based on the 'Start_End_Difference' field. I have 183 records in a MS Access 2003 in a form. I want to change the 'Start_End_Difference' to ('Start_End_Difference'-1) until 0. Basically I have data starting and ending in different months. I'm trying to graph data new to the month and carried over from the previous (stacked chart) The code only goes through the first record. It successfully creates 2 records but then stops. It appears that it is going to a new record for some reason, eventually g...

Open DB on LAST record, not FIRST
Hi, Currently when I enter one of my data entry forms, it always opens on the first record in the table, that being 1 (set by primary key). Is there anyway that I can set the form to open on the LAST inputted record, instead of the first? What does "last" mean to you? Recognize that in a table, there is no order: tables are unordered sacks of data, where Access puts the rows wherever they fit best. If you can define what "last" means, create a query that has an appropriate ORDER BY clause, and use that query as the form's RecordSource rather than the table. -...

Record Unavailable When Creating New Activity Offline
When working with my offline data, I will go into a Contact Record that I have access to offline. I will create a new activity, save it and then keep going. Sporadically when I create an activity for that contact and hit save I will get the Record Unvailalbe error. When I close the window I can create the same exact activity again and it will save just fine. I am working offline with the Outlook client, I am the onwer of the Contact object. The activity can be any type, Phone, Email, Appt. I enabled tracing and was able to catch this in the logs. Here is the error that appears i...

Field in Relationship Loses BackStyle When Filtering Records
Access 2007. This is driving me crazy and I'll have a difficult time explaining, but here goes. Using a one-top-many between 2 fields: tblProductType.ProductTypeID (Left Column) tblHomeInv.ProductTypeID (Right Column) I have several other relationships with the same results. The odd one out is a relationship that I deleted and made several other changes to and this anomaly doesn't occur now. I haven't the slightest idea what I did to correct the problem. Inpatients got the best of me. The ProductTypeID in tblProductType is set up as a cbo to select the Product type forth record. h...

Need time created of last record in the record set
I have a form that has a continous sub form on it. I want to disallow edits and additions to the main form based on the timestamp value of the last record entered in the sub form. I have code to calc the time, If DateDiff ("h", Me.fldTimeStamp, Now) >=2) Then,,, but I don't know how to get the TimeStampvalue from the last record entered. (the table has a field named fldTimeStamp with the default value set to Now()) .. Any help would be greatly appreciated. Thanks, Rob -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding...