Display Maximum Value of a Field

[Llobid]

Hi, folks. I'm trying to do something that seems very simple, but I've never tried to do it before. I've searched the forum with many different search criteria, but can't find a solution.

I want to create a criteria in a query that will return one record. That record will be the one in which the value of one field is the largest current numerical value.

For example, in Excel the function I would use might be Max(A2:A100). However, I can't seem to find how to return one record in an Access query in which the maximum value of one field is displayed.

Any help would be appreciated.

 

[raskew]

Hi -

This working example, using Northwind's Orders table returns the Max Freight field.

SELECT [COLOR="Red"]top 1[/COLOR] Orders.Freight, Orders.OrderID, Orders.OrderDate
FROM Orders
ORDER BY Orders.Freight DESC;

HTH - Bob

 

[Llobid]

More Guidance

Thanks Bob. However, I don't have a clue where to put this code. I was hoping there would be a simple function that could be put into the query criteria field that would cause the record to be returned. What I'm wanting to eventually do is put a command button on a form that would run the query showing the current maximum value of a particular field. I have a mathematics background, but my programming is weak.

Thanks for the assistance.

 

[raskew]

That's not code, it's the underlying SQL of a query created in query design-view.

Initially, it looked like (in SQL view)

SELECT Orders.Freight, Orders.OrderID, Orders.OrderDate
FROM Orders
ORDER BY Orders.Freight DESC;

Then, switching from design view to SQL view, added:

SELECT[COLOR="Red"] Top 1[/COLOR] Orders.Freight, Orders.OrderID, Orders.OrderDate
FROM Orders
ORDER BY Orders.Freight DESC;

This can also be done using the Top Values combo box on the tool bar, but you'll still need to switch to SQL view to modify the Top statement, since 1 is not an option in the combo box.

Try this:

1) Create your query in query design-view, placing the field you want the max of as the first field. Set sort-by as Descending.

2) Perform either of the two methods described above.

3) Run the query.

HTH - Bob

 

[Llobid]

Thanks

Thanks Bob. I've never actually used the SQL view of a query. This worked great! I'll have to look at that more often.

 

[raskew]

Glad it was helpful. Post back if you run into more problems.

Best Wishes - Bob

 

[The_Doc_Man]

The other way to do that is using the DMax function, which you can read about in the Help files.

 

[Brianwarnock]

There is a function Max but it requires you to use a Subquery .
Doc I thought domain functions where resource heavy?
First let's say that Bob's approach is fine, I offer this in the spirit of education.

SELECT Orders.Freight, Orders.OrderID, Orders.OrderDate
FROM Orders
Where ((Orders.Freight)=(Select Max(Orders.Freight) as MaxOrders from Freight));


Brian

 

[raskew]

Brian:

Change:
Where ((Orders.Freight)=(Select Max(Orders.Freight) as MaxOrders from Freight));
To:
Where ((Orders.Freight)=(Select Max(Orders.Freight) as MaxOrders from Orders));

Bob

 

[Brianwarnock]

Thanks Bob, thats the trouble with "air" code its not tested and our own syntax spelling errors etc are never spotted.

Brian