Querying for the single most recently dated record per client ID

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

I should add that this is as far as I've gotten but this
query seems to select all records for each client ID.

SELECT tblPurchaserMemos.[Client ID],
MAX(tblPurchaserMemos.[Purchaser Memo Date]) AS [Latest
Memo Date],
tblPurchaserMemos.[Purchaser Memo Consultant],
tblPurchaserMemos.[Purchaser Memo],
tblPurchaserMemos.[Private?]
FROM tblPurchaserMemos
GROUP BY tblPurchaserMemos.[Client ID],
tblPurchaserMemos.[Purchaser Memo Consultant],
tblPurchaserMemos.[Purchaser Memo],
tblPurchaserMemos.[Private?];

I've created this using the QBE in Access.

Regards,

Troy..
 
Try some of the techniques described in The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

--
HTH
Van T. Dinh
MVP (Access)



Troy said:
I should add that this is as far as I've gotten but this
query seems to select all records for each client ID.

SELECT tblPurchaserMemos.[Client ID],
MAX(tblPurchaserMemos.[Purchaser Memo Date]) AS [Latest
Memo Date],
tblPurchaserMemos.[Purchaser Memo Consultant],
tblPurchaserMemos.[Purchaser Memo],
tblPurchaserMemos.[Private?]
FROM tblPurchaserMemos
GROUP BY tblPurchaserMemos.[Client ID],
tblPurchaserMemos.[Purchaser Memo Consultant],
tblPurchaserMemos.[Purchaser Memo],
tblPurchaserMemos.[Private?];

I've created this using the QBE in Access.

Regards,

Troy..
-----Original Message-----
I've got a table which has multiple records per client
ID. Each record has a date field. How can I query to
create a recordset which includes the single most recent
record for each client ID?

Regards,

Troy..
.
Click to expand...
Click to expand...
 
Back
Top