MAX problem

[Valeda]

Hello,

I am using the MAX function to choose a record that has the maximum date or unique identifier. I am using MAX(IDNbr), where IDNbr is an IDENTITY. I am using MAX(ApprDnyDt) which is datetime. Neither of these return one record. In both cases, I still receive all of the records.

Any ideas?

[StepUP]

I assume you are talking about a Totals query.

If you are using ONLY the MAX function, that should work.

If you have a "Group By" in the query, it won't return just one record.

[Valeda]

No, I am not trying to total anything. I am trying to select the record with the latest date or IDENTITY within a key.

[matthewspatrick]

Valeda,

You probably need something like:

SELECT KeyField, Max([DateField])
FROM SomeTable
GROUP BY KeyField

[Valeda]

I ended up creating another query that joined the table to itself. Then I used the results from the query instead to the table. I don't know if it was the best solution, but it worked.