Prev: Oldest and Most Recent dates in header
Next: Using a Yes/No field to trigger a calculation in another field - J
From: ram on 30 Mar 2010 15:00
HI All,

In the subquery below how would I refer to the lowest 3 orderID?

Thanks in advance for any help

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
(SELECT TOP 3 OrderID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Orders.CustomerID
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID
From: Jerry Whittle on 30 Mar 2010 15:29
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID ASC)

You might need to remove " Dupe.OrderDate DESC, " if you are just interested
in the OrderID.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"ram" wrote:

> HI All,
>
> In the subquery below how would I refer to the lowest 3 orderID?
>
> Thanks in advance for any help
>
> SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
> FROM Orders
> WHERE Orders.OrderID IN
> (SELECT TOP 3 OrderID
> FROM Orders AS Dupe
> WHERE Dupe.CustomerID = Orders.CustomerID
> ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
> ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID
From: ram on 30 Mar 2010 15:42
Thank you Jerry just what I needed

"Jerry Whittle" wrote:

> ORDER BY Dupe.OrderDate DESC, Dupe.OrderID ASC)
>
> You might need to remove " Dupe.OrderDate DESC, " if you are just interested
> in the OrderID.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "ram" wrote:
>
> > HI All,
> >
> > In the subquery below how would I refer to the lowest 3 orderID?
> >
> > Thanks in advance for any help
> >
> > SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
> > FROM Orders
> > WHERE Orders.OrderID IN
> > (SELECT TOP 3 OrderID
> > FROM Orders AS Dupe
> > WHERE Dupe.CustomerID = Orders.CustomerID
> > ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
> > ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID
 | 
Pages: 1
Prev: Oldest and Most Recent dates in header
Next: Using a Yes/No field to trigger a calculation in another field - J