Counting distinct types

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hello and thanks for the help.
I have a table Computers with these (and more) fields
[SN],[Make]
I want to count the distinct number of Makes. I can do
it by creating a query and grouping by Make then creating
another query which references the first query and doing
a count.

Surely there is a more intelligent way of doing this by
using SQL but the help file isn't much help.

Thanks again.
 
I want to count the distinct number of Makes. ...

Surely there is a more intelligent way of doing this by
using SQL but the help file isn't much help.
Click to expand...

In most implementations of SQL you can use the syntax SELECT (COUNT
DISTINCT...)

However, the JET query engine used in Access does not recognize this
syntax.

The only suggestion I'd make is that you don't need an aggregate
function for the first query, just a DISTINCT:

Q1 - SELECT DISTINCT Make FROM cars;
Q2 - SELECT Count(*) FROM Q1;

or do it as a subquery:

SELECT Count(*) FROM (SELECT DISTINCT Makes FROM cars);
 
Back
Top