Query filtering by Aggregate

  • Thread starter Thread starter KT
  • Start date Start date
K

KT

What is the best way to accomplish this in a query. I have records where I
want to return only records where one returned value only is matched with one
aggregate value of another field. Tough for me to explain. Here's an
example:

1 P-0001 H
2 P-0001 H
3 P-0001 H
4 P-0002 H
5 P-0002 G

The above is a table of three fields, the first is the Primary Key. In this
example I'd want to return only records 1, 2 and 3, because the value in the
second field is identical AND all records containing this second field value
also contain identical values in the third field.

Thanks
 
SELECT *
FROM SOMETable
WHERE Column2 in
(SELECT Column2
FROM SomeTable
GROUP BY Column2
HAVING Min(Column3) = Max(Column3))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Try this --
SELECT YourTable.*
FROM YourTable
WHERE [YourTable].[Field2] & [YourTable].[Field3] = (SELECT [XX].[Field2] &
[XX].[Field3] FROM YourTable AS [XX] WHERE Count([XX].[Field2] &
[XX].[Field3]) >1 GROUP BY [XX].[Field2] & [XX].[Field3]);
 
Perfect, Thanks.


John Spencer said:
SELECT *
FROM SOMETable
WHERE Column2 in
(SELECT Column2
FROM SomeTable
GROUP BY Column2
HAVING Min(Column3) = Max(Column3))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

.
Click to expand...
 
Back
Top