Prev: testing for numeric or alpha
Next: Returning A Varibale from a query
From: KT on 17 Mar 2010 15:11
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
From: John Spencer on 17 Mar 2010 16:54
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

KT wrote:
> 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
From: KARL DEWEY on 17 Mar 2010 17:53
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]);

--
Build a little, test a little.


"KT" wrote:

> 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
From: KT on 17 Mar 2010 18:01
Perfect, Thanks.


"John Spencer" wrote:

> 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
>
> KT wrote:
> > 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
> .
>
 | 
Pages: 1
Prev: testing for numeric or alpha
Next: Returning A Varibale from a query