How to select biggest value in a field in a Query?

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
I have a table with a field called Sales like this

City, sales
============
City1, 100
City2, 200
City3, 50

What query can select 200 ?
I tried select Max(Sales) from TableA and it failed.

Help...
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

bobinnc

Active Member
Joined
Nov 4, 2006
Messages
281
If your query returned a value of 50, it is most likely that you defined the "Sales" field as text. You can either redefine it as a number, in which case your query should not work. Or you can change your query to evaluate the value of the text field using Max(Val(Sales))
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
If you want the maximum sales and nothing else use DMax.

DMax("[Sales]", "[Sales]")

That may look a little strange but the reason for that is you've named the field the same as the table.

Which isn't really a good idea.:)
 

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
Thanks. It works.i tried same formula but I did not use " to quote.
Great... thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top