If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using SELECT DISTINCT
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 |
#2
|
|||
|
|||
Using SELECT DISTINCT
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 |
#3
|
|||
|
|||
Using SELECT DISTINCT
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" wrote in message ... 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 |
#4
|
|||
|
|||
Using SELECT DISTINCT
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" wrote in message ... 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 |
#5
|
|||
|
|||
Using SELECT DISTINCT
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" wrote in message ... 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" wrote in message ... 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 |
#6
|
|||
|
|||
Using SELECT DISTINCT
Leslie Isaacs wrote:
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? Nope. Not at all. DISTINCT is used to tell the query engine to return all the records that are distinct from all the other records being returned by the query. What are the criteria for distinctness? The combination of all the fields in each row must be different from every other row. These are distinct records: A B C A C B B C A B D E These are not distinct: A B C A B C B C A B D E C D E C D E C S T When you create a grouping query, distinct results are automatically obtained, because the results are grouped by one or more fields, guaranteeing the every record is different from every other record. For example, if you group by the first column in the non-distinct example above. You will be telling the query engine to return one row where the first column contains A and another row where the first column contains B. For example SELECT firstcol, count(*) from nondistinctdata group by firstcol returns A 2 B 2 C 3 These results are distinct already: each record contains at least one field whose value is different from the first field in all the other records in the result. SQL Server allows you to do this: SELECT COUNT(DISTINCT firstcol) from nondistinctdata which returns 3 Unfortunately, this functionality was never added to Jet so the solution provided by Dennis is one of the only ways to get this answer. Here's a minor variation: SELECT Count(*) FROM (SELECT DISTINCT firstcol from nondistinctdata) as q -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#7
|
|||
|
|||
Using SELECT DISTINCT
You want a DISTINCT Count, which Access does not support.
You will need to use a subquery to get the desired results, however since your table and field names don't follow good naming practices you can't use a subquery. Good Naming practices for tables and fields means that names should consist of only Letters, Numbers, and the underscore character. In addition, names should not be reserved words such as Date, Time, Left, etc. So the only way to do what you want is to run two queries. Query One saved as qUniquePracMonth, returns the one record for each combination of practice and Month Name. SELECT DISTINCT [x confirmed].practice, [Month Name] FROM [x confirmed] Query Two returns the unique count for a specified month SELECT Count(Practice) as TheCount, [Month Name] FROM qUniquePracMonth WHERE [Month Name] = "March 2008" GROUP BY [Month Name] If you want a count of a specific practice over all the month names in the database, change the WHERE clause to WHERE Practice = "The Health Centre" John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County 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 |
#8
|
|||
|
|||
Using SELECT DISTINCT
Bob, Dennis and John
Many thanks for all your help: I now understand why I need two queries! I also understand that some of my field names should be amended - but that's for another day! Thanks again. Les "John Spencer" wrote in message ... You want a DISTINCT Count, which Access does not support. You will need to use a subquery to get the desired results, however since your table and field names don't follow good naming practices you can't use a subquery. Good Naming practices for tables and fields means that names should consist of only Letters, Numbers, and the underscore character. In addition, names should not be reserved words such as Date, Time, Left, etc. So the only way to do what you want is to run two queries. Query One saved as qUniquePracMonth, returns the one record for each combination of practice and Month Name. SELECT DISTINCT [x confirmed].practice, [Month Name] FROM [x confirmed] Query Two returns the unique count for a specified month SELECT Count(Practice) as TheCount, [Month Name] FROM qUniquePracMonth WHERE [Month Name] = "March 2008" GROUP BY [Month Name] If you want a count of a specific practice over all the month names in the database, change the WHERE clause to WHERE Practice = "The Health Centre" John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County 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 |
#9
|
|||
|
|||
Using SELECT DISTINCT
Why not select practice, [month name], count([month name]) from [x confirmed] group by practice, [month name] ???? |
#10
|
|||
|
|||
Using SELECT DISTINCT
I think the poster has data like
xxxx 2007-08 xxxx 2007-08 xxxx 2007-08 xxxx 2007-08 xxxx 2007-09 yyyy 2007-08 yyyy 2007-09 And wants to get a count of 2 for 2007-08 and not a count of 5 John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County scubadiver wrote: Why not select practice, [month name], count([month name]) from [x confirmed] group by practice, [month name] ???? |
Thread Tools | |
Display Modes | |
|
|