Hello,


I am trying to get the same results in SQL server with a query as it works in access

There are two tables:
TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel

Fields in Table:
[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]
,[OOSTitleIssueDT]
,[EntryDT]
,[UserID]
,[RecDT]
,[RecMethod]
,[CurLoc]
,[CustLName]
,[CustFName]
,[CustAdd1]
,[CustAdd2]
,[CustCity]
,[CustState]
,[CustZIP]
,[CustPhone]
,[CustLName_2]
,[CustFName_2]
,[CustLName_3]
,[CustFName_3]
,[SentToDMVDT]
,[SentBackDT]
,[Check1]
,[TitleImg]
,[From]
,[LHDLRID]
,[Locked]
________________________________________





TITLOC
Fields: TTLLOC.Location, TTLLOC.SentDT, TTLLOC.InRoute, TTLLOC.Recvd

Fields in Table:
[TitleLocID]
,[TitleID]
,[SentDT]
,[UserID]
,[Location]
,[Method]
,[Notes]
,[Recvd]
,[RecvdBy]
,[RecvdDT]
,[InRoute]
,[InRouteBy]
,[InRouteDT]
________________________________________


MaxOfTitleLocID DOES NOT EXIST in either table.


It does use another query; I found it and listed it below. I am trying to figure out how to get the same results with one SQL query. I have never worked on a query that uses another query before. Not even sure why they did it this way other than access automating this in their query builder.




TITLES_IN_MAINOFFICE_qry - Main Office
SELECT TITLOC.Location, TITLES_IN_OFFICES_qry.CustLName, TITLES_IN_OFFICES_qry.CustFName, TITLES_IN_OFFICES_qry.MaxOfTitleLocID, TITLES_IN_OFFICES_qry.TitleState, TITLES_IN_OFFICES_qry.VehYR, TITLES_IN_OFFICES_qry.VehMake, TITLES_IN_OFFICES_qry.VehModel, TITLES_IN_OFFICES_qry.TitleID, TITLOC.SentDT, TITLOC.InRoute, TITLOC.Recvd
FROM TITLES_IN_OFFICES_qry INNER JOIN TITLOC ON TITLES_IN_OFFICES_qry.MaxOfTitleLocID = TITLOC.TitleLocID
WHERE (((TITLOC.Location)="MAIN OFFICE"))
ORDER BY TITLES_IN_OFFICES_qry.CustLName, TITLES_IN_OFFICES_qry.CustFName;




TITLES_IN_OFFICES_qry - All the offices use this query in their own office queries.
SELECT TITLES.CustLName, TITLES.CustFName, Max(TITLOC.SentDT) AS MaxOfSentDT, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID, Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.RecDT
FROM TITLES INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID
GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TITLOC.TitleID, TITLES.RecDT
ORDER BY TITLES.CustLName, TITLES.CustFName;


Anyone got any ideas how this can be ONE SQL Query? I am working on it but if anyone has any ideas it is appreciated.

In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.

I have never used a query as a table before in a select .