[AccessD] Need most recent record from many

Hi Susan

I assumed the two tables were related with a unique MemberID.
If not, this may be the moment where you reveal your table designs for
the audience ...

Also, if more than one payment can happen for one member on one day,
the subquery may return more than one record, adjust the subquery to
include sorting on the primary key of the payments table as shown:

<SQL>

SELECT
  tblMember.*,
  (SELECT TOP 1
    datReceived
  FROM
    tblPayment
  WHERE
    (tblMember.ID = tblPayment.MemberID)
  ORDER BY
    datReceived DESC,
    tblPayment.ID;) AS LastPayment
FROM
  tblMember;

</SQL>

/gustav


> Getting closer...This returns the information, but only for the most recent
> record, not for each member.  So, I think I need to add grouping.  Doing 
> that I've been able to pull the right record for each member in one query, 
> but then I have to link it back to the main table using two fields as 
> secondary keys to pull the primary key in order to continue pulling 
> information which seems circular and not good programming.
> Susan


> At 12:27 PM 3/28/03, you wrote:
>>Hi Susan
>>
>>As always, it's easy when you know how ...
>>Here is one method:
>>
>><SQL>
>>
>>SELECT
>>   tblMember.*,
>>   (SELECT TOP 1
>>     datReceived
>>   FROM
>>     tblPayment
>>   WHERE
>>     (tblMember.ID = tblPayment.MemberID)
>>   ORDER BY
>>     datReceived DESC;) AS LastPayment
>>FROM
>>   tblMember;
>>
>></SQL>