OUTER join problem

[ana9 via AccessMonster.com]

I have the following query:

SELECT *
FROM { oj { oj [ACCT MGR QTR GOALS] LEFT OUTER JOIN
[ACCOUNT MANAGER] ON [ACCT MGR QTR GOALS].
ACCOUNTMANAGER = [ACCOUNT MANAGER].[AM INITIALS]} RIGHT OUTER JOIN
[SALES ORDER] ON [ACCOUNT MANAGER].[AM INITIALS] =
[SALES ORDER].ACCOUNTMANAGER }

WHERE (DATEPART('yyyy', [SALES ORDER].INVOICEDATE) = ?)
GROUP BY [ACCT MGR QTR GOALS].[Q1-2007], [ACCT MGR QTR GOALS].[Q2-2007],
[ACCT MGR QTR GOALS].[Q3-2007], [ACCT MGR QTR GOALS].[Q4-2007],
[ACCOUNT MANAGER].[AM INITIALS], [ACCOUNT MANAGER],
[ACCOUNT MANAGER], [SALES ORDER].ACCOUNTMANAGER
ORDER BY [SALES ORDER].ACCOUNTMANAGER

I am designing this in Visual Studio and I want to join the tables as stated
above, but VS keeps throwing in the { oj ...} and I get an error. If I
remove the { oj ...} I get an error as well. How am I supposed to join these
tables in this manner?

 

[Michel Walsh]

remove the oj ( two occurrences)

change the { } to ( ) ( two occurrences each)

change the ? for a parameter name


