Query Design Question

[copegjc15]

Hello all,



I am trying to run a query in Access 2007 that will return the record that has highest value for a certain field.

For example, I have the following data:

Field 1
Field 2
Field 3
Field 4

In Record #1 I have the following data:

Field 1 - 10230
Field 2 - A
Field 3 - 1
Field 4 - 75

In Record #2 I have the following data:

Field 1 - 10230
Field 2 - A
Field 3 - 2
Field 4 - 23

In Record #3 I have the following data:

Field 1 - 10245
Field 2 - A
Field 3 - 6
Field 4 - 33

When field 1 has the same data, I want my query to display the records where field 4 has the greatest value. So, in the end, this query should display Record #1 (in red) and Record #3 (in blue).

How would I do this in the query design?

Any assistance would be greatly appreciated. Thank you for taking the time to read this.

Greg

[pbaldy]

You should be able to adapt this method:

http://www.baldyweb.com/LastValue.htm

[copegjc15]

Thanks, however, I am kind of a newbie here.

How / Where would I incorporate all of that info as I am using the Query Design method.

Would I just use the build function, and then use that method and substitute my field names?

[copegjc15]

Thought I had figured it out...

Back to the drawing board

[copegjc15]

You should be able to adapt this method:

http://www.baldyweb.com/LastValue.htm

Where in Access would I actually put this code? I am using the basic query design method, and am not too familiar with any type of coding.

Greg

[pbaldy]

There's no code involved, just 2 queries (what you see on my site is the SQL view of the queries). The first would use your field1 and field4 instead of mine, grouping on 1 and max on 4. You would need to click on the totals button to see those options (looks like a Greek E).

[copegjc15]

THANKS A LOT!!!

That worked.

[pbaldy]

Excellent, glad it worked for you. Welcome to the site by the way!