sql server mseq: Hello,

I could not construct an SQL query that would return
an Entry of maximum date per Trailer Number, it still
returned more than one of the same Trailer Number
where the Status number was different.

So I have used the return data from one query to feed
into another. However, I also want to use a Pass-Through
query from Access to speed up the query. Although
it seems that a Pass-Through query is not able to be
referred to in a second Pass-Through query. The error
message I receive is:

Invalid Object Name "Q1TrailerAssignmentNMmaxDate"

The two queries I am attempting to use are:

==========================================================
Q1TrailerAssignmentNMmaxDate:
----------------------------

SELECT DISTINCT [SQLTrailers].[FleetNumber], [SQLTrailers].
[TrlNo], [SQLTrailers].[NextServiceDue], Max
([TrailerHistory].[Date]) AS MaxOfDate
FROM SQLTrailers INNER JOIN TrailerHistory ON
[SQLTrailers].[FleetNumber]=[TrailerHistory].[FleetNumber]
WHERE ((([SQLTrailers].[NextServiceDue])<getDate()+1) And
(([SQLTrailers].[OffRoad])=0) And (([SQLTrailers].
[DepotID])=1)) Or ((([SQLTrailers].[DepotID])=2)) Or
((([SQLTrailers].[DepotID])=3)) Or ((([SQLTrailers].
[DepotID])=4)) Or ((([SQLTrailers].[DepotID])=8))
GROUP BY [SQLTrailers].[FleetNumber], [SQLTrailers].
[TrlNo], [SQLTrailers].[NextServiceDue]
ORDER BY [SQLTrailers].[FleetNumber];

Q1TrailerAssignmentNM:
---------------------

SELECT [TrailerHistory].[FleetNumber], [StatusCodes].
[Status], [TrailerHistory].[VehicleReg],
[Locations&SubContractors].[Locations/Subbys],
[Q1TrailerAssignmentNMmaxDate].[NextServiceDue],
[Q1TrailerAssignmentNMmaxDate].[TrlNo], [TrailerHistory].
[Date]
FROM [Locations&SubContractors] RIGHT JOIN (StatusCodes
INNER JOIN (TrailerHistory INNER JOIN
Q1TrailerAssignmentNMmaxDate ON [TrailerHistory].
[FleetNumber]=[Q1TrailerAssignmentNMmaxDate].
[FleetNumber]) ON [StatusCodes].[ID]=[TrailerHistory].
[Status]) ON [Locations&SubContractors].[ID]=
[TrailerHistory].[LocationorSubCon]
WHERE ((([TrailerHistory].[Date])=
[Q1TrailerAssignmentNMmaxDate].[MaxOfDate]) And
(([TrailerHistory].[FleetNumber])=
[Q1TrailerAssignmentNMmaxDate].[FleetNumber]))
GROUP BY [TrailerHistory].[FleetNumber], [TrailerHistory].
[VehicleReg], [Q1TrailerAssignmentNMmaxDate].
[NextServiceDue], [Q1TrailerAssignmentNMmaxDate].[TrlNo],
[TrailerHistory].[Date], [StatusCodes].[Status],
[Locations&SubContractors].[Locations/Subbys]
ORDER BY [TrailerHistory].[FleetNumber];

========================================================

If you have any ideas that would be terrific.

Thank you kindly
Rhonda

I may be off base here as I'm not sure waht a pass through
Query is.
Would a Nested query not be more appropriate.
It's hard to say or test these queries without any idea of
the data.
Also it might be helpful to use table aliases in the query
to tidy things up a bit and make them more readable

HTH
Eric

SELECT DISTINCT [SQLTrailers].[FleetNumber], [SQLTrailers].
[TrlNo], [SQLTrailers].[NextServiceDue], Max
([TrailerHistory].[Date]) AS MaxOfDate
FROM SQLTrailers INNER JOIN TrailerHistory ON
[SQLTrailers].[FleetNumber]=[TrailerHistory].[FleetNumber]
WHERE ((([SQLTrailers].[NextServiceDue])<getDate()+1) And
(([SQLTrailers].[OffRoad])=0) And (([SQLTrailers].
[DepotID])=1)) Or ((([SQLTrailers].[DepotID])=2)) Or
((([SQLTrailers].[DepotID])=3)) Or ((([SQLTrailers].
[DepotID])=4)) Or ((([SQLTrailers].[DepotID])=8))
GROUP BY [SQLTrailers].[FleetNumber], [SQLTrailers].
[TrlNo], [SQLTrailers].[NextServiceDue]


) vQ1TrailerAssignmentNMmaxDate

ON [TrailerHistory].[FleetNumber]=
[vQ1TrailerAssignmentNMmaxDate].[FleetNumber])
ON [StatusCodes].[ID]=[TrailerHistory].[Status])
ON [Locations&SubContractors].[ID]=[TrailerHistory].
[LocationorSubCon]

WHERE ((([TrailerHistory].[Date])=
[vQ1TrailerAssignmentNMmaxDate].[MaxOfDate])
And (([TrailerHistory].[FleetNumber])=
[vQ1TrailerAssignmentNMmaxDate].[FleetNumber]))
GROUP BY
[TrailerHistory].[FleetNumber],
[TrailerHistory].[VehicleReg],
[vQ1TrailerAssignmentNMmaxDate].[NextServiceDue],
[vQ1TrailerAssignmentNMmaxDate].[TrlNo],
[TrailerHistory].[Date],
[StatusCodes].[Status],
[Locations&SubContractors].[Locations/Subbys]

ORDER BY [TrailerHistory].[FleetNumber]






--
===========================================================
==========================

SELECT TH.[FleetNumber],
SC.[Status],
TH.[VehicleReg],
LSC.[Locations/Subbys],
vQMD.[NextServiceDue],
vQMD.[TrlNo],
TH.[Date]
FROM [Locations&SubContractors] LSC
RIGHT JOIN ([StatusCodes] SC
INNER JOIN (TrailerHistory TH
INNER JOIN
(
SELECT DISTINCT
ST.[FleetNumber],
ST.[TrlNo],
ST.[NextServiceDue],
Max(TH2.[Date]) AS MaxOfDate
FROM SQLTrailers ST INNER JOIN [TrailerHistory] TH2 ON
ST.[FleetNumber]=TH2.[FleetNumber]
WHERE (((ST.[NextServiceDue])<getDate()+1) And ((ST.
[OffRoad])=0) And ((ST.[DepotID])=1)) Or (((ST.[DepotID])
=2)) Or (((ST.[DepotID])=3)) Or (((ST.[DepotID])=4)) Or
(((ST.[DepotID])=8))
GROUP BY ST.[FleetNumber], ST.[TrlNo], ST.
[NextServiceDue]
) vQMD
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.[TrlNo],
TH.[Date],
SC.[Status],
LSC.[Locations/Subbys]
ORDER BY TH.[FleetNumber]