There is a table like this:

id, date, value

I would like to see a result like this:

id, MAX(date), value, MIN(date), value

I got as far as:

SELECT t1.Id, t1.wert, t1.datum
FROM Tabelle1 AS t1
WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)

But

SELECT t1.Id, t1.wert, t1.datum, t2.wert, t2.datum
FROM Tabelle1 AS t1, Tabelle1 as t2
WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)
AND t1.id = t2.id
AND t2.datum = (Select min(t2.datum) from Tabelle 1 as t2);

doesn't work. Is this because the syntax is wrong or because Access can't do it?

I would suggest to use a self join on id column and get one set of data from one instance and wnd set from the other insatnce.. this should work..

Vishal

Thanks visheetal,

but can you specify what you mean? It sounds like my example (which doesn't work) to me.

select a.id,a.date,a.date,b.date,b.val from
a , b
where
a.id = b.id
and a.date = (select max(date) from aa)
and b.date = (select min(date) from aa).....

Well, that what I tried in my example - it results in an empty recordSet.

Mine:
SELECT t1.Id, t1.wert, t1.datum, t2.wert, t2.datum
FROM Tabelle1 AS t1, Tabelle1 as t2
WHERE t1.datum = (Select max(t1.datum) from Tabelle1 as t1)
AND t1.id = t2.id
AND t2.datum = (Select min(t2.datum) from Tabelle1 as t2);

Yours:
select a.id,a.date,a.date,b.date,b.val from
a , b
where
a.id = b.id
and a.date = (select max(date) from aa)
and b.date = (select min(date) from aa).....

Could you post a message showing the sample result you'd like to see and a tiny MDB with a set of data that you'd like us to use? I think this would help a lot.

-PatP

My first message says what the result should look like.

I should add that I whould like to see the values for the max and min Dates for each Id. Example:

Id is a Patient id and the Dates are times of blood samples. I whould like to compare the values from the first sample with the values for the very last - for each patient.

Thanks a lot!