Im currently faced with the mammoth task of migrating a fairly large web application across from Access to SQL Server and am having a nightmare with the following bit of SQL which is returning the error The multi-part identifier "ccar_history.in_date" could not be bound.
I know theres a lot of SQL to wade through, but if someone could point me in the right direction as to why this error occurs and how to fix it I would be eternally greatful
SELECT Repairs.Repairs_ID,InsuranceCompanies.insurance_ID,OwnersCar.car_id,OwnersCar.mobile AS custMobile,Repairs.stage,Repairs.customerInformed,Repairs.finished,Repairs.courtesycar_id,Repairs.hirecar,ccar_history.in_date,ccar_history.out_date,CarManafacturers.Manafacturer & ' ' & CarModels.Model AS [Vehicle],ownerscar.title & ' ' & OwnersCar.ownername AS [Owner],OwnersCar.carreg AS [Reg],Repairs.JobRefNo AS [JobRef],CourtesyCars.carreg AS [CC],Repairs.startDate AS [Start],InsuranceCompanies.Name AS [Provider],StatusCodes.Description AS [Status],Repairs.estfinishdate AS [Finish],Repairs.HitCount AS [Hit count],OwnersCar.claimnumber AS [ClaimNo]
FROM (CarManafacturers INNER JOIN (((OwnersCar INNER JOIN (InsuranceCompanies INNER JOIN (CarModels INNER JOIN (StatusCodes INNER JOIN Repairs ON StatusCodes.Status_ID = Repairs.stage) ON CarModels.make_id = Repairs.make_ID) ON InsuranceCompanies.insurance_ID = Repairs.insurance_ID) ON OwnersCar.car_ID = Repairs.cars_ID) LEFT JOIN (SELECT CourtesyCars.carreg,ccar_history.ccar_history_id,ccar_history.in_date,ccar_history.out_date FROM CourtesyCars INNER JOIN ccar_history ON CourtesyCars.courtesycar_id = ccar_history.ccar_id) CourtesyCars ON Repairs.courtesyCar_ID = CourtesyCars.ccar_history_id) LEFT JOIN Operatives ON Repairs.Op_ID = Operatives.Op_Id) ON CarManafacturers.Manafacturers_id = CarModels.Manafacturer_id) INNER JOIN colours ON OwnersCar.colour_id = colours.colour_id
WHERE 1=1 AND Potential = 0 AND Repairs.finished=0 AND Repairs.StartDate <= Getdate()