ms access - sql group by one column and then filter on one column and create new columns for different values -

in ms access query, how can group on 1 column, using max id specific values table. know can group , use max max id not sure how multiple max ids.

if have table this:

+----+------+-------+------+ | id | col1 | col2  | col3 | +----+------+-------+------+ |  1 |    | 1   |  100 | |  2 |    | 2   |  200 | |  3 |    | 3 |  300 | |  4 |    | 4  |  400 | |  5 |    | 5  |  500 | |  6 | b    | 1   |  600 | |  7 | b    | 2   |  700 | |  8 | b    | 3 |  800 | |  9 | b    | 4  |  900 | | 10 | b    | 1   | 1000 | | 11 | b    | 3 | 1100 | | 12 | b    | 6   | 1200 | | 13 | c    | 1   | 1300 | | 14 | c    | 2   | 1400 | | 15 | c    | 3 | 1500 | | 16 | c    | 4  | 1600 | | 17 | c    | 6   | 1700 | | 18 | c    | 3 | 1800 | | 19 | c    | 2   | 1900 | | 20 | c    | 1   | 2000 | +----+------+-------+------+ 

and want this:

+------+------+------+-------+ | col1 | 1  | 2  | 3 | +------+------+------+-------+ |    |  100 |  200 |   300 | | b    | 1000 |  700 |  1100 | | c    | 2000 | 1900 |  1800 | +------+------+------+-------+ 

when col1 = b, , col2 = one, id = 6 , 10. since 10 larger gets value row 1000. same rest of table...

i cannot life of me figure out how this.

this returns results requested sample data in access 2007.

select     sub.col1,     dlookup('col3', 'yourtable', 'id=' & sub.maxofid1) [one],     dlookup('col3', 'yourtable', 'id=' & sub.maxofid2) [two],     dlookup('col3', 'yourtable', 'id=' & sub.maxofid3) [three]     (         select             y.col1,             max(iif(y.col2='one', y.id, 0)) maxofid1,             max(iif(y.col2='two', y.id, 0)) maxofid2,             max(iif(y.col2='three', y.id, 0)) maxofid3         yourtable y         group y.col1     ) sub;