473,324 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

aggregate functions

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
3 3982
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Ariel Jakobovits | last post by:
I have a table with 2 primary keys, one is a foreign key, the other is produced by a sequence. I want to SELECT query for one record that has a list of the sequence-produced values for all...
1
by: Job Lot | last post by:
Is it possible to use Aggregate functions with GROUP BY Clauses on DataTable. I have a DataTable with following values: Date Amount Int Balance 1/1/2004 5000.00 50.00 5050.00...
2
by: jc | last post by:
Hi. Just as we have AVG(COLUMN_NAME) and MAX(COLUMN_NAME) how can I write my own variation of a such a function. If I can appreciate how to do this, then I hopefully I can write a...
10
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to...
5
by: David Garamond | last post by:
What do people think of adding some more aggregate functions. These are the ones that MySQL has and PG doesn't: - STD/STDDEV - VARIANCE - BIT_OR - BIT_AND - GROUP_CONCAT (for strings, added...
3
by: S P Arif Sahari Wibowo | last post by:
Hi! I would like to make an editable continous form, where most fields will be from table A and editable, except 1-3 fields are a glimpse into table B and uneditable. Table A relate to table B...
8
by: jefftyzzer | last post by:
The current issue of "Oracle Magazine" has an article on creating custom aggregate functions, which naturally got me thinking about how to do this in DB2. I found some articles on creating...
5
by: Dean | last post by:
Has anyone toiled with creating/using alternate domain aggregate functions? I have been messing with that a little. The one recordsource I have been working indicates I get 20 to 40% savings in...
0
by: BillCo | last post by:
just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
5
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.