Re-establishing a broken relationship

I've had to break a multi-relationship (three - one-to-one) between two
tables (say "A" & "B") in order to edit the properties one of them. The
problem is each time I try to build the relationship back in the relationship
window I get a message that relationship already exist "Do you want to create
another". If I choose no - then no relationship shows up in the relationship
window between these two tables. If I choose yes then I get a duplicate table
(three Table "B"'s) for table "A" one of the tables for each one-to-one
relationship I try to add. Before I had broken the relationships I had three
one-to-one connections between the two tables.

I'm not sure if the latter is actually a problem but it does seem to take my
queries a very significant time longer to run with the new relationships.


David McKnight

In the Relationship window, you can only click and drag ONE link. However,
in the Edit Relationships window (which pops up when you click and drag your
first set of fields) you can add your additional fields. After you add them
there, they will appear in the diagram window.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"David McKnight" wrote in message
...
I've had to break a multi-relationship (three - one-to-one) between two
tables (say "A" & "B") in order to edit the properties one of them. The
problem is each time I try to build the relationship back in the
relationship
window I get a message that relationship already exist "Do you want to
create
another". If I choose no - then no relationship shows up in the
relationship
window between these two tables. If I choose yes then I get a duplicate
table
(three Table "B"'s) for table "A" one of the tables for each one-to-one
relationship I try to add. Before I had broken the relationships I had
three
one-to-one connections between the two tables.

I'm not sure if the latter is actually a problem but it does seem to take
my
queries a very significant time longer to run with the new relationships.


David McKnight

In the two query senerios below I get two very different query results. The
difference between them is that in second I have added the statement.

"ON ([Filter parameters].Conference = [Season Division Conference
School].Conference)"

I would like to have code that runs the query one way or the other depending
on what I choose.


SELECT [Scores-Win Loss].ID, [Scores-Win Loss].Date, [Scores-Win
Loss].Season, [Scores-Win Loss].Home, [Scores-Win Loss].[Home Score],
[Scores-Win Loss]![Home Score]-(Coefficients![Home Field Adv Margin]/2) AS
[Home Adjusted Score], ([Scores-Win Loss]![Win-Loss])-(Coefficients![Home
Field AdvWin-Loss])/2 AS [Home Adjusted Score Win-Loss], [Scores-Win
Loss].Nuetral, [Scores-Win Loss].[Future Game], [Season Division Conference
School]!Division AS [Home Division], [Season Division Conference
School]!Conference AS [Home Conference]
FROM Coefficients, [Filter parameters] INNER JOIN ([Scores-Win Loss] INNER
JOIN [Season Division Conference School] ON ([Scores-Win Loss].Season =
[Season Division Conference School].Season) AND ([Scores-Win Loss].Home =
[Season Division Conference School].School)) ON ([Filter parameters].Division
= [Season Division Conference School].Division) AND ([Filter
parameters].Season = [Season Division Conference School].Season)
WHERE ((([Scores-Win Loss].[Future Game])=False));


SELECT [Scores-Win Loss].ID, [Scores-Win Loss].Date, [Scores-Win
Loss].Season, [Scores-Win Loss].Home, [Scores-Win Loss].[Home Score],
[Scores-Win Loss]![Home Score]-(Coefficients![Home Field Adv Margin]/2) AS
[Home Adjusted Score], ([Scores-Win Loss]![Win-Loss])-(Coefficients![Home
Field AdvWin-Loss])/2 AS [Home Adjusted Score Win-Loss], [Scores-Win
Loss].Nuetral, [Scores-Win Loss].[Future Game], [Season Division Conference
School]!Division AS [Home Division], [Season Division Conference
School]!Conference AS [Home Conference]
FROM Coefficients, [Filter parameters] INNER JOIN ([Scores-Win Loss] INNER
JOIN [Season Division Conference School] ON ([Scores-Win Loss].Season =
[Season Division Conference School].Season) AND ([Scores-Win Loss].Home =
[Season Division Conference School].School)) ON ([Filter
parameters].Conference = [Season Division Conference School].Conference) AND
([Filter parameters].Division = [Season Division Conference School].Division)
AND ([Filter parameters].Season = [Season Division Conference School].Season)
WHERE ((([Scores-Win Loss].[Future Game])=False));


"Roger Carlson" wrote:

In the Relationship window, you can only click and drag ONE link. However,
in the Edit Relationships window (which pops up when you click and drag your
first set of fields) you can add your additional fields. After you add them
there, they will appear in the diagram window.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L

"David McKnight" wrote in message
...
I've had to break a multi-relationship (three - one-to-one) between two
tables (say "A" & "B") in order to edit the properties one of them. The
problem is each time I try to build the relationship back in the
relationship
window I get a message that relationship already exist "Do you want to
create
another". If I choose no - then no relationship shows up in the
relationship
window between these two tables. If I choose yes then I get a duplicate
table
(three Table "B"'s) for table "A" one of the tables for each one-to-one
relationship I try to add. Before I had broken the relationships I had
three
one-to-one connections between the two tables.

I'm not sure if the latter is actually a problem but it does seem to take
my
queries a very significant time longer to run with the new relationships.


David McKnight