 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / July 2005No duplicates
Hi I have a table which has the fields PolicyHolder (Primary Key) and PolicyNumber plus other fields. I need to allow the same PolicyHolders name to be used but only to one policyNumber. i.e. Smith policy number 1234, Smith policy number 4321 and so on but never Smith with the same policy number. But I also need the policy number to be able to have more that one name attached to it i.e. Smith Policy number 1234, Jones Policy number 1234, White Policy number 1234 but never the same name twice with the same policy number. I hope that explains what I need, but how do I set it up? setting the field to Yes no duplicates or Yes duplicates ok doesn't work. Or does this need to be coded into the input form? If it does how do I do it? Any help would be very much appreciated. Bob Then yo uneed a combined key or a compound key. Use the HELP fiels for details on how to build a key composed of two fields. I think the terminology in help will be "compound key".  SignatureRick B > Hi I have a table which has the fields PolicyHolder (Primary Key) and > PolicyNumber plus other fields. I need to allow the same PolicyHolders name [quoted text clipped - 15 lines] > > Bob Rick setting 2 primary keys produces the following error This error can appear if: You have exceeded the maximum number of columns allowed in a table or the maximum number of locks for a single file. The indexed property of a field was changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data is already present in the table. Bob > Then yo uneed a combined key or a compound key. Use the HELP fiels for > details on how to build a key composed of two fields. I think the [quoted text clipped - 24 lines] >> >> Bob You probably already have some duplicate data in the table. You can find it by running the Find Duplicates query wizard, and deciding what to do with the duplicate data (deleting/combining records, etc). Then try creating the index. Overall, though, it really sounds as if your database should be designed a little differently: you should probably have separate tables for policies and policy holders, and a third table to create links between them with relationship to the other two tables. This is the table that would have the no duplicates index on it. > Rick setting 2 primary keys produces the following error > [quoted text clipped - 37 lines] >>> >>> Bob Thanks Bruce I will give that a try. Bob > You probably already have some duplicate data in the table. You can find > it by running the Find Duplicates query wizard, and deciding what to do [quoted text clipped - 48 lines] >>>> >>>> Bob I did not say to set two primary keys. I said to seet one COMPOUND key. SignatureRick B > Rick setting 2 primary keys produces the following error > [quoted text clipped - 36 lines] > >> > >> Bob INDEX is probably a better word, than key. SignatureRick B > Rick setting 2 primary keys produces the following error > [quoted text clipped - 36 lines] > >> > >> Bob Rick "INDEX" is the correct word Index Prevent Entry of Duplicate Values to be precise This does exactly what I need. Thank you very much for your help. Regards Bob > INDEX is probably a better word, than key. > [quoted text clipped - 43 lines] >> >> >> >> Bob Rick this mod locks all the records I can only view but not up date or edit any idea's? it works in the table only. Bob > Rick "INDEX" is the correct word > Index Prevent Entry of Duplicate Values to be precise [quoted text clipped - 54 lines] >>> >> >>> >> Bob Since one person can have many policies, you need to have 2 tables. tblPeople PeopleID (PK) LName FName Address ---- other fields related to people tblPolicies PolicyNumber (PK) PeopleID (FK) ---other fields related to policies. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Access Junkie List: http://home.bendbroadband.com/conradsystems/accessjunkie.html > Hi I have a table which has the fields PolicyHolder (Primary Key) and > PolicyNumber plus other fields. I need to allow the same PolicyHolders [quoted text clipped - 15 lines] > > Bob Or, if one policy can have more than one person on it, three tables: > tblPeople > PeopleID (PK) [quoted text clipped - 6 lines] > PolicyNumber (PK) > ---other fields related to policies. tblPolicyPeople PolicyNumber PeopleID with PK of the two fields above > Since one person can have many policies, you need to have 2 tables. >> Hi I have a table which has the fields PolicyHolder (Primary Key) and >> PolicyNumber plus other fields. I need to allow the same PolicyHolders [quoted text clipped - 15 lines] >> >> Bob Hi I am really stuck with this one if I change the primary key on the table it locks the db so that you can only view. Changing the table design seems to be very major, I have tried but cannot get the rest of the db to work after wards. How do I remove the primary key without it locking the db all I can do is view its as if the property had been set to read only. But I have checked this and isn't. Bob > Hi I have a table which has the fields PolicyHolder (Primary Key) and > PolicyNumber plus other fields. I need to allow the same PolicyHolders [quoted text clipped - 15 lines] > > Bob >> Hi I have a table which has the fields PolicyHolder (Primary Key) and >> PolicyNumber plus other fields. I need to allow the same >> PolicyHolders name to be used but only to one policyNumber. >> But I also need the policy number to be able to have more that one >> name attached to it i.e. Smith Policy number 1234, Jones Policy >> number 1234, White Policy number 1234 but never the same name twice >> with the same policy number. > Hi I am really stuck with this one if I change the primary key on the > table It sounds to me as though you should be walking right away from the PC and thinking hard about your tables design. From what we have already, you seem to need three tables instead of this one: People(*CustomerID, name, address, etc) Policies(*PolicyNumber, PolicyType, FinalValue, MaturityDate, etc) Allocations(*CustomerID, *PolicyNumber, DateSigned, VerifiedBy, etc) The two stars in the Allocations table represents ONE primary key made up of the TWO fields (each of which are foreign keys referencing their own tables). FWIW, this is a straightforward many-to-many relationship. Hope that helps Tim F Thanks Tim it looks like I will have to start over. Bob >>> Hi I have a table which has the fields PolicyHolder (Primary Key) and >>> PolicyNumber plus other fields. I need to allow the same [quoted text clipped - 27 lines] > > Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.