Query to return only the row containing the highest date.

[eric.opperman1@gmail.com]

I would like to return only the row with the latest date, but because some of the other fields have changed, all rows are returned. How can I get past this problem?



I have a table containing problem classifications. There is a foreign key, the classification of the problem, as well as the date the problem was classified.

[ajetrumpet]

select top 1 from table order by datefield desc

[eric.opperman1@gmail.com]

Thanx, that works but I am not quite out of the woods yet.

Let me explain:

I have a query that populates a subform, and this serves as a dashboard for outstanding issues.

Each issue can have a classification, but classifications can change and therefore I need the latest one. I therefore join to a query as opposed to a table. The advise you had already given works fine, but as a nested query (joined on the ticket id) it does not work.

See screenshot. And thanx for the help so far!

[ajetrumpet]

define "it does not work".

[tuyo]

im kind of stuck in the same problem of getting record with the latest date. i cannot seem this out. ive tried vba and queries in sql but im always stuck at the same point. help pls!

a little overview about the proj:
its an inventory. upon filling a form, i want it to automatically fill the opening balance with the latest ending balance IF the item in the form exists.

guidance pls.