No duplicates

[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

 

[Rick B]

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".

 

[dbl]

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

 

[Bruce Rusk]

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.

 

[dbl]

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
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 B]

I did not say to set two primary keys. I said to seet one COMPOUND key.

 

[Rick B]

INDEX is probably a better word, than key.

 

[Lynn Trapp]

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.

--
Lynn 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

 

[dbl]

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

 

[Bruce Rusk]

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.

tblPolicyPeople
PolicyNumber
PeopleID
with PK of the two fields above

 

[dbl]

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

 

[dbl]

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

 

[Tim Ferguson]

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

 

[dbl]

Thanks Tim it looks like I will have to start over.
Bob