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

Re: [SQLQueriesNoCode] Access to SQL Server queires

Expand Messages
  • Charles Carroll
    Because the groups have different audiences ideally the solution will be posted to both by Mike once he gets an answer. This group is the best home for Query
    Message 1 of 4 , Mar 13 3:07 AM
    • 0 Attachment
      Because the groups have different audiences ideally the solution will be
      posted to both by Mike once he gets an answer.

      This group is the best home for Query questions as a rule since there is no
      ASP.net component to his question its all about the query so most of the
      activity should appear here. Hopefully in future he will ask his query
      questions here only and we will help him resolve it here.

      He cross-posted it here because I told him it belongs here. He should be
      using the same subject line and just replying since he changes the subject
      the "thread" gets separated in Gmail and most readers in
      [AspNetAnyQuestionIsOk].

      HERE IS A GREAT PLACE TO ANSWER HIM, then he can cross-post the final
      solution to [AspNetAnyQuestionIsOk] just so people know the thread is
      resolved.


      On Wed, Mar 13, 2013 at 4:08 AM, John Warner <john@...> wrote:

      > **
      >
      >
      > How many groups are you cross posting this to? Which group do you prefer we
      > answer in?
      >
      > John Warner
      >
      >
      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of MBDEV - DB
      > Sent: Wednesday, March 13, 2013 2:12 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: RE: [SQLQueriesNoCode] Access to SQL Server queires
      >
      > 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.
      >
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of MBDEV - DB
      > Sent: Monday, March 11, 2013 10:41 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] Access to SQL Server queires
      >
      > 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]
      >
      > [Non-text portions of this message have been removed]
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >


      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.