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

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


"Dan Russotto" <(E-Mail Removed)> wrote in message
news:138401c39197$0c680a20$(E-Mail Removed)...
> 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