access sql max date max sequence

SELECT tblEMPData.Number, tblEMPData.Surname, tblEMPData.Name, tblEMPData.Status, tblEMPData.Site
FROM tblEMPData
where exists ( select 1 from (
	select top 1 *
	from tblEmpProjectHistory
	where tblEmpProjectHistory.[Address Number] = A.[Address Number]
	order by [Pay Str Date], [Sequence Number]
) BB )
where Number = BB.[Address Number]
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

SELECT tblEMPData.Number, tblEMPData.Surname, tblEMPData.Name,
        tblEMPData.Status, tblEMPData.Site, tblEmpProjectHistory.[Job Typ Description 1],
        tblEmpProjectHistory.[Job Step Description 1], tblEmpProjectHistory.[Sequence Number]
FROM tblEMPData INNER JOIN (
    (SELECT tblEmpProjectHistory.[Address Number], INNER1.[Pay Str Date Max],
        Max(tblEmpProjectHistory.[Sequence Number]) AS [Sequence Number Max]
     FROM tblEmpProjectHistory INNER JOIN
            (SELECT [Address Number], MAX([Pay Str Date]) AS [Pay Str Date Max]
             FROM tblEmpProjectHistory GROUP BY [Address Number]) AS INNER1
        ON (tblEmpProjectHistory.[Pay Str Date] = INNER1.[Pay Str Date Max])
        AND (tblEmpProjectHistory.[Address Number] = INNER1.[Address Number])
     GROUP BY tblEmpProjectHistory.[Address Number], INNER1.[Pay Str Date Max]) AS INNER2 INNER JOIN
     tblEmpProjectHistory ON (INNER2.[Sequence Number Max] = tblEmpProjectHistory.[Sequence Number])
        AND (INNER2.[Pay Str Date Max] = tblEmpProjectHistory.[Pay Str Date])
        AND (INNER2.[Address Number] = tblEmpProjectHistory.[Address Number])) 
     ON tblEMPData.[Address Number] = tblEmpProjectHistory.[Address Number]

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

SELECT tblEMPData.Number, tblEMPData.Surname, tblEMPData.Name, tblEMPData.Status, tblEMPData.Site
FROM tblEMPData
where exists ( select 1 from (
        select top 1 *
        from tblEmpProjectHistory
        where tblEmpProjectHistory.[Address Number] = A.[Address Number]
        order by [Pay Str Date], [Sequence Number]
        ) BB
      where Number = BB.[Address Number]
)

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

/* get top 1 history for each address */
SELECT b.*
FROM tblEmpProjectHistory b
where exists ( select 1 from (
                      select top 1 *
                      from tblEmpProjectHistory c
                      where c.[Address Number] = a.[Number]
                      order by [Pay Str Date], [Sequence Number]
               ) BB
          where [Address Number] = BB.[Address Number]
          and   [Pay Str Date] = BB.[Pay Str Date]
          and   [Sequence Number] = BB.[Sequence Number]
      )
      
/* get top 1 history for each address and EMP data*/
select a.*, c.*
from tblEMPData a inner join 
(
    SELECT b.*
    FROM tblEmpProjectHistory b
    where exists ( select 1 from (
                          select top 1 *
                          from tblEmpProjectHistory c
                          where c.[Address Number] = a.[Number]
                          order by [Pay Str Date], [Sequence Number]
                   ) BB
              where [Address Number] = BB.[Address Number]
              and   [Pay Str Date] = BB.[Pay Str Date]
              and   [Sequence Number] = BB.[Sequence Number]
          )      
) c
on a.[Number] = c.[Address Number]  

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

-- 1. Get the most recent reference number of history based on Pay Str Date and Sequence Number
--    (reference number = convert to number(Pay Str Date) + Sequence Number)
SELECT Max(CLng([Pay Str Date])+[Sequence Number])
FROM tblEmpProjectHistory
 
-- 2. Get history information of the most recent reference number
SELECT h.*
FROM tblEmpProjectHistory h
WHERE (CLng([Pay Str Date])+[Sequence Number]) In 
    (SELECT Max(CLng([Pay Str Date])+[Sequence Number])
     FROM tblEmpProjectHistory)
 
-- 3. Get EMP data and the most recent history info
SELECT d.*, h.[Job Typ Description 1], h.[Job Step Description 1], h.[Sequence Number]
FROM tblEMPData d LEFT JOIN (
    SELECT *
    FROM tblEmpProjectHistory
    WHERE (CLng([Pay Str Date])+[Sequence Number]) In 
        (SELECT Max(CLng([Pay Str Date])+[Sequence Number])
         FROM tblEmpProjectHistory)
) h ON d.[Number] = h.[Address Number]
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21: