last date value with function max

  • Thread starter Thread starter Mario Krsnic
  • Start date Start date
M

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
 
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
 
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!
Click to expand...

Thank you Nikos, I shall try it. I cannot perform it in design view, because
I need this query for my VB-Application.
 
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;
 
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
Click to expand...

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
 
Sorry John, everything is allright. The problem was a dot in
tbl.Absent.absent. Thank you very much!
Mario
 
Back
Top