search for max value

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!
 
J

Jeff Boyce

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Help: Max Value in A Row 1
Append Query Null Value 11
Group By, Max, many to many 1
Max value form a group 7
selecting the right 'MAX' record 3
select max value from multiple fields within a record 2
multiple records from one 1
Crosstab Value field formula too long, min() and max() function help sought 5

Top