Setting relationships between two tables with code

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

Guest

I would like to use a command function to set a relationship between two
tables so that when I select a option (check box or what ever) the
relationship will be made and then I can run a query with the tables joined
and then become non-joined at the end of the query and alternatively when the
check box is not selected the tables would remain non-joined when a query is
run. Can this be done?

I want to do this because I want to use the joined fields as "criteria" for
the query and sometimes I want one, sometimes two, sometimes three...
criteria. If some of the possible joined fields are not joined then they
will not function as criteria. Hopefully this makes sense.
 
I want to do this because I want to use the joined fields as "criteria" for
the query and sometimes I want one, sometimes two, sometimes three...
criteria. If some of the possible joined fields are not joined then they
will not function as criteria.
Click to expand...

It is NOT necessary to create or change a relationship in order to
create a Query joining two tables. You can join the tables on any
fields which make sense (heck, you can even join tables on fields
which DON'T make sense, so long as they are the same datatype).

I would suggest simply creating the queries, and manually dragging the
field you want to join from to the field you want to join to. You may
need to delete the join line created automatically, if that is getting
in the way.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
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));
 
I would like to have code that runs the query one way or the other depending
on what I choose.
Click to expand...

Then store two queries, and run one or the other, would be my
suggestion. Changing the table relationships will not affect the
existing query in any way.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top