Distinct record from two joined tables, one "distinct" field, controlling date

Access 2007

TableA  
IDx      Filename  

TableB  
IDy     Date     Name  

There are more fields in Table B, but this should work.

IDy is not unique, may have duplicates. IDx is unique, no duplicates.

What I started with:

 SELECT TableA.IDx, TableA.Filename, TableB.Date, TableB.Name  
 FROM Tabl开发者_如何转开发eA LEFT JOIN TableB ON TableA.IDx = TableB.IDy  

Where I am trying to get to:

(As stated in my other question, which I believe was too generic to answer the intent. I could not convert the elected answer to work with two tables and could not add a comment in the allotted space to re-ask the question with more detail.)

List of distinct records based on TableA.ID with most current record from TableB based on TableB.Date if IDx=IDy produces duplicates.

I hope this is understandable.

Something like this should probably solve your problem:

SELECT a.IDx, a.Filename, b1.Date, b1.Name
FROM ((TableA AS a)
  LEFT JOIN TableB AS b1 ON a.IDx = b1.IDy)
  LEFT JOIN TableB AS b2 ON b1.IDy = b2.IDy AND b1.Date < b2.Date
WHERE b2.IDy IS NULL

Limiting the output of TableB to only the rows with the latest Dates for every IDy is done through a crafty device, which involves joining TableB back to itself and searching for the rows where for any given Date there are no dates greater (later) than that.

Turns out, it's basically the same as John Gibb's solution, only it involves LEFT JOIN (the second one) + WHERE key IS NULL instead of NOT EXISTS.

Not sure if this will work in MS Access, but worth giving it a try:

SELECT TableA.IDx, TableA.Filename, TableB.Date, TableB.Name  
FROM TableA, TableB
WHERE TableA.Idx in (SELECT DISTINCT TableB.IDy FROM TableB)

Based on comments, try this tie breaker written as a left join.

select * 
from TableA a
 left join TableB b
   on b.idy = a.idx
  and b.date < a.date
where b.idy is null