SQL (Access) Select records that are not in the second table by condition and non-unique id

Question

I have two tables in MS Access, with non-unique id's, dates and qty:

TABLE_IN

id	date	qty	name
1	10.09.2022	1	Item_1
2	12.10.2022	1	Item_2
1	10.11.2022	2	Item_1
2	15.11.2022	1	Item_2

TABLE_OUT

id	date	qty	name
1	15.09.2022	1	Item_1
2	13.11.2022	1	Item_2
1	18.11.2022	2	Item_1

I need to select records that are IN (first table), but not OUT (second table) by dates (where 'in' date is less than 'out') and id`s.

Expected output:

id	date	qty	name
2	15.11.2022	1	Item_2

Required output data is id and qty (either it can be SUM of all qty's) that match selection.

I'm trying to use join, like this (and many other different variants):

SELECT [TABLE_IN].*
  FROM [TABLE_IN]
  LEFT JOIN [TABLE_OUT]
    ON [TABLE_IN].id = [TABLE_OUT].id
 WHERE [TABLE_OUT].id IS NULL
    OR ([TABLE_OUT].date < [TABLE_IN].date 
   AND  [TABLE_IN].id    = [TABLE_OUT].id)

But it does not gives the needed result:

id	date	qty	name
1	18.11.2022	2	Item_1
2	15.11.2022	1	Item_2

Maybe I need to use HAVING COUNT instructions.

Answer 1

See if this suits your needs:

SELECT table_in.* 
FROM table_in
LEFT JOIN table_out ON table_out.id = table_in.id
AND table_in.date < table_out.date
WHERE table_out.id IS NULL