Prev: como corregir el error:No se reconoce el formato de base de datos
Next: How to change the path to a split database file
From: oldblindpew on 22 Jan 2010 12:18 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(a)discussions.microsoft.com> wrote in message > news:CC037104-E3A4-4B28-BEFE-22E286558AA8(a)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? > >> . > >> > > > . >
From: Jeff Boyce on 22 Jan 2010 16:32 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(a)discussions.microsoft.com> wrote in message news:F27C1C3B-8F0B-457F-A1F0-293B37CB02CA(a)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(a)discussions.microsoft.com> wrote in message >> news:CC037104-E3A4-4B28-BEFE-22E286558AA8(a)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? >> >> . >> >> >> >> >> . >>
From: oldblindpew on 25 Jan 2010 11:38 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(a)discussions.microsoft.com> wrote in message > news:F27C1C3B-8F0B-457F-A1F0-293B37CB02CA(a)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(a)discussions.microsoft.com> wrote in message > >> news:CC037104-E3A4-4B28-BEFE-22E286558AA8(a)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? > >> >> . > >> >> > >> > >> > >> . > >> > > > . >
From: BruceM via AccessMonster.com on 26 Jan 2010 10:38 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
From: oldblindpew on 26 Jan 2010 18:42 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 > > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: como corregir el error:No se reconoce el formato de base de datos Next: How to change the path to a split database file |