digital_soul
asked on
The multi-part identifier "CourtesyCars.carreg" could not be bound.
Hi
I have been working on trying to convert this query from Access to SQL Server for the past day and am still having trouble. Im almost there, but am still getting the following error message on the query below:
The multi-part identifier "CourtesyCars.carreg" could not be bound.
I can see why Im getting this, but cant seem to rectify it. Any ideas?
Cheers
SELECT Repairs.repairs_ID, InsuranceCompanies.insuran ce_ID, OwnersCar.car_ID, OwnersCar.mobile AS custMobile, Repairs.stage,
Repairs.customerInformed, Repairs.finished, Repairs.courtesyCar_ID, Repairs.hirecar, CC_1.in_date, CC_1.out_date,
CarManafacturers.Manafactu rer + ' ' + 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 Repairs INNER JOIN
InsuranceCompanies ON InsuranceCompanies.insuran ce_ID = Repairs.insurance_ID INNER JOIN
CarModels ON CarModels.make_id = Repairs.make_ID INNER JOIN
CarManafacturers ON CarManafacturers.Manafactu rers_id = CarModels.Manafacturer_id INNER JOIN
StatusCodes ON StatusCodes.Status_ID = Repairs.stage INNER JOIN
OwnersCar ON OwnersCar.car_ID = Repairs.cars_ID INNER JOIN
colours ON OwnersCar.colour_id = colours.colour_id LEFT OUTER JOIN
(SELECT cc.carreg, cch.ccar_history_id, cch.in_date, cch.out_date
FROM CourtesyCars AS cc INNER JOIN
ccar_history AS cch ON cc.courtesyCar_ID = cch.ccar_id) AS CC_1 ON Repairs.courtesyCar_ID = CC_1.ccar_history_id LEFT OUTER JOIN
Operatives ON Repairs.Op_ID = Operatives.Op_Id
WHERE (1 = 1) AND (Repairs.potential = 0) AND (Repairs.finished = 0) AND (Repairs.StartDate <= GETDATE())
I have been working on trying to convert this query from Access to SQL Server for the past day and am still having trouble. Im almost there, but am still getting the following error message on the query below:
The multi-part identifier "CourtesyCars.carreg" could not be bound.
I can see why Im getting this, but cant seem to rectify it. Any ideas?
Cheers
SELECT Repairs.repairs_ID, InsuranceCompanies.insuran
Repairs.customerInformed, Repairs.finished, Repairs.courtesyCar_ID, Repairs.hirecar, CC_1.in_date, CC_1.out_date,
CarManafacturers.Manafactu
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 Repairs INNER JOIN
InsuranceCompanies ON InsuranceCompanies.insuran
CarModels ON CarModels.make_id = Repairs.make_ID INNER JOIN
CarManafacturers ON CarManafacturers.Manafactu
StatusCodes ON StatusCodes.Status_ID = Repairs.stage INNER JOIN
OwnersCar ON OwnersCar.car_ID = Repairs.cars_ID INNER JOIN
colours ON OwnersCar.colour_id = colours.colour_id LEFT OUTER JOIN
(SELECT cc.carreg, cch.ccar_history_id, cch.in_date, cch.out_date
FROM CourtesyCars AS cc INNER JOIN
ccar_history AS cch ON cc.courtesyCar_ID = cch.ccar_id) AS CC_1 ON Repairs.courtesyCar_ID = CC_1.ccar_history_id LEFT OUTER JOIN
Operatives ON Repairs.Op_ID = Operatives.Op_Id
WHERE (1 = 1) AND (Repairs.potential = 0) AND (Repairs.finished = 0) AND (Repairs.StartDate <= GETDATE())
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> CourtesyCars.carreg
try by replacing the above with
CC_1.carreg