Cannot set one-to-many relationship

[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

 

[Allen Browne]

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.

 

[Guest]

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.

 

[Guest]

That fixed it. Thanks for the help and the additional info!
--
Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[Guest]

That was the problem. Thanks many times!
--

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