By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Query Max values in repeating data

P: n/a
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

Nov 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1

Nov 16 '06 #2

P: n/a
Thanks HTH, your solution worked perfectly.
kingston via AccessMonster.com 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.com
http://www.accessmonster.com/Uwe/For...ccess/200611/1
Nov 16 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.