Cannot set one-to-many relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2003, but for reasons I don't understand, the file is in
Access 2000 file format. (Perhaps that's irrelevant???)

I'm building a large database with many tables. I would like to relate
three of them as follows:

tbl_Agencies
AgenciesID Autonumber (Primary Key)
AgName Text
AgMail1 Text
etc . . .


tbl_Programs
ProgramsID Autonumber (Primary Key)
AgenciesID Number (Foreign Key)
AgName Text
etc . . .


tbl_Placements
PlacementsID Autonumber (Primary Key)
ProgramsID Number (Foreign Key)
ProgName Text
etc . . .

Each agency has many programs, and each program has many placements. I would
like to have a one-to-many relationship from agencies to programs, and this
seems to work OK. I would also like to have a one-to-many relationship from
programs to placments, but Access sees this as a one-to-one relationship. I
can't figure out why, and I can't figure out how to make it a one-to-many
relationship.

Can you explain?

--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA
 
Access gives you a one-to-one relation if you have a unique index on the
foreign key.

Open tbl_Placements in design view.
Select the ProgramsID field.
In the lower pane of the table design window, changed the Indexed property
to:
Duplicates Ok
or better still to:
No

I am assuming that you will create a relation with Referential Integrity
enforced. If so, Access will create a hidden index on the field, so there is
no point in you indexing it manually as well.
 
In your tbl_Placements table, is the ProgramsID field set to only allow
unique values? I think sometimes it is set that way as a default, and that
would limit the relationship to one-to-one.
 
That fixed it. Thanks for the help and the additional info!
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA
 
That was the problem. Thanks many times!
--

Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA
 
Back
Top