Using MAX in Query 2

[alectek]

Hi, I need to create a query to get only Max value.
I’m using MS Access 2003.
The table looks like:
ClientID Month Project FTE
1 3 A 0.25
1 3 B 0.75
2 4 B 0.35
2 4 A 0.65

I need result witch will looks like:
ClientID Month Project FTE
1 3 B 0.75
2 4 A 0.65

Therefore, I need Project where Client has MAX FTE.
This code returning not only rows with Max FTE, but all rows.

SELECT Client ID, Month, Project, FTE
FROM Actual Hours by Position
GROUP BY Client ID, Month, Project, FTE
HAVING (FTE = Max(FTE);
Can you please help me solve this problem?


Thanks.
Alec.

 

[vise]

Try the DMax function:
DMax ( expression, domain, [criteria] )
Remember add whatever your table name is in the function so if its called tblClients put "tblClients" where
"{TABLE NAME}" in the code bellow:
--

SELECT Client ID, Month, Project, FTE
FROM Actual Hours by Position
GROUP BY Client ID, Month, Project, FTE 
HAVING (FTE = DMax("FTE","{TABLE NAME}","[Client ID]=" & [Client ID]);

 

[PHV]

And what about this ?
SELECT A.[Client ID], A.Month, A.Project, A.FTE
FROM [Actual Hours by Position] AS A INNER JOIN (
SELECT [Client ID], Max(FTE) AS maxFTE FROM [Actual Hours by Position] GROUP BY [Client ID]
) AS M ON A.[Client ID] = M.[Client ID] AND A.FTE = M.maxFTE


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vise]

I don't know if it will work with HAVING so try instead WHERE with the Dmax function

 

[vise]

With table and where statement.

SELECT Client ID, Month, Project, FTE
FROM [Actual Hours by Position]
WHERE (FTE = DMax("FTE","[Actual Hours by Position]","[ClientID]=" & [ClientID]))
GROUP BY Client ID, Month, Project, FTE ;

 

[alectek]

Brilliant!!!
Thank you very much!


Thanks.
Alec.

 

[alectek]

PHV,
I wasn't clear to explaining all cases.
To do that, I need add one more record in the table (record #3)

ClientID Month Project FTE
1 3 A 0.25
1 3 B 0.75
1 2 B 0.5
2 4 B 0.35
2 4 A 0.65

I need result witch will looks like:
ClientID Month Project FTE
1 3 B 0.75
2 4 A 0.65
1 2 B 0.5

So, I need Max per every (ClientID & Month)...thanks at advance...


Thanks.
Alec.

 

[PHV]

SELECT A.[Client ID], A.Month, A.Project, A.FTE
FROM [Actual Hours by Position] AS A INNER JOIN (
SELECT [Client ID], [Month], Max(FTE) AS maxFTE FROM [Actual Hours by Position] GROUP BY [Client ID], [Month]
) AS M ON A.[Client ID] = M.[Client ID] AND A.Month = M.Month AND A.FTE = M.maxFTE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886