MS Access SQL, How to return only the newest row before a given date joined to a master table

Question

I have two tables in a MS Access database as shown below. CustomerId is a primary key and fkCustomerId is a foreign key linked to the CustomerId in the other table.

Customer table

CustomerId	Name
1	John
2	Bob
3	David

Purchase table

fkCustomerId	OrderDate	fkStockId
1	01/02/2010	100
3	08/07/2010	101
2	14/01/2011	102
2	21/10/2011	103
3	02/03/2012	104
1	30/09/2012	105
3	01/01/2013	106
1	18/04/2014	107
3	22/11/2015	108

I am trying to return a list of customers showing the last fkStockId for each customer ordered before a given date.

So for the date 01/10/2012, I'd be looking for a return of

fkCustomerId	Name	fkStockId
1	John	105
2	Bob	103
3	David	104

A solution seems to be escaping me, any help would be greatly appreciated.

Answer 1

You can use nested select to get last order date.

SELECT Purchase.fkCustomerId,
       Name,
       fkStockId
FROM Purchase
JOIN 
(
     SELECT fkCustomerId,
            MAX(OrderDate) as last_OrderDate
     FROM Purchase
     WHERE OrderDate < '01/10/2012'
     GROUP BY fkCustomerId
) AS lastOrder
     ON  lastOrder.fkCustomerId = Purchase.fkCustomerId
     AND last_OrderDate = OrderDate 
LEFT JOIN Customer
     ON Customer.CustomerId = Purchase.fkCustomerId

This example assumes OrderDate before '01/10/2012'. You might need to change it if you want it to be filtered by a different value.

Another assumption is that there's only one corresponding fkStockId for each OrderDate