Querying the last order

[Ramesh]

I have an Orders table with Cust ID, OrderDetails, OrderDate. I am trying
to create a query to display the last order of each customer. In the design
view, i have used the Last function for the date and Group by for Cust ID.
With these two fields, i get the cust id and the last date. But when i add
OrderDetails field also, then I get one row for each order, rather than only
the last order.

How can i correct this?

Thanks for any help.
Ramesh

 

[Allen Browne]

Use a subquery to get the OrderID of the latest order for the customer.
Then use that query as the source for another query that gives you all the
details you want.

See:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html#TopN
The example returns the most recent 3 orders per client, so replace the 3
with 1.

 

[John W. Vinson]

I have an Orders table with Cust ID, OrderDetails, OrderDate. I am trying
to create a query to display the last order of each customer. In the design
view, i have used the Last function for the date and Group by for Cust ID.
With these two fields, i get the cust id and the last date. But when i add
OrderDetails field also, then I get one row for each order, rather than only
the last order.

How can i correct this?

Last() won't do what you think. It's a pretty nearly useless aggregate
function - it returns the last record IN DISK STORAGE ORDER, an order over
which you have no control and (generally) no interest.

Instead, use a Subquery. Put as a criterion on the OrderDate

=(SELECT Max(X.[OrderDate]) FROM Orders AS X WHERE X.[Cust ID] = Orders.[Cust
ID])


John W. Vinson [MVP]

 

[Ramesh]

Allen,

Hope this is not askig for too much. Is there a way to create a sub queries
with the wizard or design view? am not quite familiar with the sql lines.
am unable to understand the logic here and hence unable to use it other
similar uses.

thanks for any pointer
ramesh

 

[Allen Browne]

You have to type the subquery SQL statement in (i.e. you can't just use a
wizard.)

However, you can mock up another query, typing any old values in as
criteria. Then switch that to SQL View (View menu), and copy what you see as
the subquery.

 

[Jamie Collins]

You have to type the subquery SQL statement in (i.e. you can't just use a
wizard.)

However, you can mock up another query, typing any old values in as
criteria. Then switch that to SQL View (View menu), and copy what you see as
the subquery.

FWIW the following approach might be easier to implement in the Query
Builder (even if it doesn't perform significantly better):

SELECT M2.AddressID,
M2.ReadDate,
MAX(M1.ReadDate) AS PriorReadDate
FROM MeterReading AS M1,
MeterReading AS M2
WHERE M1.AddressID = M2.AddressID
AND M1.ReadDate < M2.ReadDate
GROUP BY M2.AddressID,
M2.ReadDate;

Jamie.

--

 

[Ramesh]

Thanks Allen and Jamie for your useful inputs.

Ramesh