Issue with Join for SQL accessing Access Database

1
votes

I am trying to run some SQLcode that returns the record with the Maximum startdate. I am using an Select statement with an inner join.

See code below.

Select 
      t1.CH_Name_Initials as FirstName,
      t1.surname as Surname,
      t1.dt_start as StartDate,
      t1.dt_DOB as DateofBirth
from 
      tb_Pers t1
inner join 
      (SELECT CH_Name_Initials, surname, dt_start,MAX(dt_start) as max_date, 
       dt_DOB
FROM 
      tb_Pers t2
group by 
      CH_Name_Initials,
      t1.surname)
s2
on 
      t1.CH_Name_Initials = t2.CH_Name_Initials 
and   t1.surname = t2.surname 
and   t1.dt_start = t2.dt_start 
and   t1.dt_DOB = t2.dt_DOB

How ever when I try and run it I get the error message

Details: "ODBC: ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation

I understand Access requres multiple brackets but I am struggling to find where to put them?

Thanks

Chris

2
sqlms-accesspowerbi

2 Answers

3
votes

The alias you use in the outer join condition is incorrect. You used t2, which refers to a table in the subquery, instead of using s2, which correctly refers to your subquery itself. Try this version:

SELECT 
    t1.CH_Name_Initials AS FirstName,
    t1.surname AS Surname,
    t1.dt_start AS StartDate,
    t1.dt_DOB AS DateofBirth
FROM 
    tb_Pers t1
INNER JOIN 
(
    SELECT CH_Name_Initials, surname, MAX(dt_start) AS startdate, dt_DOB
    FROM tb_Pers t2
    GROUP BY CH_Name_Initials, surname, dt_DOB
) s2
    ON t1.CH_Name_Initials = s2.CH_Name_Initials AND 
       t1.surname = s2.surname AND
       t1.dt_start = s2.startdate AND
       t1.dt_DOB = s2.dt_DOB
1
votes

You want to reference t2 instead to s2, you can try below

 Select 
  t1.CH_Name_Initials as FirstName,
  t1.surname as Surname,
  t1.dt_start as StartDate,
  t1.dt_DOB as DateofBirth
from 
  tb_Pers t1
inner join 
  (SELECT CH_Name_Initials, surname, dt_start,MAX(dt_start) as max_date, 
   dt_DOB 
FROM 
  tb_Pers t2
group by 
  CH_Name_Initials,
  t2.surname,
  t2.dt_start,
  t2.dt_DOB)
t2
on 
  t1.CH_Name_Initials = t2.CH_Name_Initials 
and   t1.surname = t2.surname 
and   t1.dt_start = t2.dt_start 
and   t1.dt_DOB = t2.dt_DOB;

Can you try it like this : 

with cte as (
select max(dt_start) from tb_Pers
)
select CH_Name_Initials as FirstName,
  surname as Surname,
  dt_start as StartDate,
  dt_DOB as DateofBirth 
  from
tb_Pers,cte where dt_start = cte.max;