Works in SQL Server but not in Access

[tonymoreno]

Any idea how I could make the following query work in Access? Any
help would be greatly appreciated.

SELECT

orders.ouser, products.cname, customers.clastname, oitems.score,
test_bank.evalDate

FROM

oitems INNER JOIN orders ON oitems.orderid = orders.orderID INNER JOIN
customers ON orders.ouser = customers.username INNER JOIN
products ON oitems.catalogid = products.ccode LEFT OUTER JOIN
test_bank ON customers.username = test_bank.username AND
products.ccode = test_bank.courseid

It gives me a syntax error in Access. I tried removing the word
"OUTER" and that did not help. I tried doing it with the Access Query
Designer and it gives an error message about Ambiguous Joins.

 

[MH]

Tip:

Access LOVES brackets.

MH

 

[John Spencer]

Perhaps the following version of the FROM clause.

-- Access drop the OUTER from LEFT AND RIGHT JOINS
-- Access requires parentheses around the joins when more than two tables
are joined

FROM

(((oitems INNER JOIN orders ON oitems.orderid = orders.orderID)
INNER JOIN customers ON orders.ouser = customers.username )
INNER JOIN products ON oitems.catalogid = products.ccode)
LEFT JOIN test_bank ON customers.username = test_bank.username
AND products.ccode = test_bank.courseid

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Tom Ellison]

Dear Tony:

Probably, the problem is an annoying feature of Jet queries, that you must
parenthesize JOINs. Like this:

SELECT orders.ouser, products.cname, customers.clastname, oitems.score,
test_bank.evalDate
FROM ((oitems
INNER JOIN orders ON oitems.orderid = orders.orderID)
INNER JOIN customers ON orders.ouser = customers.username)
INNER JOIN products ON oitems.catalogid = products.ccode

I've left out the OUTER JOIN here. Get this much working first.

Jet does not have the OUTER JOIN. Look at your logic here first. If you
really only need a LEFT or RIGHT JOIN, change to that, adding parentheses as
before.

To accomplish an OUTER JOIN, you must UNION a LEFT JOIN with a RIGHT JOIN.
Proceed as before, creating each half of the UNION separately and testing
it.

Now, the two queries you have created may have some columns in common -
those that would have appeared had this last JOIN been an INNER JOIN. If
you exclude these duplicates with a WHERE clause, then you can use UNION ALL
instead. This would be much faster. Simply filter one of the two queries
to exclude those rows where some required column in the right side of the
JOIN is NOT NULL. Look at the result of doing this. There are now no
duplicates between the two queries, so that when you create the UNION there
are no duplicates to eliminate. If you have accomplished this, you can use
UNION ALL which doesn't search for the duplicates you have already
eliminated. This is much faster.

Tom Ellison
Microsoft Access MVP

 

[Aaron Kempf]

everything works in SQL Server and not ni Access

Access MDB is for retards

 

[Aaron Kempf]

yeah no crap it's annoying

Dear Tony:

Probably, the problem is an annoying feature of Jet queries, that you must
parenthesize JOINs. Like this:

SELECT orders.ouser, products.cname, customers.clastname, oitems.score,
test_bank.evalDate
FROM ((oitems
INNER JOIN orders ON oitems.orderid = orders.orderID)
INNER JOIN customers ON orders.ouser = customers.username)
INNER JOIN products ON oitems.catalogid = products.ccode

I've left out the OUTER JOIN here. Get this much working first.

Jet does not have the OUTER JOIN. Look at your logic here first. If you
really only need a LEFT or RIGHT JOIN, change to that, adding parentheses as
before.

To accomplish an OUTER JOIN, you must UNION a LEFT JOIN with a RIGHT JOIN.
Proceed as before, creating each half of the UNION separately and testing
it.

