Grouping

[JoeSmoe55]

Is there a way to return the value of a column where there exists a row for each type that I specify for example, if I have the following data:

K M M G
1 123 Five 6
5 123 eight 2
3 123 four 6
6 123 Five 3
4 123 one 6
1 123 Five 4
5 123 ten 6
3 123 four 4
2 123 three 2
1 123 Five 3

I want the value of the 'G' where for each distinct value of 'G' there exists a row where 'K' is equal to 1 AND 3.

So the subset I would like to be returned is
1 123 Five 6 *
3 123 four 6 *
1 123 Five 4 *
3 123 four 4 *

So the values that I am really after is 4 and 6. Do that make sense? I think it would have to be done through functions specific to Access if it is possible within one SQL statement. Thanks in advance.

Joe

 

[dhookom]

From your sample data, all you should need is:
SELECT K, M, OtherM, Max(G) as MaxG
FROM tblYourUnNamedTable
GROUP BY K, M, OtherM
WHERE K IN (1,3);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[r937]

dhookum, you do realize that it is not allowed to have a WHERE clause after the GROUP BY, right?

joe, try this --

select G 
  from yourtable
 where K in (1,3)
group
    by G
having count(*) = 2

r937.com | rudy.ca

 

[dhookom]

Good catch on the "dangling where".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]