From: briank on 29 Mar 2010 09:10 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 29 Mar 2010 09:47 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 29 Mar 2010 10:12 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 29 Mar 2010 10:32 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 29 Mar 2010 10:34 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 >>
|
Pages: 1 Prev: update crosstab Next: Store Employee ID in table, Display User Name in form |