Criteria for Displaying Most Recent Record Only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Another one for me today!

I have a report with this Record Source:
tblPKProjects.TargetCompletionDate, tblPKProjects.Description,
tblPKProjects.Priority, tblPKProjects.Objective, tblPKProjects.RequestedBy,
tblPKProjects.Manager, tblPKProjects.BeginDate, tblPKProjects.Status,
tblPKProjects.PercentCompleted, tblPKProjects.CompletionDate FROM
tblPKProjects INNER JOIN tblPKProjectsUpdates ON tblPKProjects.txtProjectID =
tblPKProjectsUpdates.txtProjectID;

I want the report to display only the most recent update record for each
project. This would be the field tblPKProjectsUpdates.UpdateDates.

Can this be done?

Your help is greatly appreciated!

Thanks!
 
JohnLute said:
I have a report with this Record Source:
tblPKProjects.TargetCompletionDate, tblPKProjects.Description,
tblPKProjects.Priority, tblPKProjects.Objective, tblPKProjects.RequestedBy,
tblPKProjects.Manager, tblPKProjects.BeginDate, tblPKProjects.Status,
tblPKProjects.PercentCompleted, tblPKProjects.CompletionDate FROM
tblPKProjects INNER JOIN tblPKProjectsUpdates ON tblPKProjects.txtProjectID =
tblPKProjectsUpdates.txtProjectID;

I want the report to display only the most recent update record for each
project. This would be the field tblPKProjectsUpdates.UpdateDates.
Click to expand...


That's an incomplete SQL statement. Since it include no
fields from the tblPKProjectsUpdates table, it is unclear
what you really want here.

OTOH, if the query included some fields from
tblPKProjectsUpdates, then you could limit those to the most
recent value in the UpdateDates field.

SELECT P.TargetCompletionDate,
P. . . .,
U.UpdateDates,
U. . . .
FROM tblPKProjects As P
INNER JOIN tblPKProjectsUpdates As U
ON P.txtProjectID = U.txtProjectID
WHERE U.UpdateDates =
(SELECT Max(X.UpdateDates)
FROM tblPKProjectsUpdates As X
WHERE X.txtProjectID = U.txtProjectID)
 
I had the same reaction about the SQL Statement but I had copy/paste it
directly from the properties dialog box. In the query builder the UpdatDate
field is listed in the columns as I had specified it through the report
wizard. Something seems odd here...

Anyway, UpdateDates is a Short Date formatted control. Is this the control
where I try the code you provided below? If so, I don't understand in what
property I need to place the code.

Sorry for my ignorance but my working with reports is relatively new and for
some reason my brain has melted down on comprehending this!
 
The sql statement probably takes more that one line and you
only copied one of the lines. When you have a property
that's too long to display in its entirety, it can be a big
help if you use the Shift+ F2 keyboard combination to open
the Zoom Box so you can see/copy the whole thing.

I suggest that you copy/paste my suggested query to the SQL
View of a new query in the query design window. Fix the
field list as needed. Then try to run the query directly
from the query design window and check to make sure the data
is as you expect. Once you have that working properly use
the name of the query instead of your existing sql statement
in the report's RecordSource property.
 
Thanks, Marshall!

I tried your suggestion but I get a syntax error in query expression 'P . .
.. .'. when I go from SQL View to Design View.

Any ideas?

Thanks!!!

Marshall Barton said:
The sql statement probably takes more that one line and you
only copied one of the lines. When you have a property
that's too long to display in its entirety, it can be a big
help if you use the Shift+ F2 keyboard combination to open
the Zoom Box so you can see/copy the whole thing.

I suggest that you copy/paste my suggested query to the SQL
View of a new query in the query design window. Fix the
field list as needed. Then try to run the query directly
from the query design window and check to make sure the data
is as you expect. Once you have that working properly use
the name of the query instead of your existing sql statement
in the report's RecordSource property.
--
Marsh
MVP [MS Access]


I had the same reaction about the SQL Statement but I had copy/paste it
directly from the properties dialog box. In the query builder the UpdatDate
field is listed in the columns as I had specified it through the report
wizard. Something seems odd here...

Anyway, UpdateDates is a Short Date formatted control. Is this the control
where I try the code you provided below? If so, I don't understand in what
property I need to place the code.

Sorry for my ignorance but my working with reports is relatively new and for
some reason my brain has melted down on comprehending this!
Click to expand...
Click to expand...
 
Of course, you need to replace the P.... and U.... with the
remainder of your field list. I just put those there so you
could see where to type the table.field stuff I couln't see
in your first post.

Note that I am using aliases P and U for you two table
names. Much less typing and easier to read this way.
--
Marsh
MVP [MS Access]



JohnLute wrote:\
 
Thanks, Marshall! I'm just an Access novice when it comes to coding so I was
unfamiliar with your shorthand.

That code worked fine - thanks again!

Marshall Barton said:
Of course, you need to replace the P.... and U.... with the
remainder of your field list. I just put those there so you
could see where to type the table.field stuff I couln't see
in your first post.

Note that I am using aliases P and U for you two table
names. Much less typing and easier to read this way.
--
Marsh
MVP [MS Access]



JohnLute wrote:\
I tried your suggestion but I get a syntax error in query expression 'P . .
. .'. when I go from SQL View to Design View.
Click to expand...
Click to expand...
 
JohnLute said:
Thanks, Marshall! I'm just an Access novice when it comes to coding so I was
unfamiliar with your shorthand.

That code worked fine - thanks again!
Click to expand...


Glad you got it working, John. Sorry about the misleading
shorthand.
 
Back
Top