Search code examples
sqlms-accessms-access-2016

SELECT WHERE Between SELECT Dates from another table

I'm trying to find out if this can be made simpler/shorter. It is working but i think i can be faster, more efficient or cleaner.

SELECT Tbl_VSchedTime.* 
FROM CalendarScheduled
INNER JOIN Tbl_VSchedTime ON CalendarScheduled.[EID] = Tbl_VSchedTime.[EID]
WHERE ([Tbl_VSchedTime].[SchdDate] Between (SELECT MIN(CalendarScheduled.[SchdDate]) FROM CalendarScheduled) And (SELECT MAX(CalendarScheduled.[SchdDate]) FROM CalendarScheduled));

This is my test Select that will actually be change to Delete. In short I am selecting from Tbl_VShedTime anything matching the EIDs on the INNER JOIN and between the min and max dates in CalendarScheduled. However am forced to use a SELECT under each of the dates for it to work. It wont just work by using MIN and MAX when I have already "conjured" the table on the FROM Statement.

Is this just how I have to use it in access or did I miss something? Thanks.

Edit: The question is: Why can't I just write:

WHERE ([Tbl_VSchedTime].[SchdDate] Between MIN(CalendarScheduled.[SchdDate]) And MAX(CalendarScheduled.[SchdDate]))

Solution

  • Consider joining the aggregate query and then use aggregates in WHERE condition. Because aggregates return one row, cross join them with your current query which in MS Access means comma-separation in FROM:

    CROSS JOIN Before INNER JOIN

    SELECT t.* 
FROM 
(SELECT MIN([SchdDate]) AS MinDate, MAX([SchdDate]) AS MaxDate
 FROM CalendarScheduled) AS agg
,
CalendarScheduled c
INNER JOIN Tbl_VSchedTime t ON c.[EID] = t.[EID]
WHERE (t.[SchdDate] BETWEEN agg.MinDate AND agg.MaxDate;

    CROSS JOIN After INNER JOIN

    SELECT t.* 
FROM 
 (CalendarScheduled c
  INNER JOIN Tbl_VSchedTime t ON c.[EID] = t.[EID])
,
(SELECT MIN([SchdDate]) AS MinDate, MAX([SchdDate]) AS MaxDate
 FROM CalendarScheduled) AS agg
WHERE (t.[SchdDate] BETWEEN agg.MinDate AND agg.MaxDate;