select last date

[bergis]

Hi,
I have the following query:
SELECT wellist.WELL, event.JOB_TYPE, event.START_DATE,event.END_DATE, daily.DDATE
FROM daily, event, wellist
WHERE (((event.END_DATE) Is Null) AND ((event.I_KEY)=[daily].[i_key]) AND ((event.E_KEY)=[daily].[e_key]) AND ((wellist.I_KEY)=[event].[i_key] And (wellist.I_KEY)=[daily].[i_key]))
ORDER BY wellist.WELL, daily.DDATE;

I want this query to only show the last daily.DDATE, and furtermore I want to update the database so that the last DDATE is set as event.END_DATE.

Does anyone know how to do this? If I could get the query to only show the last DDATE, I could just use copy/paste to update the db.

tia
Kjell

 

[genomon]

Maybe add a "DISTINCT to the "SELECT" or a TOP 1, then add "DESC" to the "ORDER BY"????

 

[bergis]

Hi, thanks for the input.
It turned out to be relatively easy. Here's the final query.

SELECT WELLIST.WELL, EVENT.E_KEY, EVENT.JOB_TYPE, EVENT.START_DATE, EVENT.END_DATE, Max(DAILY.DDATE) AS MaxOfDDATE
FROM WELLIST INNER JOIN (DAILY INNER JOIN EVENT ON DAILY.E_KEY = EVENT.E_KEY) ON (WELLIST.I_KEY = EVENT.I_KEY) AND (WELLIST.I_KEY = DAILY.I_KEY)
WHERE (((EVENT.END_DATE) Is Null))
GROUP BY WELLIST.WELL, EVENT.E_KEY, EVENT.JOB_TYPE, EVENT.START_DATE, EVENT.END_DATE
HAVING (((EVENT.START_DATE)<#1/1/2001#))
ORDER BY WELLIST.WELL

Note that this has some extra lines not needed just to get the max DDATE.

Kjell