May 4, 2007 at 7:15 am
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.
May 4, 2007 at 7:22 am
Im pretty sure it's cause you haven't got any brackets () around your join criteria.
Suggest you go into Access and create a query with the query designer that has a LEFT JOIN to work out what bracketing you need.
Cheers
Steve
May 4, 2007 at 2:45 pm
It says Join Expression Not Supported when I add the part in italics.
May 4, 2007 at 2:45 pm
I investigated the bracket problems and I'm closer, but still not there yet:
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
products.ccode = test_bank.courseid
May 7, 2007 at 6:43 am
Have you tried a "Pass-Through" Query in Access yet?
May 7, 2007 at 6:47 am
I'm using Access as a back-end to a website so a pass through query is out of the question. I decided that for now I'm just going to have 2 queries instead of 1. I'm going to migrate from Access to SQL as my backend so I'll eventually get rid of this stupid mdb.
May 7, 2007 at 8:58 pm
Here's one more idea - MAKE A BACKUP COPY OF THE ACCESS FILE FIRST
And then open the COPY, click Tools ~ Options ~ Tables/Queries and click the checkbox for "SQL Server compatible syntax".
Access will display a few warnings, and then close & re-open itself - at which point the SQL syntax should probably work with SQL Server.
May 9, 2007 at 2:00 pm
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 products.ccode = test_bank.courseid
Here's a couple of things that you could try. First, you're out of parenthesis. I wonder if Access is not parsing the final left join correctly. Since you have an AND clause, and it's the only one there, try this:
LEFT JOIN test_bank ON (customers.username = test_bank.username AND products.ccode = test_bank.courseid)
Also, I think I'd try to restructure it so that the left join is the first join being performed, which would require a change to the Products join.
I'm not sure why, but the second part of the Test_Bank made me think of a Where clause, but that's not really appropriate. But looking it over again, for consistency's sake I'd reverse the arguments of the AND so that it's test_bank.courseid = products.ccode. I wonder if that could have anything to do with it.
Good luck!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 10, 2007 at 12:48 pm
I think you just need to do the first part of what Wayne suggested:
LEFT JOIN test_bank ON (customers.username = test_bank.username
AND products.ccode = test_bank.courseid)
This should fix your problem. I would leave the left join where it is at the end of the joins and not reverse the test_bank & products condition.
HTH
Regards,
-TB
May 10, 2007 at 1:21 pm
I have tried the suggestions. I tried adding the brackets as Wayne suggested. I also tried adding the option of using SQL compatible syntax and that did not work either.
They did not work for me. Thanks anyway.
May 10, 2007 at 1:51 pm
OK I tried an experiment and got the same error when I tried to manually write the query in SQL but when I used the Query Designer in MS Access to write the query I did not have a problem.
When I look at the SQL the Designer generated it looks like this:
FROM ((Tbl1 INNER JOIN Tbl2 ON Tbl1.Fld1 = Tbl2.Fld1) INNER JOIN [Tbl 3] ON (Tbl1.Fld1 = [Tbl 3].Fld1) AND (Tbl2.Fld1 = [Tbl 3].Fld1)) LEFT JOIN [Tbl 4] ON (Tbl1.Fld2 = [Tbl 4].Fld2) AND ([Tbl 3].Fld1 = [Tbl 4].Fld1);
Maybe this was too generic of an example but I did notice if I tried to break this up and separate the joins on their own lines I also got the same error message so MS Access may be sensitive to line breaks.
Regards,
-TB
June 7, 2007 at 9:11 am
Have you tried moving to a Access Project file? Access then becomes a front end to SQL Server and you get rid of the Access syntax problem.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply