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