CODEWITHSUNDEEP
sqlms-accessms-access-2016
Ricardo A
Ricardo A

Reputation: 1815

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]))

Upvotes: 0

Views: 699

Answers (1)

Parfait
Parfait

Reputation: 107652

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;

Upvotes: 1

Related Questions