priority on one criteria

[smayshar]

Hello friends,
I have a table with fields: clientID, enterDate ...
What will be my sql first find a clientID's record then the latest date from those records

thenk

 

[Aqif]

Hi,

An immediate solution could be

SELECT ID, DLookUp("Max(DateE)","Table1","ID=" & [ID]) AS Expr1 FROM Table1
GROUP BY ID, DLookUp("Max(DateE)","Table1","ID=" & [ID]);

But in ideal situation you should use nested loop. I will try to post the syntax in few minutes

Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[Aqif]

Hi again,

The nested query look like this. The result is same but with a diff approach

SELECT I.ID, (Select Max(DateE) from Table1 as T where T.ID=I.ID) AS LastDate
FROM Table1 AS I
Group By ID;

Hope it helps

Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.

 

[smayshar]

thenks alot
worked perfect

 

[dhookom]

Why not just:

SELECT ID, Max(DateE) as MaxDate
from Table1
Group By ID;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[smayshar]

I had to use( where T.ID=I.ID)
only one id needed
thenks again