search for max value

  • Thread starter Thread starter Beans
  • Start date Start date
B

Beans

I have two tables that have a relationship, mapunit and component. there can
only be a single record for each mapunit, however, each single mapunit can
have many components. my question is how to structure a query to search
through all components and return the maximum value, such as percent
composition of the mapunit? ex.--

<<--mapunit table
| mapunit name
| Alpha
|
|-->>component table
comp. name comp %
beta 75
zeta 25

In this case I'd would like to I would be returned the beta component. Is
this possible in the query builder?

Thanks for any feedback!
 
Not sure I fully understand your data structure, so take this as a notion
only...

Access queries can be "Totals" queries. If you were to create a new query,
joining the two tables, then change it to a Totals query, you could GroupBy
the [mapunit_name] and use the Maximum aggregation on the [comp%] field.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top