Basic SQL Subquery

Question

I am working on a project where I am supposed to pull the top 2 Dates of all of my customers last order in MS Access. In the event that there is only one date , it should pull up that single date as well.

SELECT Customer_t.Customer_ID, 
Customer_t.Customer_Name, 
Order_t.Order_ID, 
Order_t.Order_Date
FROM Customer_t 
INNER JOIN Order_t 
 ON Customer_t.Customer_ID = Order_t.Customer_ID
WHERE (((Customer_t.Customer_ID)=[Order_t].[Customer_ID]))

I am a little new but I am having difficulty following the sub-query information that is currently out on the web.

I know I am trying to use the TOP 2 statement but I continue to receive syntax errors or statements that will say the most I can pull is one item .

Any help would be appreciated. I have seen similar questions to this already but I cannot repeat what the answers have recommended

Answer 1

This type of query is a bit tricky in MS Access. You can't do what you want easily with JOIN. Instead, think of this as filtering. You want to keep only the two most recent order ids for each customer.

That suggests a correlated subquery:

SELECT c.Customer_ID,  c.Customer_Name, o.Order_ID, o.Order_Date
FROM Customer_t as c INNER JOIN
     Order_t as o
     ON c.Customer_ID = o.Customer_ID
WHERE c.Order_ID IN (SELECT TOP 2 o2.Order_Id
                     FROM Order_t as o2
                     WHERE o2.Customer_Id = o.Customer_Id
                     ORDER BY o2.Order_Date, o2.Order_Id
                    );

I'm including Order_id in the ORDER BY so each row has a unique key. In MS Access, TOP really does TOP WITH TIES, so it is a good idea to add a unique key if you really want at most 2 rows.