Using SELECT DISTINCT

[Leslie Isaacs]

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

 

[Dennis]

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]

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

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"));

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

 

[Dennis]

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;

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

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"));

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

 

[Leslie Isaacs]

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

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;

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

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"));


:

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

 

[Bob Barrows [MVP]]

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

 

[John Spencer]

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]

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

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

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

 

[scubadiver]

Why not

select practice, [month name], count([month name])
from [x confirmed]
group by practice, [month name]


????

 

[John Spencer]

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