In laying out the relationships between tables in Access 2007, I tried to
create two joins between a pair of tables, the AssignmentID and EmployeeID.
I do this kind of multiple join often. However this time I am getting an
error when I try to create the second join:
"A relationship already exits. Do you want to edit the existing
relationship? To create a new relationship, click No."
I can only join either AssignmentID or EmployeeID, not both.
I am defining the relationships in the Front End - none are definied in the
Back End.
What is wrong here? I checked an older database and found as many as four
joins between tables, here I am limited to one.

I can't address the possible version-specific issues, but you get no
beneefit other than a diagram from defining relationships in the front end.
To be enforced by the DB engine, the relationship must be in the database
where the table exists.

Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"M Skabialka" <mskabialka (AT) NOSPAMdrc (DOT) com> wrote

I found the answer right after I posted here - you have to edit both
relationships in the same window - which I have done before but went brain
dead this time. However even though my relationships are defined in the
front end, whenever I use the query designer and add tables, the
relationships I have designed in the front end are there. If it is better
programming practice to do it in the back end, I will do so, but seldom
touch the back end once my tables are designed.
Thanks,
Mich

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote

On Wed, 16 Jun 2010 16:09:05 -0500, "M Skabialka"
<mskabialka (AT) NOSPAMdrc (DOT) com> wrote:

Yes, it's much, much better practice to define relationships in the
BE. I never, ever touch them in the FE.

Tony

The relationships you define in the front end are "cosmetic only" if the
tables are linked from a back end database. That is, you may get join lines
automatically set when you create a query, but you can't enforce referential
integrity, nor cascade update, nor cascade delete.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"M Skabialka" <mskabialka (AT) NOSPAMdrc (DOT) com> wrote

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote in
news:87tjpmFghlU1 (AT) mid (DOT) individual.net:

That is, they aren't really relationships.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I have moved the relationships to the back end - thanks for the advice.
Mich

"Access Developer" <accdevel (AT) gmail (DOT) com> wrote