Syntax Error in Join Operation

[R_Sam]

Hi all,

I've created a works tracking DBMS which also incorporates inventory management and billing/invoicing. Where a client has multiple items in for repair, the idea is to batch invoice rather than a single invoice for each item. The SQL that I've written to facilitate this keeps giving me the following error message "Syntax error in JOIN Operation". The SQL is as follows:



JobnumPreFix = Left(JobnumPreFix, 3)
stJobnumPreFix = JobnumPreFix & "*"


'Query definition that identifies all the jobnumbers that are to be batch invoiced
stSQL4 = "SELECT Lkup_tblSalutation.Salutation, tblClientName.FirstName, tblClientName.Surname, tblClientName.BusinessName, " & _
"tblClientBusinessAddress.StreetAddress, tblClientBusinessAddress.DistrictAddress, tblClientBusinessAddress.TownAddress, " & _
"tblClientBusinessAddress.CountyAddress, tblClientBusinessAddress.PostCode " & _
"FROM ((Lkup_tblSalutation INNER JOIN tblClientName ON Lkup_tblSalutation.SalutationID = tblClientName.SalutationID) " & _
"INNER JOIN tblClientName ON tblClientBusinessAddress.ClientID = tblClientName.ClientID) " & _
"WHERE ClientID IN (SELECT tblHardwareSpecs.JobNumber, tblSummarySheet.PartName, tblSummarySheet.PartNumber, tblSummarySheet.SerNumber, " & _
"tblSummarySheet.Description, tblSummarySheet.Cost, tblHardwareSpecs.ClientID " & _
"FROM (tblHardwareSpecs INNER JOIN tblSummarySheet ON tblHardwareSpecs.HardwareID = tblSummarySheet.HardwareID) " & _
"WHERE tblHardwareSpecs.JobNumber LIKE '" & stJobnumPreFix & "' AND tblHardwareSpecs.Batched = TRUE " & _
"ORDER BY tblHardwareSpecs.JobNumber ASC) " & _
"ORDER BY BusinessName ASC, ORDER BY JobNumber ASC;"

I'd be grateful for any help I can get on this. Looking at it's not obvious to me what the problem is with the joins operations.

Thanks in advance.

Rob

[CJ_London]

you need to alias your subquery

your subquery can only return one field - clientID

and you can only have one order by in your main query