Return first occurrence

I

Izba

I have a table with a 2-field primary key, Custid (sort: Ascending) and Date
(Sort: Descending). The combination of the two fields creates a unique key.
I want to write a query that will return, for each customer, the most recent
record entered for him/her. Example:

custid date
1 10/20/2004
1 8/7/2003
1 7/13/2002
2 1/13/2001
2 3/3/1999
2 1/1/1998

Returned recordset would contain
1 10/20/2004
2 1/13/2001

Appreciate the help. I am using MS Access 2000 in Win XP
 
G

Gerald Stanley

SELECT custId, Max([date])
FROM YourTable
GROUP BY custId;

It is not good practice to name a column Date as it is a reserved word used
for the Date() function.

Hope This Helps
Gerald Stanley MCSD
 
T

tdom

You can obtain your desired results by grouping the query by 'custid' and the
selecting the first 'date'. Paste the below text into the SQL designer and
replace 'TableName' with your table name.
-------------------
SELECT
custid,
First(date) AS MostRecentDate
FROM TableName
GROUP BY custid;
 
M

Marshall Barton

Izba said:
I have a table with a 2-field primary key, Custid (sort: Ascending) and Date
(Sort: Descending). The combination of the two fields creates a unique key.
I want to write a query that will return, for each customer, the most recent
record entered for him/her. Example:

custid date
1 10/20/2004
1 8/7/2003
1 7/13/2002
2 1/13/2001
2 3/3/1999
2 1/1/1998

Returned recordset would contain
1 10/20/2004
2 1/13/2001

Appreciate the help. I am using MS Access 2000 in Win XP
Click to expand...


To get the entire record, you need to use a subquery

SELECT *
FROM table AS T
WHERE T.[Date] = (SELECT Max(X.[Date])
FROM table AS X
WHERE X.CustID = T.CustID)
 
Top