Now, the two queries you have created may have some columns in common -
those that would have appeared had this last JOIN been an INNER JOIN. If
you exclude these duplicates with a WHERE clause, then you can use UNION ALL
instead. This would be much faster. Simply filter one of the two queries
to exclude those rows where some required column in the right side of the
JOIN is NOT NULL. Look at the result of doing this. There are now no
duplicates between the two queries, so that when you create the UNION there
are no duplicates to eliminate. If you have accomplished this, you can use
UNION ALL which doesn't search for the duplicates you have already
eliminated. This is much faster.

Tom Ellison
Microsoft Access MVP

 

[tonymoreno]

Thank you for all the replies, but I'm still struggling. With adding
the appropriate brackets I can accomplish all inner joins. However, I
still cannot accomplish the Left Outer Join on both tables. The LEFT
JOIN works with the one table but when I add the second table it says
Join Expression Not Supported.

"To accomplish an OUTER JOIN, you must UNION a LEFT JOIN with a RIGHT
JOIN.
Proceed as before, creating each half of the UNION separately and
testing
it."

I don't understand the above statement. Isn't a LEFT JOIN in Access
the same as a LEFT OUTER JOIN in SQL? And if I UNION a LEFT JOIN and
a RIGHT JOIN wouldn't that be a FULL JOIN? I want a LEFT JOIN on 2
tables not a FULL JOIN.

This Clause works:

FROM (((oitems
INNER JOIN orders ON oitems.orderid = orders.orderID)
INNER JOIN customers ON orders.ouser = customers.username)
INNER JOIN products ON oitems.catalogid = products.ccode)
LEFT JOIN test_bank ON customers.username = test_bank.username

This Clause does NOT work:

FROM (((oitems
INNER JOIN orders ON oitems.orderid = orders.orderID)
INNER JOIN customers ON orders.ouser = customers.username)
INNER JOIN products ON oitems.catalogid = products.ccode)
LEFT JOIN test_bank ON customers.username = test_bank.username AND
dbo.products.ccode = dbo.test_bank.courseid

 

[MH]

Your second clause still has a reference to dbo in it, is that a typo?

MH

 

[Tom Ellison]

Dear Tony:

I expect you're almost there. How about this:


SELECT orders.ouser, products.cname, customers.clastname, oitems.score,
test_bank.evalDate
FROM (((oitems
INNER JOIN orders ON oitems.orderid = orders.orderID)
INNER JOIN customers ON orders.ouser = customers.username)
INNER JOIN products ON oitems.catalogid = products.ccode)
LEFT JOIN test_bank ON customers.username = test_bank.username AND
products.ccode = test_bank.courseid
UNION
SELECT orders.ouser, products.cname, customers.clastname, oitems.score,
test_bank.evalDate
FROM (((oitems
INNER JOIN orders ON oitems.orderid = orders.orderID)
INNER JOIN customers ON orders.ouser = customers.username)
INNER JOIN products ON oitems.catalogid = products.ccode)
RIGHT JOIN test_bank ON customers.username = test_bank.username AND
products.ccode = test_bank.courseid
WHERE customers.username IS NULL

I have just had a startling revelation. Your original request gave this
last JOIN as a LEFT OUTER JOIN. I have responded as though it said OUTER
JOIN. OUTER and INNER JOINs do not have a LEFT or RIGHT. Now I'm not at
all sure what you wanted this to do. It all hinges on what you meant by
this, and my possible misunderstanding based on its ambiguity.

Tom Ellison
Micorosft Access MVP

 

[MH]

Hi Tom,

In SQL Server, "LEFT OUTER JOIN" is the same as "LEFT JOIN" in Access.

MH

 

[tonymoreno]

Your second clause still has a reference to dbo in it, is that a typo?

MH












- Show quoted text -

Yes, "dbo" was a typo. I accidentally included it in my post, but I
was not including it in the actual query.

 

[MH]

I'd do this in two queries:

Query 1 would contain all your INNER JOINs and query 2 would be query 1
linked to Test_Bank using your two LEFT JOINs.

A bit like using a View in SS, look at it as a feature. )

MH