how to pull most recent date from a linked table

[Guest]

I have a people table and a linked events table that tracks
inquiries, calls, etc. from each person. So if Mary Smith
has been contacted five times and each contact has a record
in Events, then how do I pull out the most recent contact
date and put it in the [LastContact]field in People?

First, I'd like to develop an update query that would allow
me to populate the field in the people table for all the
records currently in the table. Let's call that field
[People]![LastContact]. How do I get Access to query the
Events table field [EventDate] and select the most recent
date of those linked to the People table record. The links
are [People]![PeopleID] and [Events]![EventID].

And then I want to develop an event procedure that would
change the [LastContact] date automatically whenever a new
contact is made. Can you at least give me some clues for
how to go about it so I'm not wandering in the Access
wilderness? I'm fairly savvy with Access expressions, but
don't know all the intricacies of Visual Basic.

Please reply to (e-mail address removed).

 

[Steve Schapel]

There is a database design principle that says that derived values
should not be stored. Whereas there are special circumstances where
this rule is not applicable, this is not the case with your example. It
is not a good idea to have a LastContact field in your People table, and
I would recommend you remove it altogether. You can easily make a query
to find the most recent event date for each person, and then you can
reference this query whenever you need the LastContact value for your
purposes on form or report. The SQL of such a query would look
something like this...
SELECT PeopleID, Max([EventDate]) As LastContact
FROM Events
GROUP BY PeopleID
(I have assumed you made a typo in your post, and that it is not the
EventID field in the Events table that identifies the person, and that
you have a PeopleID field for this purpose instead.)

 

[Arvin Meyer]

You don't need a LastContact field in the People table, you can always check
the current last contact by using either DMax on the table or building a
recordset and getting the TOP 1 value from the date field sorted in
descending order.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access