Return the Most Recent Update

[John]

I have reviewed the other posting on this subject and tried to apply the
solutions as suggested but I am not getting the results I need.

I have a table of projects (t_PEPs) with pkey field PEPID and a table of
updates (t_Updates). Users provide monthly updates to the projects. They
select the PEPID as well as enter status information. This information is
stored in the t_Updates.

I need to create a report that will return each project and the most recent
update information for that project using the "UpdateDate" field. The
specific fields contained in t_Update that I want to appear in the report
include: Update_LSOStatus, Update_LLNSStatus, and UpdateRemedy

I've tried the "Top 1" method in queries and the Max function in a query and
the DMAX in reports. The Top1 query returns only the most recent update
regardless of the PEPID. The Max continues to return all updates for a
project and the DMAX function returns #Error.

Thanks in advance

 

[KARL DEWEY]

Try this --
SELECT [t_PEPs].*, [t_Updates].*
FROM [t_PEPs] LEFT JOIN [t_Updates] ON [t_PEPs].[PEPID] =
[t_Updates].[PEPID]
WHERE [t_Updates].[UpdateDate] = (SELECT Max([XX].[UpdateDate]) FROM
[t_Updates] AS [XX]);

 

[John]

Karl - Thanks for your help. I ran this query and it returns only the most
recent update, not the most recent update for each project.

I feel I am closer based on this though because when running it as a
sub-report, it displays the one returned result only when the parent report
is the related project. Unfortunately, the data does not appear in the
sub-report for other projects.



QWERTY

Try this --
SELECT [t_PEPs].*, [t_Updates].*
FROM [t_PEPs] LEFT JOIN [t_Updates] ON [t_PEPs].[PEPID] =
[t_Updates].[PEPID]
WHERE [t_Updates].[UpdateDate] = (SELECT Max([XX].[UpdateDate]) FROM
[t_Updates] AS [XX]);

--
Build a little, test a little.

I have reviewed the other posting on this subject and tried to apply the
solutions as suggested but I am not getting the results I need.

I have a table of projects (t_PEPs) with pkey field PEPID and a table of
updates (t_Updates). Users provide monthly updates to the projects. They
select the PEPID as well as enter status information. This information is
stored in the t_Updates.

I need to create a report that will return each project and the most recent
update information for that project using the "UpdateDate" field. The
specific fields contained in t_Update that I want to appear in the report
include: Update_LSOStatus, Update_LLNSStatus, and UpdateRemedy

I've tried the "Top 1" method in queries and the Max function in a query and
the DMAX in reports. The Top1 query returns only the most recent update
regardless of the PEPID. The Max continues to return all updates for a
project and the DMAX function returns #Error.

Thanks in advance

 

[KARL DEWEY]

Another try UNTESTED --
SELECT [t_PEPs].*, [t_Updates].*
FROM [t_PEPs] LEFT JOIN [t_Updates] ON [t_PEPs].[PEPID] =
[t_Updates].[PEPID]
AND [t_Updates].[UpdateDate] = (SELECT Max([XX].[UpdateDate]) FROM
[t_Updates] AS [XX]);

--
Build a little, test a little.

Karl - Thanks for your help. I ran this query and it returns only the most
recent update, not the most recent update for each project.

I feel I am closer based on this though because when running it as a
sub-report, it displays the one returned result only when the parent report
is the related project. Unfortunately, the data does not appear in the
sub-report for other projects.



QWERTY

Try this --
SELECT [t_PEPs].*, [t_Updates].*
FROM [t_PEPs] LEFT JOIN [t_Updates] ON [t_PEPs].[PEPID] =
[t_Updates].[PEPID]
WHERE [t_Updates].[UpdateDate] = (SELECT Max([XX].[UpdateDate]) FROM
[t_Updates] AS [XX]);

--
Build a little, test a little.

I have reviewed the other posting on this subject and tried to apply the
solutions as suggested but I am not getting the results I need.

I have a table of projects (t_PEPs) with pkey field PEPID and a table of
updates (t_Updates). Users provide monthly updates to the projects. They
select the PEPID as well as enter status information. This information is
stored in the t_Updates.

I need to create a report that will return each project and the most recent
update information for that project using the "UpdateDate" field. The
specific fields contained in t_Update that I want to appear in the report
include: Update_LSOStatus, Update_LLNSStatus, and UpdateRemedy

I've tried the "Top 1" method in queries and the Max function in a query and
the DMAX in reports. The Top1 query returns only the most recent update
regardless of the PEPID. The Max continues to return all updates for a
project and the DMAX function returns #Error.

Thanks in advance

 

[John Spencer]

Assuming that you have a field UpDateDate to determine the last update and a
foreign key PepID in t_Updates. A query like this one would identify which
record was the latest.

SELECT PepID, Max(UpDateDate) as LastUpdate
FROM t_UpDates
GROUP By PepID

So using that as a subquery in the from clause is one way to get the required
data returned.

SELECT t_Peps.*, t_Updates.*
FROM t_PEPs INNER JOIN
(t_Updates INNER JOIN
(SELECT PepID, Max(UpDateDate) as LastUpdate
FROM t_UpDates
GROUP By PepID) as L
On t_Updates.PepID = L.PepID
and t_Updates.UpdateDate = L.Lastupdate))
ON t_Peps.PepID = t_Updates.PepID


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County