Access SQL - How to prevent alternating date while Selecting from latest date

Question

I have below my two tables and output

the SQL i used to get the output is

SELECT t.empID, t.timesheet, r.Rate AS RateBilled
FROM Rates AS r, timesheet AS t
WHERE (((r.empid)=t.empid) And ((t.timeSheet)>=r.promotionDate))
GROUP BY t.empID, t.timesheet, r.Rate , r.promotiondate
HAVING (((r.promotionDate)=Max([r].[promotionDate])));

my problem is that row 5 and 7 of Output table should also use $15 in Ratebilled (since the promotion date for emp 01 is may/1) , but it seems to still use the initial Jan-1 rate. Any help is appreciated.

Answer 1

 select m.*, r2.rate from  
 (SELECT t.empID, t.timesheet,
 max(r.promotiondate) as promotiondate FROM timesheet AS t left join
 rates r on r.empid=t.empid And t.timeSheet>=r.promotionDate  
 group by t.empid, t.timesheet) m 
 inner join rates r2 on  m.empid=r2.empid and m.promotiondate=r2.promotionDate