Loading ...
Sorry, an error occurred while loading the content.

4853Access to SQL Server queires

Expand Messages
  • MBDEV - DB
    Mar 11, 2013
    • 0 Attachment
      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





      [Non-text portions of this message have been removed]
    • Show all 4 messages in this topic