Max value from differents fields (Access table)

I already had posted the question below where I received a good solution.
But my intial question was incomplete.

Posted question:
Is their any function where I can have the maximum between differents fields ?
Example:
Table:
tb_Sales

Fields:
Jan_Sales = 2000
Feb_Sales= 3000
Mar_Sales=2000

In a query, is their a function like
Max(Jan_Sales,Feb_Sales, Mar_Sales)
that  will give me 3000

Solution received:
Select Max(T.Sales) as MaxSales
from
(
select Jan_Sales as Sales from tb_Sales
Union all
select Feb_Sales as Sales from tb_Sales
Union all
select Mar_Sales as Sales from tb_Sales
) as T

New question
How can I include the client numbert in the in the SQL below:
Example:
Client_number   maxSales
001            3000
002            6000

SQL Solution:
Select Max(T.Sales) as MaxSales
from
(
select Jan_Sales as Sales from tb_Sales
Union all
select Feb_Sales as Sales from tb_Sales
Union all
select Mar_Sales as Sales from tb_Sales
) as T


Thanks
Karl001Asked:
Who is Participating?
 
mbizupCommented:
Try this:

Select t.ClientID, Max(T.Sales) as MaxSales
from
(
select ClientID, Jan_Sales as Sales from tb_Sales
Union all
select ClientID, Feb_Sales as Sales from tb_Sales
Union all
select ClientID, Mar_Sales as Sales from tb_Sales
) as T
GROUP BY t.ClientID

Open in new window

0
 
Karl001Author Commented:
thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.