Query Max values in repeating data

[brob]

I am Access newbie looking for some guidance in putting together a
query that can output a table of max values in a given column grouped
by a couple of other columns. Here is an example of what I need the
query to do.

Existing Data
Table:Z
A B C D E
1 1 1 7 12
1 1 2 6 43
1 1 3 3 22
1 2 1 4 2
1 2 2 3 123
1 2 3 4 27
2 1 1 5 13
2 1 2 6 6
2 1 3 6 312
2 2 1 3 27
2 2 2 8 27
2 2 3 9 4

Query Result
Table:Y
A B C D E
1 1 2 6 43
1 2 2 3 123
2 1 3 6 312
2 2 2 8 27

I need to find the maximum value in column E grouped by columns A and
B. In the case of a tie in column E, the row with the largest value in
column C should be selected. Any help would be much appreciated.

Thanks in advance,
Brent

[kingston via AccessMonster.com]

Create a totals query:

Group By [A], [b]
Max [E]

Join this query to the original table via all three fields [A], [b], and [E].
Output all of the fields.
If there is a tie, make a totals query on the preceding query:

Group By [A], [b], [E]
Max [C]

Then join this query to the original table via all four fields and output all
five fields. HTH

brob wrote:

>I am Access newbie looking for some guidance in putting together a
query that can output a table of max values in a given column grouped
by a couple of other columns. Here is an example of what I need the
query to do.

Existing Data
Table:Z
A B C D E
1 1 1 7 12
1 1 2 6 43
1 1 3 3 22
1 2 1 4 2
1 2 2 3 123
1 2 3 4 27
2 1 1 5 13
2 1 2 6 6
2 1 3 6 312
2 2 1 3 27
2 2 2 8 27
2 2 3 9 4

Query Result
Table:Y
A B C D E
1 1 2 6 43
1 2 2 3 123
2 1 3 6 312
2 2 2 8 27

I need to find the maximum value in column E grouped by columns A and
B. In the case of a tie in column E, the row with the largest value in
column C should be selected. Any help would be much appreciated.

Thanks in advance,
Brent

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1

[brob]

Thanks HTH, your solution worked perfectly.
kingston via AccessMonster.c om wrote:

Create a totals query:

Group By [A], [b]
Max [E]

Join this query to the original table via all three fields [A], [b], and [E].
Output all of the fields.
If there is a tie, make a totals query on the preceding query:

Group By [A], [b], [E]
Max [C]

Then join this query to the original table via all four fields and output all
five fields. HTH

brob wrote:

I am Access newbie looking for some guidance in putting together a
query that can output a table of max values in a given column grouped
by a couple of other columns. Here is an example of what I need the
query to do.

Existing Data
Table:Z
A B C D E
1 1 1 7 12
1 1 2 6 43
1 1 3 3 22
1 2 1 4 2
1 2 2 3 123
1 2 3 4 27
2 1 1 5 13
2 1 2 6 6
2 1 3 6 312
2 2 1 3 27
2 2 2 8 27
2 2 3 9 4

Query Result
Table:Y
A B C D E
1 1 2 6 43
1 2 2 3 123
2 1 3 6 312
2 2 2 8 27

I need to find the maximum value in column E grouped by columns A and
B. In the case of a tie in column E, the row with the largest value in
column C should be selected. Any help would be much appreciated.

Thanks in advance,
Brent

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200611/1