Access Finding minimums by category in ACCESS

[AJGE]

I have a database that is tracking racers and their results in bike races.

Riders earn points based on their finish time as a % of the fastest finish time in their gender and ability category....

I have a select query that pulls the riders name, gender, ability, and finish time.

I have a calculated field [SORT] that adds their gender and ability.

I am trying to write a function that would determine the lowest finish time in each unique SORT category.....

I have used Dmin... but have hit a road block... no matter how I approach it, DMin simply returns the lowest finish time... it neglects the fact that the riders are in categories... which doesnt help.

Can DMin be written in a way that would post the minimum result for each unique value in another field?

ie.
If I have mens expert, mens sport, and mens beginner, as categories and then have several riders in each of those classes... I need to see the lowest mens sport time displayed next to any rider who is in that category.... as well as the same result for the other categories.
Right now I can get the lowest time overall displayed next to ALL the records.

I am a self taught access user... so I may be missing something obvious.

Any help would be appreciated.

AJ

 

[goodfellow]

I'm not sure how to do it with formula, but in SQL query you must use "group by" clause for that.
Maybye this will help: http://lmgtfy.com/?q=access+group+by

Cheers and good luck!

 

[alow]

In Access, you would do that in Design view easily, by turning the query into a totals query and set the function for the finish time as MIN. That should get you pretty close.

 

[AJGE]

Thanks Guys,

Im with you on how simple this seems it should be...

I am aware that the Totals Query with a "Group By" and "Min" should get me where I want to be... sadly though its not.

I can get the query to kick back the MIN, or lowest time... but it is not taking the riders category into account.

Ultimately I need a set of results that include every rider, their finish time, and a column that displays the lowest finish time in THEIR category... (ie. mens, womens, and each skill level would have different "best times").

This then allows me to compare each racer to the best time in their category, assign points, bobs your uncle and were done.

I was able to tackle this at the Report level using Grouping... by placing a calculated function as a control in the Category Sub Header... the calculation "re-ran" for each new heading level and thus I was able to compare each rider to the winner of their category... as you know though, you cannot sort by calculated fields in a report.

The root of my mission here is to build this calculation in at the Query level so that I am able to generate reports from this and properly sort finish positions, etc.

I appreciate the suggestions.... if anyone can help me dig deeper, I'll be in their debt!