Find start time matches to nearest given

1

Hi All,

I am new to Access and SQL but I have been playing around and learning as I go.

I have a table of start times and a table of Employee Start Times. The Employee Start Times are fixed.

I am trying to match the Employee Start Times to the start times. I can return the exact matches but I want to be able to use a variance of 30 minutes before Employee Start Time and 45 minutes after.

I cannot find a way of getting the query to match with this variance.

2

Without knowing the structure of your tables it is hard to write a query, but the idea would be something like shown below. This is for SQLServer, will not work in Access

SELECT
	e.EmployeeStartTime,
	s.ApproximateStartTime
FROM
	EmployeeStartTimeTable AS e
	OUTER APPLY
	(
		SELECT TOP (1) StartTime AS ApproximateStartTime
		FROM
			StartTimeTable s
		ORDER BY
			ABS(DATEDIFF(mi,s.StartTime,e.EmployeeStartTime))
	)  AS s
1 Like
3

JamesK

Thanks for your quick reply and help.

I should have said the field names, my apologies.

The Start Times Table just has one filed, Start Times.

The Employees Start Time Table has two, Employee name and Start Time.

Also, I do not have SQLServer, just Access where I can write SQL.

Regards,

Kashflo