Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help converting MS ACCESS query to MS SQL

    Hi. New here, but I would like to ask for some help. I'm a newbie when it comes to writing queries. If anybody, that's smarter than me, could help me convert this query to ms sql format I would greatly appreciate it. Here's the query..

    Code:
     SELECT dbo_joboperations.jmoworkcenterid AS Station,
           Last(dbo_jobs.jmporderquantity)   AS Completed
    FROM   dbo_jobs
           INNER JOIN (dbo_timecardlines
                       INNER JOIN dbo_joboperations
                               ON dbo_timecardlines.lmljobid =
                                  dbo_joboperations.jmojobid)
                   ON dbo_jobs.jmpjobid = dbo_joboperations.jmojobid
    GROUP  BY dbo_joboperations.jmoworkcenterid,
              dbo_joboperations.jmojobid
    HAVING ( ( ( dbo_joboperations.jmoworkcenterid ) LIKE "WALL" )
             AND ( ( Last(dbo_timecardlines.lmlroundedendtime) ) >= Date() )
             AND ( ( Last(dbo_joboperations.jmoproductioncomplete) ) = true ) );

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Is this a query that was done in Microsoft Access and you are now trying to create the same query in Microsoft SQL Server in T-SQL? The reason I ask is that Access does not in general use the INNER JOIN syntax.
    Wendell

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's exactly what I'm trying to do. I tried myself a few times, but I just get all sorts of syntax errors, and the one time I got it to run, I got totally different results than what I get in Access, connecting to the same server.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    The code has several issues.

    First, Access uses a JOIN syntax that can be peculiar and SQL Server is different. The syntax in your query is not supported.

    Secondly, SQL Server does not support the Last function. I got the syntax with the ciorrect joins, but I confess it's not clear to me the purpose of the Last function, which I never used in Access.
    Code:
    SELECT		dbo_joboperations.jmoworkcenterid AS Station,
    			Last(dbo_jobs.jmporderquantity)   AS Completed
    FROM		dbo_jobs
    INNER JOIN	dbo_joboperations ON dbo_jobs.jmpjobid = dbo_joboperations.jmojobid
    INNER JOIN	dbo_timecardlines ON dbo_timecardlines.lmljobid = dbo_joboperations.jmojobid
                   
    GROUP BY	dbo_joboperations.jmoworkcenterid,dbo_joboperations.jmojobid
    HAVING		(dbo_joboperations.jmoworkcenterid LIKE "WALL") 
    			AND (Last(dbo_timecardlines.lmlroundedendtime) >= getDate() )
    			AND (Last(dbo_joboperations.jmoproductioncomplete)= 'True' )
    Of course, this won't work. Can you explain what is the purpose of using Last, both in the SELECT clause and the in the HAVING clause?
    Rui
    -------
    R4

  5. #5
    New Lounger
    Join Date
    Sep 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I inherited the system that has this query in it, but I'm assuming it sorts the records somehow and returns the newest records in that table ?

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    It seems to be obtaining the last value of a given column, which I interpret to be the value of the given column for the most recent record.

    I don't know of a similar function in SQL Server. For SQL Server 2012 and 2014, there are a couple analytic functions that could do the job, but they require some knowledge of the underlying table structure, because at least an order by clause is needed. So, for the tables where you use Last, which fields can be used to order the recordset and obtain the correct value?
    Rui
    -------
    R4

  7. #7
    New Lounger
    Join Date
    Sep 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well I got it to work with this query, but I'm not sure it's optimized enough.. What do you think ?

    SELECT ISNULL(SUM(jmpOrderQuantity),0)
    FROM
    (
    SELECT "dbo"."Jobs"."jmpJobID" ,
    "dbo"."Jobs"."jmpOrderQuantity" ,
    "dbo"."TimecardLines"."lmlRoundedEndTime" ,
    "dbo"."JobOperations"."jmoJobID" ,
    "dbo"."JobOperations"."jmoWorkCenterID" ,
    "dbo"."JobOperations"."jmoProductionComplete"
    FROM "dbo"."JobOperations",
    "dbo"."Jobs",
    "dbo"."TimecardLines"
    WHERE ((("dbo"."JobOperations"."jmoWorkCenterID" LIKE 'WALLL')
    AND ("dbo"."Jobs"."jmpJobID" = "dbo"."JobOperations"."jmoJobID"))
    AND ("dbo"."TimecardLines"."lmlJobID" = "dbo"."JobOperations"."jmoJobID"))
    AND lmlRoundedEndTime >= Convert(date,GetDate())
    AND jmoProductionComplete = 1

    ) tmp

Posting Permissions

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