Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: Problem with Last funtion of access in Sql server

    I have the following query in access , but in sql server we dont have Last funtion ,,,,, so how to convert it ???? to run in sql server
    Pls Help



    SELECT tblClaims.LossId, tblClaims.JELossNo, tblClaims.claimno, Last(tblClaimRecord.CDate) AS
    LastOfCDate, Last(tblClaimRecord.CNote) AS LastOfCNote, Last(tblClaimRecord.CNotes) AS
    LastOfCNotes, tblClaimsExaminer.ExaminerName FROM (tblClaims INNER JOIN tblClaimRecord ON
    tblClaims.LossId = tblClaimRecord.LossID) INNER JOIN tblClaimsExaminer ON
    tblClaims.ExaminerID = tblClaimsExaminer.ExaminerID WHERE (((tblClaims.LossStatCode)<100))
    GROUP BY tblClaims.LossId, tblClaims.JELossNo, tblClaims.claimno, tblClaimsExaminer.ExaminerName
    ORDER BY Last(tblClaimRecord.CDate);

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    max(tblClaimRecord.CDate)?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Aug 2009
    Posts
    73
    max wont work because date entered in the last record can be different
    for ex it can be 12-12-2003

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Given
    In Access, the Last function returns the last value from the result set of a query.
    Try this
    Code:
    SELECT T.LossId, 
    	T.JELossNo, 
    	T.claimno, 
    	tblClaimRecord.CDate AS LastOfCDate, 
    	tblClaimRecord.CNote AS LastOfCNote, 
    	tblClaimRecord.CNotes AS LastOfCNotes, 
    	T.ExaminerName 
    FROM (SELECT tblClaims.LossId, 
    		tblClaims.JELossNo, 
    		tblClaims.claimno, 
    		tblClaimsExaminer.ExaminerName,
    		MAX(tblClaimRecord.CDate) as MaxCDate
    	FROM tblClaims 
    		INNER JOIN tblClaimRecord ON
    			tblClaims.LossId = tblClaimRecord.LossID
    		INNER JOIN tblClaimsExaminer ON
    			tblClaims.ExaminerID = tblClaimsExaminer.ExaminerID 
    	WHERE tblClaims.LossStatCode < 100
    	GROUP BY tblClaims.LossId, tblClaims.JELossNo, 
    		tblClaims.claimno, tblClaimsExaminer.ExaminerName
    	) AS T
    	INNER JOIN tblClaimRecord ON
    		T.LossId = tblClaimRecord.LossID AND
    		T.MaxCDate = tblClaimRecord.CDate
    ORDER BY T.MaxCDate
    Yet U wonder how Access handles the ORDER BY Last(tblClaimRecord.CDate). As Last(tblClaimRecord.CDate) depends upon the way the records are ordered, the query orders the records in a way that is dependent on the way they were ordered... Too much recursion makes my head spin.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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