last date value with function max

[Mario Krsnic]

Hello everybody,
how can I get only the last date value from the table Tblabsent. I tried so:

SELECT familyName, name, born, reason, max(absent) AS absent2
FROM persons INNER JOIN Tblabsent ON persons.id=tblAbsent.id
GROUP BY familyName, name, born, reason;

The query is ok, if it runs without column reason.

SELECT familyName, name, born, max(absent) AS absent2
FROM persons INNER JOIN Tblabsent ON persons.id=tblAbsent.id
GROUP BY familyName, name, born;

But I need the column reason. How to resolve it?
Thanks for your ideas!
Mario Krsnic

 

[Nikos Yannacopoulos]

Mario,

That's because the reason field comes from the many side table
(Tblabsent). The easiest way to get around it is to use two queries, the
first one:

SELECT id, max(absent) AS absent2
FROM Tblabsent
GROUP BY id;

and then a second one where you join the previous one with both tables
on the common fields (persons on id = id, Tblabsent on id = id and
absent2 = absent) and retrieve the extra fields required from each -
much faster to do in design mode that write the SQL!

HTH,
Nikos

 

[Mario Krsnic]

and then a second one where you join the previous one with both tables on

the common fields (persons on id = id, Tblabsent on id = id and absent2 =
absent) and retrieve the extra fields required from each - much faster to
do in design mode that write the SQL!

Thank you Nikos, I shall try it. I cannot perform it in design view, because
I need this query for my VB-Application.

 

[John Spencer]

This UNTESTED SQL statement would probably give you what you want. Since
you didn't post the SQL with table names on the fields, I made assumptions
as to which field was in which table.
Persons: ID, FamilyName, Name, Born
tblAbsent: ID(FK to Persons), absent (date of absence), reason


SELECT familyName, name, born, reason, LastAbsence
FROM (persons INNER JOIN tblAbsent
on Persons.ID = tblAbsent.ID) INNER JOIN
(SELECT tblAbsent.ID
, Max(Absent) as LastAbsence
FROM TblAbsent
GROUP BY tblAbsent.ID) as Tmp
ON tblAbsent.id=tmp.id AND
tbl.Absent.absent = tmp.LastAbsence
ORDER BY familyName, name, born, reason;

 

[Mario Krsnic]

Hello John,

I made assumptions

as to which field was in which table.
Persons: ID, FamilyName, Name, Born
tblAbsent: ID(FK to Persons), absent (date of absence), reason

Your assumptions are right, but I get the syntax error from Access for
this query. "Error in join operation: tblabsent". If I remove the part
"AND tbl.Absent.absent = tmp.LastAbsence" then there are no syntax errors,
but
the query gives not only the last date values.
Please help me how to improve the query!
Mario

 

[Mario Krsnic]

Sorry John, everything is allright. The problem was a dot in
tbl.Absent.absent. Thank you very much!
Mario