Left Join Problem

  • Thread starter Jonathan Haddad
  • Start date
J

Jonathan Haddad

I've taken on the unfortunate task of updating an existing query to
include another table in a left join. unfortunately, I'm getting this
error:

Join Expression Not supported

When I try to run the query with the Left Join section in there. I'm
using Access 97 ( I know... blah ). I remove the join and it works.
The fields are correct, ive played with this thing for a while now with
absolutely no success. Any ideas?

SELECT distinctrow OrderMaster.OMSchoolID,
OrderMaster.OMTeacherID, OrderMaster.Date,
OrderMaster.Date, OrderDetail.ODOrderID, OrderDetail.ODShowID,
Schools.Name, Schools.Address1, Schools.Address2,
Schools.City, Schools.State, Schools.Zip,
[FName] & " " & [LName] AS TName,
Teachers.FName,
Teachers.LName, Shows.Date, Shows.Time, Shows.Desc,
OrderDetail.Status,
OrderStatusCodes.Desc,
OrderStatusCodes.Inventory,
OrderStatusCodes.Due,
OrderDetail.Qty, OrderDetail.Type,
SeatTypes.Desc, SeatTypes.Normal, SeatTypes.Due,
Shows.Price, [SeatTypes].[Due]*[OrderStatusCodes].[Due] AS Mult,
[Qty]*[Price]*[Mult] AS Expr1, [Expr1]+[Fees]*[Mult] AS Expr3,
OrderDetail.Date,
OrderDetail.[Study Guides], OrderDetail.Fees, Schools.Phone1,
OrderDetail.Workshops

FROM

Teachers, Schools, OrderMaster, Shows, SeatTypes,
OrderStatusCodes, OrderDetail

LEFT JOIN AR ON OrderDetail.ODShowID = AR.ARShowID
AND OrderDetail.ODOrderID = AR.AROrderID


WHERE
OrderMaster.Date >= [Begin Date?] And OrderMaster.Date <= [End Date?]
AND SeatTypes.StypeID = OrderDetail.Type AND
Shows.ShowID = OrderDetail.ODShowID AND
OrderMaster.OrderID = OrderDetail.ODOrderID AND
Schools.SchoolID = OrderMaster.OMSchoolID AND
Teachers.TeacherID = OrderMaster.OMTeacherID AND
OrderStatusCodes.StatusID = OrderDetail.Status


ORDER BY OrderDetail.ODOrderID DESC , Shows.Date, Shows.Time,
Shows.Desc, OrderDetail.Type;

Jon
 
M

Michel Walsh

Hi,



First, make a query just involving AR and OrderDetail

SELECT OrderDetail.*, Ar.*
FROM OrderDetail LEFT JOIN AR
ON OrderDetail.ODShowID = AR.ARShowID
AND OrderDetail.ODOrderID = AR.AROrderID


Save it, as Qu1.


Next, just use

SELECT ...
FROM othertables, Qu1




Hoping it may help,
Vanderghast, Access MVP
 
J

John Vinson

FROM

Teachers, Schools, OrderMaster, Shows, SeatTypes,
OrderStatusCodes, OrderDetail

LEFT JOIN AR ON OrderDetail.ODShowID = AR.ARShowID
AND OrderDetail.ODOrderID = AR.AROrderID
Click to expand...

I'm curious: do you *really* want a Cartesian join, showing every
possible combination of all Teachers, all Schools, all OrderMasters,
etc. etc.? Won't that give you an enormous recordset with a great many
irrelevant records (i.e. a Teacher joined to a School other than the
teacher's own school, or OrderDetail records displayed with completely
different OrderMaster records)?

If that *is* what you want Michel's suggestion should work... but this
seems a very odd query!
 
J

Jonathan Haddad

Check out the where clause in the original query. I rewrote the entire
query to be more readable to me when I couldn't get it to work using
INNER JOINS like Access sets up.

Jon
 
J

John Vinson

Check out the where clause in the original query. I rewrote the entire
query to be more readable to me when I couldn't get it to work using
INNER JOINS like Access sets up.
Click to expand...

oops! Sorry. Haven't used WHERE clause joins since working in Oracle
ten or twelve years back.
 
Top