Ms-access – Access Sub Query Speed

ms accesssubquery

I have three tables in Access:

employees
----------------------------------
id (pk),name

times
----------------------
id (pk),employee_id,event_time

time_notes
----------------------
id (pk),time_id,note

I want to get the record for each employee record from the times table with an event_time immediately prior to some time. Doing that is simple enough with this:

    select employees.id, employees.name, 
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as time_id 
from employees

However, I also want to get some indication of whether there's a matching record in the time_notes table:

select employees.id, employees.name, 
(select top 1 time_notes.id from time_notes where time_notes.time_id=(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC)) as time_note_present,
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) as last_time_id 
from employees

This does work but it's SOOOOO SLOW. We're talking 10 seconds or more if there's 100 records in the employee table. The problem is peculiar to Access as I can't use the last_time_id result of the other sub-query like I can in MySQL or SQL Server.

I am looking for tips on how to speed this up. Either a different query, indexes. Something.

Best Answer

you can use link to create index on MS-Access.

Hope this query work ok in MS-Access ,

select employees.id, employees.name, last_time_id
,(select top 1 time_notes.id from time_notes where time_notes.time_id=last_time_id)
time_note_present
from
(
select employees.id, employees.name, 
(select top 1 times.id from times where times.employee_id=employees.id and times.event_time<=#2018-01-30 14:21:48# ORDER BY times.event_time DESC) 
as last_time_id 
from employees
)t4

Thanks @Perkins.I have edited my answer.