sql server - MS Access SQL Join Performance Improvement -

i hoping on how can improve query below taking abosolute age execute.

i'm aware it's left join that's causing statement run have no idea how else run query improve performance , return required results.

query

select      s.id, s.first_name, s.last_name, count(a.absenceid) ((       dbo_ds_staff s       inner join dbo_ds_team_staff_member tsm on s.id=tsm.staff_id     )     inner join dbo_ds_team_leader tl on tsm.team_id=tl.team_id ) left join ct_adt_absence on s.id=a.staffid         tl.staff_id=2169      , tsm.start_date<now()       , (         tsm.end_date>=now()          or tsm.end_date null     )      , tl.start_date<now()     , (      tl.end_date>=now()       or tl.end_date null    )    group s.id, s.first_name, s.last_name    order s.first_name, s.last_name; 

if there's further information can give assist, please let me know! thanks

as suggested in comments question, pass-through query in access might speed things pushing processing of sql server tables onto sql server itself. example, pass-through query named [ptqstafflist] sql

select      s.id, s.first_name, s.last_name     dbo.ds_staff s     inner join      dbo.ds_team_staff_member tsm          on s.id=tsm.staff_id     inner join      dbo.ds_team_leader tl          on tsm.team_id=tl.team_id tl.staff_id=2169     , tsm.start_date < current_timestamp      , (tsm.end_date >= current_timestamp or tsm.end_date null)     , tl.start_date < current_timestamp     , (tl.end_date >= current_timestamp or tl.end_date null) 

...which looks in access...

...returns:

id  first_name  last_name --  ----------  --------- 1   gord        thompson 

we can use in regular select query local access table

select      s.id,      s.first_name,      s.last_name,      count(a.absenceid) absencecount     ptqstafflist s     left join      ct_adt_absence          on s.id=a.staffid    group s.id, s.first_name, s.last_name    order s.first_name, s.last_name; 

returning

id  first_name  last_name  absencecount --  ----------  ---------  ------------ 1   gord        thompson              1