Grouping Common Values in one field with the lowest corresponding value in another...

[JustMePatrick1979]

I'm not sure the subject line accurately describes what I am getting at.

Example 1: I have some Columns, and in those columns, say Field 2 as some numbers, and Field 3 has some numbers. Fields, Doesn't matter, too much but I am likely to display it.

Fields | Fields| Fields

123 2 111
123 4 111
567 6 222
567 8 222


Example 2, what I want to see is:
Field1 | Field2| Field3

123 2 111
567 6 222

Is it possible to do this?



Thanks for your insights!

[June7]

Looks like a simple aggregate (GROUP BY) query.

SELECT Field1, Field3, Min(Field2) AS Min2 FROM table GROUP BY Field1, Field3;

[orange]

JMP,

If you were describing your requirement and logic in simple, plain English---what would that be?
I think June has made a great guess, but it is a guess.

Welcome to the forum.

[JustMePatrick1979]

JMP,

If you were describing your requirement and logic in simple, plain English---what would that be?
I think June has made a great guess, but it is a guess.

Welcome to the forum.

That was my problem, trying to articulate it in simple, plain English.

June was heading in right direction with their reply. I was trying to do it in an existing query. However, I needed to run it in a separate query with the source being the original query.

Shortly after this post I found this post on a different forum: https://access-programmers.co.uk/for...d.php?t=135168 That really helped.

[Micron]

That was my problem, trying to articulate it in simple, plain English.

For each value in field1 I want the Max value from field2 and the value from field3 from the same record as Max of field2?
Or did I mess that up?

[June7]

Well, your example shows the min of field 2. It also shows Field3 is the same value for each Field1 group. Perhaps you need to provide more realistic data sample.

Possibly need to use TOP N per group subquery. Review http://allenbrowne.com/subquery-01.html#TopN