Expression to return MAX VALUES

[knpaddac]

I have a database of school information with the following field names:


county, school, year, enrollment

I want to be able to create a comparison of each school to the largest school in their county for each year, so I want to run a query that will return
1. school, 2. county, 3. enrollment, and 4. largest enrollment of any school in that county.

How would I put that 4th item in a query?

[June7]

Could use DMax() domain aggregate.

Or build a report with grouping on county and use Max() function in a textbox in group footer. Report allows display of detail data as well as aggregate calcs.

[knpaddac]

Could use DMax() domain aggregate.

Ive looked this up and it seems like it should work, however I am having trouble figuring out how to state the criteria portion. There are 800+ schools spread over 92 different counties and I want each line in the returned data to include the largest enrollment number for that school's particular county.

Currently, what I have looks like this:
DMax("[Enrollment]","Year","[County]")

This just returns the same value for every school and that value is the largest of all schools in the entire state. I know that it needs to be "[County]=SOMETHING" but I do not know what that SOMETHING needs to be to come back how I am wanting it.

[aytee111]

Create a separate totals query, include country (using GROUP BY) and enrollment (using MAX in the group by field). This will return one row/one value per country. Bring this query in to your original query, link by country.

[June7]

As aytee111 offered, an aggregate query joined to the data is another approach and probably perform faster than DMax(). If you want the Max for each Year for each County, group on both fields and then compound link on both County and Year fields.

Also, the syntax for DMax() would be:

DMax("[Enrollment]", "tablename", "[County]='" & [County] & "' AND [Year]=" & [Year])


BTW, year is a reserved word and should not use reserved words as names.