May work (haven't check further than what was obvious)

Vanderghast, Access MVP

 

[ana9 via AccessMonster.com]

You have to use unnamed parameters with access in visual studio, but that
wasn't causing the error. I tried running the query without the {oj...} and
still got an error so just changing to () shouldn't make a difference but if
it does I'll let you know

remove the oj ( two occurrences)

change the { } to ( ) ( two occurrences each)

change the ? for a parameter name

May work (haven't check further than what was obvious)

Vanderghast, Access MVP

I have the following query:

[quoted text clipped - 18 lines]

these
tables in this manner?

 

[ana9 via AccessMonster.com]

I tried the () with no effect. Still get an error in the JOIN statement.

remove the oj ( two occurrences)

change the { } to ( ) ( two occurrences each)

change the ? for a parameter name

May work (haven't check further than what was obvious)

Vanderghast, Access MVP

I have the following query:

[quoted text clipped - 18 lines]

these
tables in this manner?

 

[Michel Walsh]

You don't remove the { oj }, just the oj, then replace the { } into ( ).

I forgot to say you also have to remove the OUTER word (twice). It is just
LEFT JOIN, or RIGHT JOIN.


You can have a named parameter instead of the place holder, ? , , even in
Ado.Net, and doing so, would make your query executable outside it too.


If you still get an error, what error is it? (or is it still just 'an error'
in the JOIN clause, the compiler not giving any more clue ? )




Vanderghast, Access MVP

You have to use unnamed parameters with access in visual studio, but that
wasn't causing the error. I tried running the query without the {oj...}
and
still got an error so just changing to () shouldn't make a difference but
if
it does I'll let you know

remove the oj ( two occurrences)

change the { } to ( ) ( two occurrences each)

change the ? for a parameter name

May work (haven't check further than what was obvious)

Vanderghast, Access MVP

I have the following query:

[quoted text clipped - 18 lines]

these
tables in this manner?

 

[ana9 via AccessMonster.com]

I changed the FROM clause to the following:

(( [ACCT MGR QTR GOALS] LEFT JOIN
[ACCOUNT MANAGER] ON [ACCT MGR QTR GOALS].
ACCOUNTMANAGER = [ACCOUNT MANAGER].[AM INITIALS]) RIGHT JOIN
[SALES ORDER] ON [ACCOUNT MANAGER].[AM INITIALS] =
[SALES ORDER].ACCOUNTMANAGER )

and I get the error : "ERROR [HY000][Microsoft][ODBC Microsoft Access Driver]
Join expression not supported."

You don't remove the { oj }, just the oj, then replace the { } into ( ).

I forgot to say you also have to remove the OUTER word (twice). It is just
LEFT JOIN, or RIGHT JOIN.

You can have a named parameter instead of the place holder, ? , , even in
Ado.Net, and doing so, would make your query executable outside it too.

If you still get an error, what error is it? (or is it still just 'an error'
in the JOIN clause, the compiler not giving any more clue ? )

Vanderghast, Access MVP

You have to use unnamed parameters with access in visual studio, but that
wasn't causing the error. I tried running the query without the {oj...}

[quoted text clipped - 18 lines]

 

[ana9 via AccessMonster.com]

I just did a little test joining only one outer join instead of two, which I
need. Doing a left or right join with the { oj ... } syntax that VS
automatically creates worked just fine with only one outer join.

I tried the () with no effect. Still get an error in the JOIN statement.

remove the oj ( two occurrences)

[quoted text clipped - 11 lines]

 

[Michel Walsh]

Can you try it in two steps?

The first query will have to be saved in the database, though.


SELECT *
FROM [ACCT MGR QTR GOALS] LEFT JOIN [ACCOUNT MANAGER]
ON [ACCT MGR QTR GOALS].ACCOUNTMANAGER = [ACCOUNT MANAGER].[AM INITIALS]


save the query as qinter, as example, then, the last step will be


SELECT *
FROM qinter RIGHT JOIN [SALES ORDER]
ON qinter.[AM INITIALS] = [SALES ORDER].ACCOUNTMANAGER



and you will have to use qinter rather than [ACCT MGR QTR GOALS] or than
[ACCOUNT MANAGER] in that second query.


Vanderghast, Access MVP

I changed the FROM clause to the following:

(( [ACCT MGR QTR GOALS] LEFT JOIN
[ACCOUNT MANAGER] ON [ACCT MGR QTR GOALS].
ACCOUNTMANAGER = [ACCOUNT MANAGER].[AM INITIALS]) RIGHT JOIN
[SALES ORDER] ON [ACCOUNT MANAGER].[AM INITIALS] =
[SALES ORDER].ACCOUNTMANAGER )

and I get the error : "ERROR [HY000][Microsoft][ODBC Microsoft Access
Driver]
Join expression not supported."

You don't remove the { oj }, just the oj, then replace the { } into ( ).

I forgot to say you also have to remove the OUTER word (twice). It is just
LEFT JOIN, or RIGHT JOIN.

You can have a named parameter instead of the place holder, ? , , even in
Ado.Net, and doing so, would make your query executable outside it too.

If you still get an error, what error is it? (or is it still just 'an
error'
in the JOIN clause, the compiler not giving any more clue ? )

Vanderghast, Access MVP

You have to use unnamed parameters with access in visual studio, but
that
wasn't causing the error. I tried running the query without the {oj...}

[quoted text clipped - 18 lines]

these
tables in this manner?

 

[ana9 via AccessMonster.com]

Problem solved:

FROM ([ACCOUNT MANAGER] RIGHT JOIN [ACCT MGR QTR GOALS] ON [ACCOUNT MANAGER].
[AM INITIALS] = [ACCT MGR QTR GOALS].[AM INITIALS]) LEFT JOIN [SALES ORDER]
ON [ACCT MGR QTR GOALS].[AM INITIALS] = [SALES ORDER].ACCOUNTMANAGER

Before I initially designed it in the visual part, which threw in a bunch of
crap code. I went back and re-created the query without going into the
visual designer and this did the trick. Thanks for helping me troubleshoot.

I just did a little test joining only one outer join instead of two, which I
need. Doing a left or right join with the { oj ... } syntax that VS
automatically creates worked just fine with only one outer join.

I tried the () with no effect. Still get an error in the JOIN statement.

[quoted text clipped - 3 lines]