Using SELECT DISTINCT
in [Queries]
|
Prev: MARNI handbags handbags - Best High Quality MARNI handbags handbags www.luxury-gift.org
Next: Filtering for "Not Something"
From: Leslie Isaacs on 30 Jun 2008 05:34 Hello All I have a table [x confirmed], and 2 of the fields are 'practice' and 'month name'. This table contains typically between 10 and 50 records for each combination of 'practice' and 'month name' values - so for e.g. there may be 22 records where 'practice' = "The Health Centre" and 'month name' = "May 2008". I need a query that will return the total number of UNIQUE values of 'practice' for a given value of 'month name' - i.e. I need to know how many different 'practice' values there are where 'month name' = "March 2008". I have tried this: SELECT DISTINCT [x confirmed].practice FROM [x confirmed] WHERE ((([x confirmed].[month name])="March 2008")) GROUP BY [x confirmed].practice; .... but this returns the total number of records where 'month name' = "March 2008". Adding the word "DISTINCT" seems to have made no difference to the result returned by the query. What have I done wrong? Hope somone can help. Many thanks Les
From: Dennis on 30 Jun 2008 05:48 Try this instead SELECT [x confirmed].Practice, [x confirmed].[month name] FROM [x conformed] GROUP BY [x confirmed].Practice, [x confirmed].[month name] HAVING ((([x confirmed].[month name])="March 2008")); "Leslie Isaacs" wrote: > Hello All > > I have a table [x confirmed], and 2 of the fields are 'practice' and 'month > name'. This table contains typically between 10 and 50 records for each > combination of 'practice' and 'month name' values - so for e.g. there may be > 22 records where 'practice' = "The Health Centre" and 'month name' = "May > 2008". I need a query that will return the total number of UNIQUE values of > 'practice' for a given value of 'month name' - i.e. I need to know how many > different 'practice' values there are where 'month name' = "March 2008". > > I have tried this: > > SELECT DISTINCT [x confirmed].practice > FROM [x confirmed] > WHERE ((([x confirmed].[month name])="March 2008")) > GROUP BY [x confirmed].practice; > > .... but this returns the total number of records where 'month name' = "March > 2008". Adding the word "DISTINCT" seems to have made no difference to the > result returned by the query. > > What have I done wrong? > > Hope somone can help. > Many thanks > Les > > >
From: Leslie Isaacs on 30 Jun 2008 06:15 Hello Dennis Thanks for your suggestion - but unfortunately it returned one record for each value of 'practice' (whereas I want the query to return just one row, showing that (say) there are 150 unique values of 'prac name' in [x confirmed] where 'month name' = "March 2008"). I'm sure this shouldn't be so difficult - but I just don't seem able to get it!! Hope you can help. Thanks again. Les "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:2FDE139A-E8F2-4879-89F0-AF23835D747A(a)microsoft.com... > Try this instead > > SELECT [x confirmed].Practice, [x confirmed].[month name] > FROM [x conformed] > GROUP BY [x confirmed].Practice, [x confirmed].[month name] > HAVING ((([x confirmed].[month name])="March 2008")); > > > "Leslie Isaacs" wrote: > > > Hello All > > > > I have a table [x confirmed], and 2 of the fields are 'practice' and 'month > > name'. This table contains typically between 10 and 50 records for each > > combination of 'practice' and 'month name' values - so for e.g. there may be > > 22 records where 'practice' = "The Health Centre" and 'month name' = "May > > 2008". I need a query that will return the total number of UNIQUE values of > > 'practice' for a given value of 'month name' - i.e. I need to know how many > > different 'practice' values there are where 'month name' = "March 2008". > > > > I have tried this: > > > > SELECT DISTINCT [x confirmed].practice > > FROM [x confirmed] > > WHERE ((([x confirmed].[month name])="March 2008")) > > GROUP BY [x confirmed].practice; > > > > .... but this returns the total number of records where 'month name' = "March > > 2008". Adding the word "DISTINCT" seems to have made no difference to the > > result returned by the query. > > > > What have I done wrong? > > > > Hope somone can help. > > Many thanks > > Les > > > > > >
From: Dennis on 30 Jun 2008 06:35 You could use a sub query by saving the current query as Temp and then using the below SELECT Count(Temp.practice) AS CountOfPractice FROM Temp; "Leslie Isaacs" wrote: > Hello Dennis > > Thanks for your suggestion - but unfortunately it returned one record for > each value of 'practice' (whereas I want the query to return just one row, > showing that (say) there are 150 unique values of 'prac name' in [x > confirmed] where 'month name' = "March 2008"). > > I'm sure this shouldn't be so difficult - but I just don't seem able to get > it!! > > Hope you can help. > > Thanks again. > Les > > > "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message > news:2FDE139A-E8F2-4879-89F0-AF23835D747A(a)microsoft.com... > > Try this instead > > > > SELECT [x confirmed].Practice, [x confirmed].[month name] > > FROM [x conformed] > > GROUP BY [x confirmed].Practice, [x confirmed].[month name] > > HAVING ((([x confirmed].[month name])="March 2008")); > > > > > > "Leslie Isaacs" wrote: > > > > > Hello All > > > > > > I have a table [x confirmed], and 2 of the fields are 'practice' and > 'month > > > name'. This table contains typically between 10 and 50 records for each > > > combination of 'practice' and 'month name' values - so for e.g. there > may be > > > 22 records where 'practice' = "The Health Centre" and 'month name' = > "May > > > 2008". I need a query that will return the total number of UNIQUE values > of > > > 'practice' for a given value of 'month name' - i.e. I need to know how > many > > > different 'practice' values there are where 'month name' = "March 2008". > > > > > > I have tried this: > > > > > > SELECT DISTINCT [x confirmed].practice > > > FROM [x confirmed] > > > WHERE ((([x confirmed].[month name])="March 2008")) > > > GROUP BY [x confirmed].practice; > > > > > > .... but this returns the total number of records where 'month name' = > "March > > > 2008". Adding the word "DISTINCT" seems to have made no difference to > the > > > result returned by the query. > > > > > > What have I done wrong? > > > > > > Hope somone can help. > > > Many thanks > > > Les > > > > > > > > > > > >
From: Leslie Isaacs on 30 Jun 2008 06:50
Dennis OK - I can see that would work, so I will use that ... if I have to! But shouldn't I be able to do this with a single query? Isn't that what DISTINCT is for? I wish I understood this! Thanks again - at least I can now get the answers I need. Les "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:8946EA05-3C30-432B-B4EF-B39C3F4367DD(a)microsoft.com... > You could use a sub query by saving the current query as Temp and then using > the below > > SELECT Count(Temp.practice) AS CountOfPractice FROM Temp; > > > "Leslie Isaacs" wrote: > > > Hello Dennis > > > > Thanks for your suggestion - but unfortunately it returned one record for > > each value of 'practice' (whereas I want the query to return just one row, > > showing that (say) there are 150 unique values of 'prac name' in [x > > confirmed] where 'month name' = "March 2008"). > > > > I'm sure this shouldn't be so difficult - but I just don't seem able to get > > it!! > > > > Hope you can help. > > > > Thanks again. > > Les > > > > > > "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message > > news:2FDE139A-E8F2-4879-89F0-AF23835D747A(a)microsoft.com... > > > Try this instead > > > > > > SELECT [x confirmed].Practice, [x confirmed].[month name] > > > FROM [x conformed] > > > GROUP BY [x confirmed].Practice, [x confirmed].[month name] > > > HAVING ((([x confirmed].[month name])="March 2008")); > > > > > > > > > "Leslie Isaacs" wrote: > > > > > > > Hello All > > > > > > > > I have a table [x confirmed], and 2 of the fields are 'practice' and > > 'month > > > > name'. This table contains typically between 10 and 50 records for each > > > > combination of 'practice' and 'month name' values - so for e.g. there > > may be > > > > 22 records where 'practice' = "The Health Centre" and 'month name' = > > "May > > > > 2008". I need a query that will return the total number of UNIQUE values > > of > > > > 'practice' for a given value of 'month name' - i.e. I need to know how > > many > > > > different 'practice' values there are where 'month name' = "March 2008". > > > > > > > > I have tried this: > > > > > > > > SELECT DISTINCT [x confirmed].practice > > > > FROM [x confirmed] > > > > WHERE ((([x confirmed].[month name])="March 2008")) > > > > GROUP BY [x confirmed].practice; > > > > > > > > .... but this returns the total number of records where 'month name' = > > "March > > > > 2008". Adding the word "DISTINCT" seems to have made no difference to > > the > > > > result returned by the query. > > > > > > > > What have I done wrong? > > > > > > > > Hope somone can help. > > > > Many thanks > > > > Les > > > > > > > > > > > > > > > > > > |