Sorting issue

[Guest]

Hello,

I have this report that shows records filtered by an ID number and sorted by
date. The report header also shows a name associated with that ID.
Sometimes, however, an ID can be linked to 2 names, in which case only one
of them is displayed. I would like to make sure that the name that is shown
is the one associated with the most recent record.
I seems like Access might be doing that automatically, but if it doesn't, is
there a way to make sure it does? And if so, how can that be accomplished?

Thank you.

 

[Guest]

This is of course not a sorting question, but the question started out as
one, and then evolved into this when attempting to formulate the question and
trying out a few ideas made things clearer.

 

[Smartin]

Hello,

I have this report that shows records filtered by an ID number and sorted by
date. The report header also shows a name associated with that ID.
Sometimes, however, an ID can be linked to 2 names, in which case only one
of them is displayed. I would like to make sure that the name that is shown
is the one associated with the most recent record.
I seems like Access might be doing that automatically, but if it doesn't, is
there a way to make sure it does? And if so, how can that be accomplished?

Thank you.

As you rightly point out this isn't a sorting issue after all (^:

This two step approach might work, depending on your design.

I started with this table:

table NAMESDATES
PK ID TheName TheDate
1 1 Larry 1/1/2006
2 1 Moe 9/8/2006 (*)
3 2 Curly 6/5/2006 (*)
4 3 Shemp 4/3/2006 (*)
5 3 Lewis 2/3/2006

(*) = desired rows

The following query will return ID and it's associated maximum date:

query NAMESDATES2
SELECT ND2.ID, MAX(ND2.THEDATE) AS THEDATE
FROM NAMESDATES AS ND2
GROUP BY ND2.ID;

The next query will return all columns from NAMESDATES where TheName is
associated with the most recent time stamp:

query NAMESDATES1
SELECT *
FROM NAMESDATES AS ND1
INNER JOIN
NAMESDATES2 AS ND2
ON ND1.ID = ND2.ID AND ND1.THEDATE = ND2.THEDATE

Result:

PK ND1.ID TheName ND1.THEDATE ND2.ID ND2.THEDATE
2 1 Moe 9/8/2006 1 9/8/2006
3 2 Curly 6/5/2006 2 6/5/2006
4 3 Shemp 4/3/2006 3 4/3/2006

 

[Smartin]

Smartin wrote:

Sorry about the formatting... I forgot tabs were embedded in my paste.