Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: Access To SQL Server Queries

    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 .

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Right off I'm wondering why you are joining on max(titloc.titlelocid).
    titlelocid is, presumably, a surrogate key. Its value has no meaning.
    If what you want it the latest record from titloc, you should be basing that logic on one of the date columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...also, what version of SQL Server are you using? This problem will best be solved using a Common Table Expression.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2007
    Posts
    18
    SOLVED

    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's a good solution. It may not be optimal SQL, but it will work and its easy to implement.
    If you start having performance issues with large data sets, you'll want to rethink that architecture.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •