Querying Latest Change

[RikHess]

I have a table called "Name" which is indexed on ID, and a table called "ChangeLog" which includes the ID along with the date of the change and a description of the change. From this one-to-mamy relationship I want to query out only the most recent change for any ID.

Is there an easy answer to this?

TIA!

 

[belovedcej]

Select Max(dateChange), Description, ID
From changelog
InnerJoin name on name.id = changelog.id
GroupBy ID

 

[Golom]

Maybe. It depends on how you are populating the [ChangeDate] field. If you are just using Date() then there may be many records that were changed on the same date and there's no way to tell which one was the last one.

If instead, you are using Now() to populate the field then you can probably distinguish the last one (but it's not guaranteed in a multi-user environment.)

The query would look something like

Select ID, MAX(ChangeLog.ChangeDate) As [Latest Change]

From [Name] INNER JOIN ChangeLog
     ON [Name].ID = ChangeLog.ID

Group By [Name].ID

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[RikHess]

Thanks! This is what I needed.

Rik