Help about max value

[lockgun]

Well,i use two tables.in one table i have studentsid-name-lastname-age and in the other table i have studentsid and averagegarde.


i used a query to display name-lastname-age-averagegrade and i put averagegrade a total max.the problem is that when i run it it shows all of the records and not just the max averagegrade with name and lastname and age.Any help?

[ItsMe]

There is a function that will return the Max value. It is a Domain function and requires its own trip to the data, separate from the original SQL. When I use them I use them as an expression in an unbound control on a form or in VBA behind a form.

Something like this could be placed in a control's Control Source property.
= DMax("[FieldName]", "qryName")

[lockgun]

well..because i am a beginner in access i cant understand what you tell me to do..isnt there a way to display what i want more simply?

[ItsMe]

If you wanted the smallest or first value in your recordset you could add some code to your query's SQL, pretty simple. I am not aware of an SQL operator that looks for the largest value.

Using unbound controls is about as basic as it gets. I gave you a snippit to use. Just adjust it to match the names of your query and the field name that has the value you are searching for.

Do you not have forms in your application?

[lockgun]

no i didnt have to make any forms..

[ItsMe]

There is almost always a way to get things done. What you are asking for is generally managed in a form or a report. You could probably incorporate some functions in your query object and have it operate autonomously but, I do not think that would be the simplest approach.

Not using forms is unconventional so I don't have a solution catered to your request at the tip of my tongue. Maybe some grouping of alias' along with another Max() function would do the trick. I really do not know. Most built in functions are for use in VBA, not SQL. In a query you are limited to Access' version of SQL. I am not an expert in SQL but I am not aware of an option for last or greatest record. I will do another search though....

[lockgun]

ok thank you for your time!!

[ItsMe]

There is a way to get the entire thing done in a single SQL statement. However, it is anything but simple and may not be as dynamic as if you used a form to retrieve the data. I am referring to dynamically updating the recordset or the data retrieved.

Consider the following SQL:

SELECT Last(RSVP.GuestID) AS LastOne, Min(RSVP.AutoNum) AS MinNum
FROM RSVP;

This will retrieve two unrelated records in a single row of a recordset. It employs two functions and assigns them to an alias, ie AS MinNum

An alias will be displayed as a field name.

As soon as I add another field that is not an alias, the Last() function and the Min() function will not work together. I will get an aggregate function error. This is what I was eluding to earlier about grouping with alias', and it is still rather complex.

You can create an SQL statement that uses functions and alias’ across the board. If you add a field straight from a table without using a function, you will get an error. If you can get creative and build functions (across the board) to return the data you need, the SQL will work.

Clear as mud?

[June7]

The TOP N parameter might help you. Review:

http://www.w3schools.com/sql/sql_top.asp

http://allenbrowne.com/subquery-01.html#TopN

[ItsMe]

SELECT TOP was the first thing I considered. At the time I forgot about combining it with ORDER BY XX.XX DESC

However, I still don't see an easy way to pull a function like Avg() without running the function first, in its own query, and then employing TOP N.

[ItsMe]

@ lockgun

Can you switch from design view in your Query to SQL view? Copy the SQL and post it here. Use the "Go Advanced" button here in the forum and then use the # hashtag to create CODE tags to paste your SQL code here in the forum.

Pasting your code in CODE tags will help to preserve the formatting and make it easier for us to read.

[June7]

Yes, might require 2 query objects or one query object with nested subquery.