From: briank on
I would like to query my table to output [Name] and [Location] based upon the
presense of multiple entries in [Location]. In my example below, "Jones" and
"Smith" will output in my query since they were in [Location] in different
entries (Seattle and Cleveland). To compare: Lawson has two entries too but
they were the same so he will not show up on my query. Is this a good time
to use a subquery? TY Brian

[Name] [Location]
Jones Seattle
Smith Seattle
Lawson Annapolis
Jones Cleveland
Smith Cleveland
Lawson Annapolis

Desired Output
[Name] [Location]
Jones Seattle
Jones Cleveland
Smith Seattle
Smith Cleveland
From: vanderghast on
Subqueries? what is that? :-)



SELECT name, location
FROM somewhere
GROUP BY name, location
HAVING COUNT(*) = 1




Vanderghast, Access MVP


"briank" <briank(a)discussions.microsoft.com> wrote in message
news:0A523138-5E5A-406F-99C6-738BF8A4FC84(a)microsoft.com...
>I would like to query my table to output [Name] and [Location] based upon
>the
> presense of multiple entries in [Location]. In my example below, "Jones"
> and
> "Smith" will output in my query since they were in [Location] in different
> entries (Seattle and Cleveland). To compare: Lawson has two entries too
> but
> they were the same so he will not show up on my query. Is this a good
> time
> to use a subquery? TY Brian
>
> [Name] [Location]
> Jones Seattle
> Smith Seattle
> Lawson Annapolis
> Jones Cleveland
> Smith Cleveland
> Lawson Annapolis
>
> Desired Output
> [Name] [Location]
> Jones Seattle
> Jones Cleveland
> Smith Seattle
> Smith Cleveland

From: briank on
Thank you for the help. However I am getting an error in my SS2K5 SS Mgt
Studio Execute window. Is thtere something obvious that I'm missing?

Column 'tblMain.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Is there something obvious in my SQL that I'm missing here?

"vanderghast" wrote:

> Subqueries? what is that? :-)
>
>
>
> SELECT name, location
> FROM somewhere
> GROUP BY name, location
> HAVING COUNT(*) = 1
>
>
>
>
> Vanderghast, Access MVP
>
>
> "briank" <briank(a)discussions.microsoft.com> wrote in message
> news:0A523138-5E5A-406F-99C6-738BF8A4FC84(a)microsoft.com...
> >I would like to query my table to output [Name] and [Location] based upon
> >the
> > presense of multiple entries in [Location]. In my example below, "Jones"
> > and
> > "Smith" will output in my query since they were in [Location] in different
> > entries (Seattle and Cleveland). To compare: Lawson has two entries too
> > but
> > they were the same so he will not show up on my query. Is this a good
> > time
> > to use a subquery? TY Brian
> >
> > [Name] [Location]
> > Jones Seattle
> > Smith Seattle
> > Lawson Annapolis
> > Jones Cleveland
> > Smith Cleveland
> > Lawson Annapolis
> >
> > Desired Output
> > [Name] [Location]
> > Jones Seattle
> > Jones Cleveland
> > Smith Seattle
> > Smith Cleveland
>
From: John Spencer on
This query would identify all person's that have two (or more) locations.

SELECT [Name]
FROM [SomeTable]
GROUP BY [Name]
HAVING Max(Location) <> Min(Location)

You can use that in a subquery to list the records you want.
SELECT *
FROM [SomeTable]
WHERE [Name] in
(
SELECT [Name]
FROM [SomeTable]
GROUP BY [Name]
HAVING Max(Location) <> Min(Location)
)


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

briank wrote:
> I would like to query my table to output [Name] and [Location] based upon the
> presense of multiple entries in [Location]. In my example below, "Jones" and
> "Smith" will output in my query since they were in [Location] in different
> entries (Seattle and Cleveland). To compare: Lawson has two entries too but
> they were the same so he will not show up on my query. Is this a good time
> to use a subquery? TY Brian
>
> [Name] [Location]
> Jones Seattle
> Smith Seattle
> Lawson Annapolis
> Jones Cleveland
> Smith Cleveland
> Lawson Annapolis
>
> Desired Output
> [Name] [Location]
> Jones Seattle
> Jones Cleveland
> Smith Seattle
> Smith Cleveland
From: vanderghast on
You changed for the proper fields names in the SELECT ***and*** in the GROUP
BY clauses? You may need [ ] around the names if they are reserved words,
or ill formed names.

Vanderghast, Access MVP


"briank" <briank(a)discussions.microsoft.com> wrote in message
news:39F5BF9A-0CF1-4310-B82B-E2BA305C55AC(a)microsoft.com...
> Thank you for the help. However I am getting an error in my SS2K5 SS Mgt
> Studio Execute window. Is thtere something obvious that I'm missing?
>
> Column 'tblMain.Name' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
>
> Is there something obvious in my SQL that I'm missing here?
>
> "vanderghast" wrote:
>
>> Subqueries? what is that? :-)
>>
>>
>>
>> SELECT name, location
>> FROM somewhere
>> GROUP BY name, location
>> HAVING COUNT(*) = 1
>>
>>
>>
>>
>> Vanderghast, Access MVP
>>
>>
>> "briank" <briank(a)discussions.microsoft.com> wrote in message
>> news:0A523138-5E5A-406F-99C6-738BF8A4FC84(a)microsoft.com...
>> >I would like to query my table to output [Name] and [Location] based
>> >upon
>> >the
>> > presense of multiple entries in [Location]. In my example below,
>> > "Jones"
>> > and
>> > "Smith" will output in my query since they were in [Location] in
>> > different
>> > entries (Seattle and Cleveland). To compare: Lawson has two entries
>> > too
>> > but
>> > they were the same so he will not show up on my query. Is this a good
>> > time
>> > to use a subquery? TY Brian
>> >
>> > [Name] [Location]
>> > Jones Seattle
>> > Smith Seattle
>> > Lawson Annapolis
>> > Jones Cleveland
>> > Smith Cleveland
>> > Lawson Annapolis
>> >
>> > Desired Output
>> > [Name] [Location]
>> > Jones Seattle
>> > Jones Cleveland
>> > Smith Seattle
>> > Smith Cleveland
>>