Allowing only one record to be checked in subform.

[aJay]

Hi folks,

I'm working on a system to keep track of investors. In the main
investor details form, I allow the user to enter the investor's
shareholdings on different dates, and select it as the latest one
(saving having to check latest dates to find each investor's latest
shareholding)

What i'm looking to do is, when one record is selected as the latest,
change all other records for that investor from previous dates, to not
being the latest.

Eg.
Date % Latest
12/06/06 - 2.51% No
15/06/06 - 2.67% No
01/12/06 - 2.41% Yes

Add a new shareholding, it becomes:

Date % Latest
12/06/06 - 2.51% No
15/06/06 - 2.67% No
01/12/06 - 2.41% No
02/12/06 - 1.59% Yes

Any help would be appriciated.
Thanks.

 

[aJay]

How would I use the DMax function within the following query to show
only the newest shareholdings for each investor?

SELECT investors.investorid, investors.investorname,
shareholdings.shareholding, shareholdings.date, shareholdings.latest
FROM investors INNER JOIN shareholdings ON investors.investorid =
shareholdings.investorid
WHERE ((shareholdings.latest) Like "-1")
ORDER BY investors.investorname;