No duplicates

  • Thread starter Thread starter dbl
  • Start date Start date
D

dbl

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".
 
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
 
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.
 
Thanks Bruce I will give that a try.

Bob
Bruce Rusk said:
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.
Click to expand...
 
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
 
Or, if one policy can have more than one person on it, three tables:
tblPeople
PeopleID (PK)
LName
FName
Address
---- other fields related to people

tblPolicies
PolicyNumber (PK)
---other fields related to policies.
Click to expand...

tblPolicyPeople
PolicyNumber
PeopleID
with PK of the two fields above
 
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
 
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 am really stuck with this one if I change the primary key on the
table
Click to expand...

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
 
Back
Top