Retrieving record with most recent date/time stamp and storing the latest value

[Dan Russotto]

I have a database where I am using a many-to-many
relationship as an audit trail. For example, table 1
stores project information for several projects. Table #3
is a listing of all of the releases that a given project
can be developed towards. Table #2, the join table, keeps
track of which release a project is in, and allows the
user to change the release (in a new record) that a
project is in, because it may have missed its deadlines.
Each one of the records has a date/time stamp as an audit
trail of how a project has changed releases. We would
like to store the most current or latest release either
in a query or a table with table #1. In other words, take
a value from the most recent record of the join table
(table #2), and store it in a field in table #1. If a new
record is adden in table #2, then this value should
update the value in table #1. Any suggestions out there
on how to implement this? Thanks, Dan

 

[A Coyle]

Hi
Try this, table and fields should be self explanatory

Query1 (finds max release date for each project):
SELECT t_Project.ProjectName, Max(t_ProjectReleaseJoin.ReleaseDate) AS
MaxOfReleaseDate
FROM t_Project INNER JOIN t_ProjectReleaseJoin ON t_Project.ProjectName =
t_ProjectReleaseJoin.ProjectName
GROUP BY t_Project.ProjectName;

Query2 (returns the record matching the max release date):
SELECT query1.ProjectName, query1.MaxOfReleaseDate,
t_ProjectReleaseJoin.ReleaseNumber
FROM query1 INNER JOIN t_ProjectReleaseJoin ON (query1.ProjectName =
t_ProjectReleaseJoin.ProjectName) AND (query1.MaxOfReleaseDate =
t_ProjectReleaseJoin.ReleaseDate);


regards
A