bfuchs
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?
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;
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.RecC ount
FROM (tblUtilityName INNER JOIN qCountAdds ON tblUtilityName.UtilityID = qCountAdds.utilityid) LEFT JOIN qCountAddsActiveAtEnd ON tblUtilityName.UtilityID = qCountAddsActiveAtEnd.util ityid;
SELECT tblUtilityName.UtilityID, tblUtilityName.UtilityName
FROM (tblUtilityName INNER JOIN qCountAdds ON tblUtilityName.UtilityID = qCountAdds.utilityid) LEFT JOIN qCountAddsActiveAtEnd ON tblUtilityName.UtilityID = qCountAddsActiveAtEnd.util
why is it that you have four tables in your query with joins as shown
Employeestbl.ID = TovInfo.EmployeeID
Facilitiestbl.ID = FacilitiesDocumentsRequire ments.Faci lityID
but, either of the two last tables does not have a relation to the first two tables. ?
Employeestbl.ID = TovInfo.EmployeeID
Facilitiestbl.ID = FacilitiesDocumentsRequire
but, either of the two last tables does not have a relation to the first two tables. ?
ASKER
@Dale,
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?
Thanks,
Ben
untitled.png
Why do you need a Cartesian joinThis 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
Thanks,
Ben
untitled.png
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
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.
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
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,
stDate;
You get: null values indicates no matching link field.
ID LastName FirstName TovFacilityID SkillsCheckList Day Expr1
4 BYRON MARGARET
3 AULT CHARLES
With criteria,
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.
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
ORDER BY Employeestbl.SkillsCheckliYou 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;
Shows no resultsIf 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.
ASKER
@Hnasr,
Thanks,
Ben
untitled.png
db50.mdb
The problem is not fully understood.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.
To help clarify the issue, list few records of each table, and list the required output.
Thanks,
Ben
untitled.png
db50.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This works
Thank you.
Thank you.
Welome!
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.*, FacilitiesDocumentsRequire
FROM
Facilitiestbl
LEFT JOIN FacilitiesDocumentsRequire
) as F
WHERE ((E.TovFacilityID)=[tov_id
ORDER BY E.SkillsChecklistDate;