Selecting field based on effective date

[Luffk73]

Hello,

I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.
For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.


Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.

Thanks

Luffk73

[June7]

Only solution I see uses domain aggregate:

SELECT BU.*, Color.*
FROM BU INNER JOIN Color ON BU.empID = Color.empID
WHERE (((Color.[Date])=DMin("[Date]","Color","empID=" & [Bu].[empID] & " AND [Date]>=#" & [EffDate] & "#")));


Date is a reserved word - should not use reserved words as names. Also advise to avoid spaces and special characters/punctuation (underscore is exception) in naming convention.