one-to-many relationships

  • Thread starter Thread starter Ava
  • Start date Start date
A

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
 
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
 
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)
Click to expand...
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
 
Back
Top