MS Access SQL extracting a dataset of latest dates from grouped data

I have the following 3 tables: The first holds actual purchases by Customers, the second is price quotes to a customer for a product and the third shows any interest (Sale or quote) in a product by a customer.

A customer may simply purchase a product, or get a quote and never purchase, or get a quote and then later purchase a product.

I need to extract a dataset that shows the most recent date that a product was either sold or quoted to a customer. So, sorted by ProductCode, I want a dataset that looks like this …

I could do this in SQLServer but can’t quite get what I want in MSAccess SQL. Any help with the MSAccess SQL would be appreciated. James

Answer

First build a UNION query of Orders and Quotes:

SELECT [Date], CustomerID, ProductCode, "Order" AS Cat FROM SalesOrders
UNION SELECT [Date], CustomerID, ProductCode, "Quote" FROM Quotes;

Use that query in another query to return TOP 1 record for each product:

SELECT * FROM OrdersQuotesUNION
WHERE [CustomerID] & [Cat] & [Date] In (
    SELECT TOP 1 CustomerID & Cat & [Date] FROM OrdersQuotesUNION AS Dup 
    WHERE Dup.ProductCode = OrdersQuotesUNION.ProductCode ORDER BY Date DESC)
ORDER BY OrdersQuotesUNION.ProductCode;