Your where clause includes a reference to your secondary table:
IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents'
Well, in a left join where the is no matching record in IM_SCRELATIONM1 then SOURCE_FILENAME is null and hence those rows are excluded from the result set.
You have a couple of options. In standard SQL you can include the criteria as part of your join:
...ON (IM_INCIDENTSM1.INCIDENT_ID = IM_SCRELATIONM1.SOURCE)
AND (IM_SCRELATIONM1.SOURCE_FILENAME = 'incidents')
I think you can do this in Access as well.
The second option is to select all the records from IM_SCRELATIONM1 where SOURCE_FILENAME = 'incidents' as a subquery and then left join on the results:
LEFT JOIN (Select DEPEND from IM_SCRELATIONM1 where SOURCE_FILENAME = 'incidents') as INCIDENTS ON (IM_INCIDENTSM1.INCIDENT_ID = INCIDENTS.SOURCE)
OPEN_TIME between #6/1/2003# And #6/30/2003#
By the way,
mns' solution is simpler as long as there aren't any rows in IM_SCRELATIONM1 where SOURCE_FILENAME actually is null, in which case these would show up in your result set. The above two solutions should exclude them.