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

aggregate functions

P: n/a
Hi,

I have 3 columns, I wish to group by one of them, choose one with max(), and
wish for the 3rd column to be in the same row as the one in the 2nd column.

Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking
(1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

Cheers,
Chris
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you really must use Roman numerals here, you will have to teach Access
how to order these.

Create another table with 2 fields:
- RomanNumber (text)
- SortAs (Number)

You can then join this table in a query to your other one, and choose the
max related value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Not Me" <su**@sumwhere.fake> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...

I have 3 columns, I wish to group by one of them, choose one with max(), and wish for the 3rd column to be in the same row as the one in the 2nd column.
Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking
(1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

Nov 12 '05 #2

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
"Not Me" <su**@sumwhere.fake> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...
I have 3 columns, I wish to group by one of them, choose one with max(), and
wish for the 3rd column to be in the same row as the one in the 2nd

column.

Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking (1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

If you really must use Roman numerals here, you will have to teach Access
how to order these.


Ah, sorry that was just an example, the information isn't really numbers,
letters and numerals!
Anyway, I don't want to do anything with the 3rd column, other than display
it (max is on 2nd column)
Nov 12 '05 #3

P: n/a
this worked for me, using your data sample
paste this is the sql-view of a query

SELECT a.id, b.maxF1 AS f1, a.f2
FROM table1 AS a, [SELECT id, Max(f1) AS MaxF1
FROM Table1
GROUP BY id]. AS b
WHERE (((a.id)=[b].[id]) AND ((a.f1)=[b].[maxF1]));
I just don't know why the design view can display it properly ?

the [SELECT ... ]. AS b creates a sub table (b) with the first 2
fields you want, and then I join it to the first table to get the
matching f2

"Not Me" <su**@sumwhere.fake> wrote in message news:<bq**********@ucsnew1.ncl.ac.uk>...
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:3f**********************@freenews.iinet.net.a u...
"Not Me" <su**@sumwhere.fake> wrote in message
news:bq**********@ucsnew1.ncl.ac.uk...
I have 3 columns, I wish to group by one of them, choose one with max(), and wish for the 3rd column to be in the same row as the one in the 2nd column.
Eg.

1 a i
1 b ii
2 a v
2 c iv
2 b i
3 d iii
3 c vi

if I group by the first column, choose max() on the second column (picking (1,b),(2,c),(3,d)) I want to choose column 3 from the same row

output should be

1 b ii
2 c iv
3 d iii

Hope that's clear enough! I don't want to group by the 3rd column also,
because that duplicates column 1.

If you really must use Roman numerals here, you will have to teach Access
how to order these.


Ah, sorry that was just an example, the information isn't really numbers,
letters and numerals!
Anyway, I don't want to do anything with the 3rd column, other than display
it (max is on 2nd column)

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.