select Distinct query

  • Thread starter Thread starter smk23
  • Start date Start date
S

smk23

How do I write a query for tables Client and Orders with a one to many
relationship where I want only a single record per Client, with only the
latest Order?

Thanks
 
One way (not the most elegant) to do this would be to first write a query
that gets you the latest Order per ClientID, then create a second query that
joins the first query with your Client table to pick up the Client info.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Making some assumptions regarding table and field names:
SELECT O.*, C.ClientName
FROM Clients C JOIN Orders O on C.ClientID = O.ClientID
WHERE O.OrderID = (SELECT TOP 1 O2.OrderID FROM Orders O2 WHERE O2.ClientID
= O.ClientID ORDER BY O2.OrderDate DESC);
 
Back
Top