I have two tables in MS Access, with non-unique id`s, dates and qty, with records like this:

TABLE_IN

TABLE_OUT

So, 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 is like this:

Required output data is id and qty, it cam 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 certainly, gives out the wrong (not the one you need) result, i.e.:

So, please, help me to get expected selection. Maybe i need to use HAVING COUNT instructions, but i can`t figure out how to do that %)

CodePudding user response：

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