Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Selecting the best record in a subset in a table

Posted on 2014-01-22
3
Medium Priority
?
324 Views
Last Modified: 2014-01-22
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
0
Comment
Question by:Fordraiders
3 Comments
 
LVL 40

Expert Comment

by:PatHartman
ID: 39800859
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
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39800904
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
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 39801843
Thanks very much
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question