rhonda fischer

Hello,

I am not sure how to add a new table to my long pass-
through query with view to selecting the additional column
Depot. As soon as I add the new table the errmsg:
"The Column prefix TH does not match with a table name
or alias name used in the query" appears.

How can I add to the query to select the Depot Column
from the Vehicles table. Is it the join I have used or
the order??

Thank you for your thoughts

Cheerio
Rhonda


SELECT TH.[FleetNumber],
SC.[ID],
SC.[Status],
TH.[VehicleReg],
LSC.[Locations/Subbys],
TH.Notes,
vQMD.[NextServiceDue],
vQMD.Overdue,
vQMD.[TrlNo],
TH.[Date],
vQMD.OffRoad,
vQMD.WorkshopConcessionRelease,
vQMD.WorkshopStopped,
V.Depot <=== ***** Added Line ********

FROM [locations&subcontractors] LSC
RIGHT JOIN ([StatusCodes] SC
INNER JOIN (TrailerHistory TH
INNER JOIN (Vehicles V <=== ***** Added Line ********

INNER JOIN
(
SELECT DISTINCT
ST.[FleetNumber],
ST.[TrlNo],
ST.[NextServiceDue],
convert(int, getDate() - NextServiceDue) as Overdue,
Max(TH2.[Date]) AS MaxOfDate,
cast(ST.OffRoad as int) AS OffRoad,
cast(ST.WorkshopConcessionRelease as int) AS
WorkshopConcessionRelease,
cast(ST.WorkshopStopped as int) AS WorkshopStopped
FROM SQLTrailers ST INNER JOIN [TrailerHistory] TH2 ON
ST.[FleetNumber]=TH2.[FleetNumber]

GROUP BY
ST.[FleetNumber],
ST.[TrlNo],
ST.[NextServiceDue],
cast(ST.OffRoad as int),
cast(ST.WorkshopConcessionRelease as int),
cast(ST.WorkshopStopped as int)
) vQMD

ON V.[Veh Reg] = TH.[VehicleReg]) <=== ***** Added Line *
ON TH.[FleetNumber]=vQMD.[FleetNumber])
ON SC.[ID]=TH.[Status])
ON LSC.[ID]=TH.[LocationorSubCon]


WHERE (((TH.[Date])=vQMD.[MaxOfDate])
And ((TH.[FleetNumber])=vQMD.[FleetNumber]))
GROUP BY
TH.[FleetNumber],
TH.[VehicleReg],
vQMD.[NextServiceDue],
vQMD.Overdue,
vQMD.[TrlNo],
TH.Notes,
TH.[Date],
SC.[Status],
SC.[ID],
LSC.[Locations/Subbys],
vQMD.OffRoad,
vQMD.WorkshopConcessionRelease,
vQMD.WorkshopStopped
ORDER BY vQMD.[TrlNo]