I ran into this same issue when we decided to implement Effective Dating. The way I solved the problem was to create a query to only pull the distinct most recent value from the "many" table, then join that query between the one and many tables.
Example: Customers, Orders in northwind.
To pull only the most recent order for Customers, do the following
Create a query named qryMostRecentOrder as:
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.CustomerID;
Then to get all information for the Order, create a query to join the Customer table, qryMostRecentOrder, and Order
SELECT Customers.CompanyName, Orders.*
FROM Customers INNER JOIN (qryMostRecentOrder INNER JOIN Orders ON (qryMostRecentOrder.MaxOfOrderDate = Orders.OrderDate) AND (qryMostRecentOrder.CustomerID = Orders.CustomerID)) ON Customers.CustomerID = qryMostRecentOrder.CustomerID;
I hope this helps. I had to go a bit further with my project because we wanted to be able to enter a date and find the effective record for the date entered. So I used a Where Max(EffectiveDate)<= [My Entered Date] filter on the query.
Let me know if you need a better explanation.
