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.