none
Query advise RRS feed

  • Question

  • Hi

    I have the following query and I am getting the 'Syntax error in JOIN operation' error on the last WHERE (highlighted). 

    SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel, Q3Str.[E-Mail], Q3Str.Ab, [StaffBatchSMSShiftsListQuery - Staff Shifts].Shifts, [StaffBatchSMSShiftsListQuery - Ps].P, [StaffBatchSMSShiftsListQuery - NPs].NP, Q3Str.Forename, Q3Str.Surname, Q3Str.Drive 
    FROM ((SELECT * 
           FROM ((SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name, Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Ab, Staff.Forename, Staff.Surname, Staff.Drive 
                  From staff 
                  WHERE Staff.Copied = 0 AND staff.[Contact Status] = "Current") AS Q2Str 
           WHERE Q2Str.[Staff ID] In (SELECT Staff.[Staff ID] 
                                      FROM Staff 
                                      WHERE Staff.[Staff Role] <> "Hostess" ) AS Q3Str 
           LEFT JOIN [StaffBatchSMSShiftsListQuery - NPs] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - NPs].StaffID) 
           LEFT JOIN [StaffBatchSMSShiftsListQuery - Ps] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - Ps].StaffID) 
           LEFT JOIN [StaffBatchSMSShiftsListQuery - Staff Shifts] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - Staff Shifts].[Staff ID] 
    WHERE Q3Str.Forename Like "joh*" OR Q3Str.Surname Like "smi*" 

    What am I missing?

    Thanks

    Regards





    • Edited by Y a h y a Wednesday, August 26, 2015 6:39 PM
    Wednesday, August 26, 2015 5:32 PM

Answers

  • Try this and then join the  [StaffBatchSMSShiftsListQuery - xxxxxx]   queries one at a time, testing before adding the next.

    SELECT Staff.*, [Surname] & ", " & [Forename] AS NAME, Staff.Grade AS AB
    FROM Staff
    WHERE (((Staff.Forename) Like "joh*") AND ((Staff.Surname) Like "smi*") AND ((Staff.Copied)=0) AND ((Staff.[Contact Status])="Current") AND ((Staff.[Staff Role])<>"Hostess"));


    Build a little, test a little

    Thursday, August 27, 2015 2:09 AM

All replies

  • The highlighting did not come through but if you are talking about WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*" then just omit as it serves no purpose.

    Then run and see what error you get.


    Build a little, test a little

    Wednesday, August 26, 2015 6:17 PM
  • Hi

    Karl, unfortunately in general I need the clause. I have now edited my original post.

    Regards


    • Edited by Y a h y a Wednesday, August 26, 2015 6:50 PM
    Wednesday, August 26, 2015 6:38 PM
  • You have 5 open parenthesis but only 4 closing.

    FROM ((SELECT *

           FROM ((SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name, Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Ab, Staff.Forename, Staff.Surname, Staff.Drive

                  From staff

                  WHERE Staff.Copied = 0 AND staff.[Contact Status] = "Current") AS Q2Str

           WHERE Q2Str.[Staff ID] In (SELECT Staff.[Staff ID]

                                      FROM Staff

                                      WHERE Staff.[Staff Role] <> "Hostess" ) AS Q3Str

           LEFT JOIN [StaffBatchSMSShiftsListQuery - NPs] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - NPs].StaffID)

           LEFT JOIN [StaffBatchSMSShiftsListQuery - Ps] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - Ps].StaffID)


    Build a little, test a little

    Wednesday, August 26, 2015 8:38 PM
  • Hi

    I have balanced the parenthesise and now it is giving me 'Syntax error in JOIN' on the underlined text.

    SELECT Q3Str.[Staff ID], Q3Str.Name, Q3Str.Mobile, Q3Str.NormTel, Q3Str.[E-Mail], Q3Str.Ab, [StaffBatchSMSShiftsListQuery - Staff Shifts].Shifts, [StaffBatchSMSShiftsListQuery - Ps].P, [StaffBatchSMSShiftsListQuery - NPs].NP, Q3Str.Forename, Q3Str.Surname, Q3Str.Drive 
    FROM ((SELECT * 
           FROM (SELECT Staff.[Staff ID], [Surname] & ", " & [Forename] AS Name, Staff.Mobile, Staff.NormTel, Staff.[E-Mail], Staff.Grade AS Ab, Staff.Forename, Staff.Surname, Staff.Drive 
                 From staff 
                 WHERE Staff.Copied = 0 AND staff.[Contact Status] = "Current") AS Q2Str 
           WHERE Q2Str.[Staff ID] In (SELECT Staff.[Staff ID] 
                                      FROM Staff 
                                      WHERE Staff.[Staff Role] <> "Hostess" ) AS Q3Str 
           LEFT JOIN [StaffBatchSMSShiftsListQuery - NPs] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - NPs].StaffID) 
           LEFT JOIN [StaffBatchSMSShiftsListQuery - Ps] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - Ps].StaffID) 
           LEFT JOIN [StaffBatchSMSShiftsListQuery - Staff Shifts] ON Q3Str.[Staff ID] = [StaffBatchSMSShiftsListQuery - Staff Shifts].[Staff ID] 
    WHERE Q3Str.Forename Like "*" OR Q3Str.Surname Like "*" 

    Sorry the query is bit complex for me to figure out.

    Thanks

    Regads



    • Edited by Y a h y a Thursday, August 27, 2015 1:31 AM
    Thursday, August 27, 2015 1:30 AM
  • Try this and then join the  [StaffBatchSMSShiftsListQuery - xxxxxx]   queries one at a time, testing before adding the next.

    SELECT Staff.*, [Surname] & ", " & [Forename] AS NAME, Staff.Grade AS AB
    FROM Staff
    WHERE (((Staff.Forename) Like "joh*") AND ((Staff.Surname) Like "smi*") AND ((Staff.Copied)=0) AND ((Staff.[Contact Status])="Current") AND ((Staff.[Staff Role])<>"Hostess"));


    Build a little, test a little

    Thursday, August 27, 2015 2:09 AM