Select with Conditions

  • Thread starter Thread starter briank
  • Start date Start date
B

briank

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
 
Subqueries? what is that? :-)



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




Vanderghast, Access MVP
 
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 said:
Subqueries? what is that? :-)



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




Vanderghast, Access MVP


briank said:
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
Click to expand...
Click to expand...
 
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
 
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 said:
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 said:
Subqueries? what is that? :-)



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




Vanderghast, Access MVP


briank said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top