Last record by text field of an unsorted table

[Nir N]

I have the followinig simple quwery:

SELECT M2001.LASTNAME, M2001.FIRSTNAME, SESSION1.Date
FROM M2001 INNER JOIN SESSION1 ON M2001.RECNUM = SESSION1.LINKNUM;

M2001 has 1 - to many relationsship with Session1.

My problem is that the "Session1" table is an external (linked) table from a
foreign database, and it's "DATE" field is, for some obscure reason, a text
field and NOT date field (although is displays a date in the regular access
format,
e.g. "20090809").

How can I get only the last (most recent) record , based on the DATE field,
of each person?

Thanks!

 

[John Spencer]

You can try the following. It should work since the "Date" format seems to be
yyyy-mm-dd and Max will work with strings.

SELECT M2001.LASTNAME
, M2001.FIRSTNAME
, Max(SESSION1.[Date]) as LastSession
FROM M2001 INNER JOIN SESSION1
ON M2001.RECNUM = SESSION1.LINKNUM
GROUP BY M2001.LASTNAME
, M2001.FIRSTNAME

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

 

[KARL DEWEY]

Try this --
SELECT M2001.LASTNAME, M2001.FIRSTNAME, SESSION1.Date
FROM M2001 LEFT JOIN SESSION1 ON M2001.RECNUM = SESSION1.LINKNUM
WHERE SESSION1.Date = (SELECT Max([XX].Date) FROM SESSION1 AS [XX];

 

[John Spencer]

Karl,
I believe you didn't finish the subquery since the user asked to get the data
for each person. Perhaps what you meant was:

SELECT M2001.LASTNAME, M2001.FIRSTNAME, SESSION1.Date
FROM M2001 LEFT JOIN SESSION1 ON M2001.RECNUM = SESSION1.LINKNUM
WHERE SESSION1.Date =
(SELECT Max([XX].Date)
FROM SESSION1 AS [XX]
WHERE XX.LinkNum = Session1.LinkNum)

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