Selecting the best record in a subset in a table

Access 2010

I have a table called "Belts"

Fields:
Item-Text
Mfrnum-Text
Mfrname - Text
Score-Text
REDBOOKPAGE-Text
Tag-Text


In Table I have repeating values in the Mfrnum field when a filter is in place for the Mfrnum field for a particular value like as shown.
In this case i'm filtering "1000H075" in the table.

Example of a filterWhat I need: based on the "Score" field. What ever score record  has the highest value in the "Score" field. Then a "Y" gets placed  in the "Tag" Field for only the highest value score.


In the case where the highest value is the same pick the top records in the filter.

I'm indexing by  "Mfrnum" asc... and then  "Score"  descending in the filter.


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dale FyeConnect With a Mentor Commented:
Tag is a reserved word, so you might want to think of a different field name so you don't have to wrap it in [ ] every time you use it.

Do you only want to do this when you filter the data, or do you want to update it for all MFRNUM in your table?

You might try:

UPDATE Belts as B
SET [Tag] = "Y"
WHERE [Score] = DMAX("Score", "Belts", "MFRNum = '" & B.MFRNUM & "'")

This would update [Tag] to "Y" for the record with the maximum [Score] within each MFRNUM.
FROM
0
 
PatHartmanCommented:
If you are talking about the Tag property of a control on the form, this won't work.  Access maintains only a single instance of values for continuous/datasheet forms so setting the Tag property of a control sets it for all instances of the control.

Tell us more about what you need this for.  You should be able to find the record by sorting the records descending by the score and then adding  Top 1 to the query.
0
 
FordraidersAuthor Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.