Return first occurrence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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;
 
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)
 
Back
Top