Multiple joins in Access queries

[Ken Temkin]

I don't know if this suggestion has been made before, so here it goes anyway
.. . .

When joining two tables in the Access Query Designer, the default join is an
INNER JOIN.

If more than one field is involved in the join, then those subsequent joins
are also, by default, INNER JOINS.

If one attempts to change the joins between those tables to, say, LEFT
JOINs, then one has to manually change all of the joins between the two
tables.

Since having multiple, MIXED joins between two tables does not make any
sense at all, why doesn't Access simply change all of the joins between the
two tables to the last style of join that you used between the two tables?

Admitedly, in a well-designed application, there should be a minimum of
multiple joins between tables. Some of us, however, work with legacy
databases, and databases external to our own, and are required to do
extensive querying using attachment to those tables. I regularly work with an
accounting database where I have to make up to six joins between two tables.
When I have to change those six joins by double-clicking, exactly, the tip of
my cursor on the join line between those two tables, six times, it gets a bit
tedious, especially if I have to do this to more than one pair of tables in
the same query. And with having typical databases involving dozens and dozens
of queries like this, it's a bit much, especially when the default behavior
doesn't make any sense at all.

So, my suggestion is, change the behavior of the Access Query Designer to
change all of the joins between two tables, if you change one of the joins.

Thanks.

--
Ken Temkin
Maverick Software Design
1335 Dodge Ave.
Evanston, IL 60201

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-b7af-0c456ba210ae&dg=microsoft.public.access

 

[Larry Linson]

I don't follow your reasoning that mixed joins make no sense. There are
cases in which mixed joins are perfectly reasonable. It simply depends on
your requirements.

If you are looking for a simpler way, it may be easier to modify the joins
in SQL view (or copy them to your favorite text editor, change, and copy
them back) than on the Query grid.

Larry Linson
Microsoft Access MVP

 

[Guest]

Mixed joins may be perfectly reasonable, but he meant the
kind of mixed joins that are not supported by the Access
QBE window: multi-field joins with some fields Full Inner
Join, and, some fields Left Inner Join, like you get when you
start to change an auto-generated multi-field Inner Join to a
Left Join.

I agree with him.

But it seems clear that he is not using Access 2003, or he
would have said something about the new Join Window you
get when you start to change a join like that.

I personally don't like the 'new' window any more than I liked
the 'old' behaviour, but I am interested in other opinions?

(david)

 

[Rick Brandt]

Mixed joins may be perfectly reasonable, but he meant the
kind of mixed joins that are not supported by the Access
QBE window: multi-field joins with some fields Full Inner
Join, and, some fields Left Inner Join, like you get when you
start to change an auto-generated multi-field Inner Join to a
Left Join.

I agree with him.

I also agree it would be a cool feature, but given the problem/benefit ratio of
this particular issue it would not likely make my top 1000 things I wish they
would change/fix in Access.