Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Very interesting Access query problem.

Hi Experts,

I have designed the following query in design view and it worked fine, however when I copy the SQL and try to paste it in new query I get an error "Join expression not supported", what could be the explanation of such behavior and what's the solution?

SELECT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, TovInfo.TovFacilityID, FacilitiesDocumentsRequirements.SkillsCheckList, TovInfo.Day, Employeestbl.SkillsChecklistDate
FROM Employeestbl INNER JOIN TovInfo ON Employeestbl.ID = TovInfo.EmployeeID, Facilitiestbl LEFT JOIN FacilitiesDocumentsRequirements ON Facilitiestbl.ID = FacilitiesDocumentsRequirements.FacilityID
WHERE (((TovInfo.TovFacilityID)=[tov_id1] Or (TovInfo.TovFacilityID)=[tov_id2]) AND ((TovInfo.Day)>Date()-60) AND ((Employeestbl.SkillsChecklistDate) Is Null))
ORDER BY Employeestbl.SkillsChecklistDate;

Open in new window

Avatar of Dale Fye
Dale Fye
Flag of United States of America image

I'm extremely surprised that it worked originally.  Why do you need a Cartesian join between the Employee/TovInfo combination of tables and the FacilitiesTbl/FacilitiesDocumentsRequirements table combination?  You might try something like:

SELECT E.ID, E.LastName, E.FirstName, E.TovFacilityID, F.SkillsCheckList, E.Day, E.SkillsChecklistDate
FROM (
SELECT Employeestbl.*, TOVInfo.*
FROM Employeestbl
INNER JOIN TovInfo ON Employeestbl.ID = TovInfo.EmployeeID
) As E,
(SELECT FacilitiesTbl.*, FacilitiesDocumentsRequirements.*
FROM
Facilitiestbl
LEFT JOIN FacilitiesDocumentsRequirements ON Facilitiestbl.ID = FacilitiesDocumentsRequirements.FacilityID
) as F
WHERE ((E.TovFacilityID)=[tov_id1] Or (E.TovFacilityID)=[tov_id2]) AND ((E.Day)>Date()-60) AND ((E.SkillsChecklistDate) Is Null))
ORDER BY E.SkillsChecklistDate;
Avatar of COACHMAN99
COACHMAN99

what happens if you try changing ((TovInfo.Day)>Date()-60) to ((TovInfo.Day)>Dateadd('d',-60, Date())
The coma doesn't belong in the From clause.  Access uses parentheses to separate joins.  Here's an example of working syntax with an inner and a left join

SELECT tblUtilityName.UtilityID, tblUtilityName.UtilityName, qCountAdds.RecCount, qCountAddsActiveAtEnd.RecCount
FROM (tblUtilityName INNER JOIN qCountAdds ON tblUtilityName.UtilityID = qCountAdds.utilityid) LEFT JOIN qCountAddsActiveAtEnd ON tblUtilityName.UtilityID = qCountAddsActiveAtEnd.utilityid;
why is it that you have four tables in your query with joins as shown

Employeestbl.ID = TovInfo.EmployeeID
Facilitiestbl.ID = FacilitiesDocumentsRequirements.FacilityID

but,  either of the two last tables does not have a relation to the first two tables.  ?
Avatar of bfuchs

ASKER

@Dale,
Why do you need a Cartesian join
This is because there is no direct join between tov info and facilitiestbl, as facilitiestbl can have two codes one stored in tov_id1 and the other in tov_id2.

See attached.

@COACHMAN99
while it was still working I tried changing to yours and got the error above.

FYI- If I change it to inner join at the following line and then go in design and change back to left join, it works again..weird, no?
Facilitiestbl LEFT JOIN FacilitiesDocumentsRequirements

Open in new window


Thanks,
Ben
untitled.png
Avatar of bfuchs

ASKER

@Pat, @Ray,

I need the following joins, employeestbl to TovInfo and TovInfo to facilitiestbl and left join to FacilitiesRequirements.
However as explained above the link between facilitiestbl and tovinfo cannot be established directly.

See attached.

Thanks,
Ben
untitled.png
Try to reproduce the issue using a sample database and upload.
Avatar of bfuchs

ASKER

@hnasr,

Here you go..

As you can see, query1 is working fine, now try to create a new query pasting the SQL OP.

Thanks,
Ben
db50.mdb
@bfuchs
Thanks for providing a sample database.

If you inspect each field at both ends of arelation, there are no equal fields, so the result set will be 0, using inner join.

If using left join for all, and removing criteria,
SELECT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, TovInfo.TovFacilityID, FacilitiesDocumentsRequirements.SkillsCheckList, TovInfo.Day, Employeestbl.SkillsChecklistDate AS Expr1
FROM (Employeestbl LEFT JOIN TovInfo ON Employeestbl.ID = TovInfo.EmployeeID) LEFT JOIN (Facilitiestbl LEFT JOIN FacilitiesDocumentsRequirements ON Facilitiestbl.ID = FacilitiesDocumentsRequirements.FacilityID) ON TovInfo.EmployeeID = Facilitiestbl.ID

Open in new window

ORDER BY Employeestbl.SkillsChecklistDate;
You get: null values indicates no matching link field.
ID      LastName      FirstName      TovFacilityID      SkillsCheckList      Day      Expr1
4      BYRON      MARGARET                        
3      AULT      CHARLES                        
With criteria,
SELECT Employeestbl.ID, Employeestbl.LastName, Employeestbl.FirstName, TovInfo.TovFacilityID, FacilitiesDocumentsRequirements.SkillsCheckList, TovInfo.Day, Employeestbl.SkillsChecklistDate AS Expr1
FROM (Employeestbl LEFT JOIN TovInfo ON Employeestbl.ID = TovInfo.EmployeeID) LEFT JOIN (Facilitiestbl LEFT JOIN FacilitiesDocumentsRequirements ON Facilitiestbl.ID = FacilitiesDocumentsRequirements.FacilityID) ON TovInfo.EmployeeID = Facilitiestbl.ID
WHERE (((TovInfo.TovFacilityID)=[tov_id1] Or (TovInfo.TovFacilityID)=[tov_id2]) AND ((TovInfo.Day)>Date()-60) AND ((Employeestbl.SkillsChecklistDate) Is Null))
ORDER BY Employeestbl.SkillsChecklistDate;

Open in new window

Shows no results
If you copy the above sql and paste in a new query, mit works.

Looks like the cross join of inner join and left join is confusing to access. In design mode it accepts that, but when copying a sql, the sql analyzer gets confused.

You need another look at your design.
The problem is not fully understood.
To help clarify the issue, list few records of each table, and list the required output.
Avatar of bfuchs

ASKER

@Hnasr,
The problem is not fully understood.
To help clarify the issue, list few records of each table, and list the required output.
OK I have changed some records to include joined info, now if you look at the query1 it contains one record and this is the desired output, however it only works by saving it as a query, while if I send the sql to a user and that user should paste it in a new query, it fails, see attached.

Thanks,
Ben
untitled.png
db50.mdb
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

This works
Thank you.
Welome!