one-to-many relationships

[Ava]

Hello,

On my database, I have a table named "Prospects" (one)
and another named "Opprotunity" (many). I have always
assumed that when you have a one-to-many relationship
like this, you can have more than one Opprotunity entered
for every Prospect. When I try to test this theory out on
my DB, Access won't allow me to create more than one
Opprotunity per Propect. Am I doing something wrong? What
should I do?

Thank you so much,
Ava

 

[Ava]

Im sorry, "Opprotuniy" has Yes Duplicates OK
and "Prospects" has Yes No Duplicates. Both have the
primary key "Prospect ID" and Access won't let me change
the index in "Prospects" from No to OK. Sorry about the
mix up. What should I do?

Thanks,
Ava

 

[Howard Brody]

You have the ProspectID set as the Primary Key in both
tables - which means it won't allow duplicates. What you
want is:

tblProspects

ProspectID (PrimaryKey, Indexed, NoDuplicates)
tblOpportunities
ProspectID (Indexed, Duplicates OK)
OpportunityID (Indexed, Duplicates)

Either no PrimaryKey for this table or both fields as a
joint PrimaryKey.

And your Relationship:
[tblProspects].[ProspectID] (one) =
[tblOpportunities].[ProspectID] (many)

Hope this helps!

Howard Brody