Problem to output all fields using aggregate function 1

[RinaGreen]

Hi everybody

I created the following SQL query and it does work except I need all other fields in the output.

select id,Max(dt_day)
FROM DenLab
GROUP BY id;

However I found out that whenever I add some of the other fields to select clause, it doesn't give me correct answer

for example:

select id, other field ,Max(dt_day)
FROM DenLab
GROUP BY id;

But I do need other fields!11

Is there any way to do it?

Thank you in advance

Rina

 

[cmmrfrds]

If you need only the values that go along with the max day.

Easiest in Access to use 2 queries.

select id, Max(dt_day) as mDay
FROM DenLab
GROUP BY id;

Save as qMaxDay.

select * from DenLab
Inner join qMaxDay
On qMaxDay.id = DenLab.id
and qMaxDay.mDay = DenLab.dt_day

 

[lespaul]

you have to add all the fields in the SELECT clause to the GROUP BY clause:

select id, other field ,Max(dt_day)
FROM DenLab
GROUP BY id, other field;


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual