Solved

Selecting the best record in a subset in a table

Posted on 2014-01-22
3
301 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 35

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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

860 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