Basic SQL Subquery
I am working on a project where I have to pull the top 2 Dates of all my clients into the last order in MS Access. In case there is only one date, it should also pull out that single date.
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 bit of a newbie but am having difficulty with the sub-request information that is on the internet right now.
I know that I am trying to use the TOP 2 operator, but I keep getting syntax errors or statements that will say that I can only retrieve one item.
Any help would be greatly appreciated. I've seen similar questions before, but I cannot repeat what the answers recommended
source to share
This type of query is a little more complicated in MS Access. JOIN
You cannot do what you want. Think of it as filtering instead. You only want to keep the last two order IDs for each customer.
This assumes 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 am including Order_id
in ORDER BY
, so each row has a unique key. MS Access TOP
really does TOP WITH TIES
, so it's a good idea to add a unique key if you really want no more than two lines.
source to share