Look at field and show highest number for each same record

[Ms Kathy]

(Running Access 2007; limited understanding; trying to pull data together for a report)

My dilemma:
I have an Item# that has a production standard that may differ based on how the production line is run - (i.e. Item 1234 Prod Std 5; Item 1234 Prod Std 3 and so on). There is a production standard number "goal" that would be achieved when the line is running most efficiently. I have a report where they want to see this "goal" number for each Item regardless of the standard that is entered in this field - so (as shown in example above Item 1234 should show a Prod Std of 5 for both entries). I do not know how to accomplish this. I've tried the aggregate function "max" in a query which shows me the Max Prod Std for the one item entry; but I want this same number in all the records: so I want to 'look at the Prod Std field and show the highest number possible regardless of what is currently in that field'. Is this even possible?

(The easiest way is to simply change the production standard on each of the lines to reflect this one "best" standard; however this number is used elsewhere for other purposes; so I can't touch that.)

Thank you.

 

[Ranman256]

in your query bring down all the fields you want to see,
then Add the MAX(FIELD) too.

this should do it.

 

[Ms Kathy]

Thank you. But this doesn't give me what I' looking for. I have the same item number that can be run on different lines (2 primary keys). I want the production standard to show up on my report as the 'highest number available' regardless of what is entered in the field. There is more than one instance of the same item number - I find that when I enter MAX only one instance shows up. (I hope I'm making sense.)