Results 1 to 4 of 4
  1. #1
    Pantinosm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    16

    Urgent. Last ms function change for SQL query

    Hi All,

    I am having big problems executing an easy access query in SQL. Last function as you may know is not working in SQl, so i need something equivalent. I need to get the last ship on a max date. Find the ms access that works below.

    Code:
    SELECT p.CREW_IPN, 
      p.LAST_NAME,
       p.FIRST_NAME,
        p.RANK_CODE,
    	 c.NATIONALITY, 
    	 ad.ADDRESS_NAME,
    	 	  last(ve.VESSEL_NAME) AS LastOfVESSEL_NAME, 
    	  Max(a.START_DATE) AS MaxOfSTART_DATE,
    	   p.EMAIL, a.START_STATUS
    FROM ((("dbo"."BASS_CREW_PROFILE" p INNER JOIN "dbo"."BASS_CREW_ACTIVITY" a ON p.CREW_ITEM_ID = a.CREW_ITEM_ID) 
    INNER JOIN dbo.BASS_VESSEL ve ON a.VESSELTRX_ID = ve.VESSELTRX_ID) 
    INNER JOIN "dbo"."BASS_REG_ADDRESS" ad ON p.MANNING_COMPANY = ad.ADDRESS_ID)
     INNER JOIN "dbo"."BASS_REG_COUNTRY" c ON p.NATIONALITY = c.COUNTRY_CODE
    GROUP BY p.CREW_IPN,
     p.LAST_NAME, 
     p.FIRST_NAME, 
     p.RANK_CODE, 
     c.NATIONALITY, 
     ad.ADDRESS_NAME, 
     p.EMAIL, 
     p.REC_DELETED, 
     a.START_STATUS,
     a.START_DATE
    HAVING (((p.EMAIL)<>'') AND ((p.REC_DELETED)<>-1) AND ((a.START_STATUS)='CONF') AND (a."START_DATE" > convert
           (datetime, '2020-01-01 00:00:00', 120))  and (a."START_DATE" < convert
           (datetime, '2021-01-01 00:00:00', 120)))
    ORDER BY p.LAST_NAME;
    I hope for some help.



    Thanks in advance.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If Max(start_Date) identifies the latest record, why do you need last vessel? Shouldn't the record(s) associated with the latest start_date be the "last" vessel?

  3. #3
    Pantinosm is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    16
    I get duplicate records for some reason.

    Table 1
    Name Email
    1
    2
    3


    Table 2
    Name StartDate Vessel
    2 1/1/2020 X
    2 2/10/2020 Y


    Query above gives me both records. I want only the latest. And i need the vessel name so how can i get only the line with last date?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Remove the quote marks around all instances of dbo.and the linked table names then try it again
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Audit log in function! urgent please!
    By sssandhya89 in forum Security
    Replies: 3
    Last Post: 03-04-2013, 09:51 AM
  2. Query help urgent
    By IKNOWNOSQL in forum Queries
    Replies: 1
    Last Post: 06-29-2011, 05:15 AM
  3. Urgent Query Help Please
    By AccessFreak in forum Queries
    Replies: 1
    Last Post: 06-23-2011, 06:02 AM
  4. Replies: 0
    Last Post: 07-15-2010, 11:26 PM
  5. Query to run from a FORM VERY URGENT
    By prag in forum Forms
    Replies: 14
    Last Post: 10-08-2009, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums