Query to give value based on most recent date (1 Viewer)

[Alexander Willey]

Dear Forum,

I have a table that is part of a wider database. The table includes the following fields:

AdvisorName * (Text)
TimeLogID* (auto number)
ProjectID (number)
LogDate (date field)
TimeForecast (number field)

The compound primary key field is indicated with a *. The other fields may contain duplicates.

I want to retrieve the single most recent (based on LogDate) TimeForecast value for each ProjectID

Any help in writing a query or a way to achieve this would be most welcome

Many thanks

Alex

 

[plog]

First there's no need for a compound primary key. Since TimeLogID is an autonumber, that's your primary key. AdvisorName might need to be indexed, but it doesn't need to be part of the key, especially with another field that is guaranteed to be unique.

To accomplish what you want is going to require a subquery:

SELECT ProjectID, MAX(LogDate) AS RecentLogDate
FROM YourTableNameHere
GROUP BY ProjectID

That will identify the most recent date of the last record for each ProjectID. You then take that query and build another using it and YourTableName, you link them by ProjectID and LogDate to RecentLogDate and pull in all the fields from YourTableNameHere.

One caveat, all records with the most recent LogDate will be pulled into that final query.
That means if a ProjectID has 2 records that share the same date, they could both be pulled into that final query. To avoid this, you need to define a way to break that tie (what other field helps to define "most recent")

 

[Alexander Willey]

thanks very much for your help plog

 

[arnelgp]

what happens to the TimeForecast value?

what if there are many TimeForecast on same Max(LogDate)?


SELECT T1.ProjectID, T1.RecentLogDate, (SELECT Top 1 TimeForecast FROM YourTable WHERE ProjectID=T1.PROJECTID AND LogDate=T1.RecentLogDate Order By TimeForecast Desc) As RecentTimeForecast FROM (SELECT ProjectID, Max(LogDate) As RecentLogDate FROM YourTable Group By Projectid) AS T1

 

[Alexander Willey]

thank you arnelgp

There may be more than one TimeForecast value on one day

At the moment, the final query can display multiple values for one day and I only really require one value.

I am therefore using a group function for ProjectID and Max of TimeForecast, as that will give me the worst case value for a single date

Ideally I would like the newest entry as that feels more appropriate, however I don't know of an elegant approach

thank you again