Access to SQL Server queires

[mbelcher]

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.


ACCESS QUERY:
NAME OF THE QUERY: TITLES_IN_OFFICES_qry

ELECT 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



It is using itself in the SQL string above. There is no MaxOfTitleLocID field in either table. I was thinking some kind of alias but not finding it in this query.





MY latest SQL version that is returning messed up results.
ELECT TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel, TTLLOC.Location, TTLLOC.SentDT,
TTLLOC.InRoute, TTLLOC.Recvd
FROM TITLES INNER JOIN
TTLLOC ON TITLES.TitleID = TTLLOC.TitleID
WHERE (TTLLOC.Location = 'MAIN OFFICE')


I am not sure I understand what the access query is doing; it seems to be using itself in the query. Any idea what is going on here?



Thanks,

Mike

[recyan]

At First glance,
your Access Query is using another query TITLES_IN_OFFICES_qry.
You need to look at that too.

Thanks

[mbelcher]

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.

[recyan]

Not sure, if I have understood things properly, all the same check out if below gives some guidelines :

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 
(
    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
 )
AS 
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;

You can try to use the VIEWS in SQL to build the sub-query TITLES_IN_OFFICES_qry & then use it in the Main Query TITLES_IN_MAINOFFICE_qry.

Thanks

[mbelcher]

SOLVED; Recyan that is how I did it.


How it was solved in case anyone needs to know. The original query was using another query "TITLES_IN_OFFICES_qry" with itself. I converted that into a view in SQL Server and now it can be used in the "TITLES_IN_MAINOFFICE_qry".




TITLES_IN_OFFICES_qry was created into a view in SQL Server
USE [TheDatabase]
go
Create View TITLES_IN_OFFICES_qry
As
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*/






END PRODUCT:
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




TITLES_IN_MAINOFFICE_qry Converted
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_MAINOFFICE_qry can now use it in MAINOFFICE_qry query.






It works


Thanks to you all for the help.

[recyan]

Glad you got things working.
Google "subqueries in sql" whenever you have spare time.

Thanks