Count Unique Values in a Totals Query

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Can anyone help me with what must be reasonably straighforward but I can't
figure it out.

I have 2 fields 'Range' & 'Manufacturer' - and I want a query to give me the
number of unique 'Manufacturer' records for each 'Range'.

Any help greatly appreciated,

Regards....Jay
 
Access doesn't support Count Unique so you have to make a unique set of
records before you attempt to count uniquely.

Query One:
SELECT Distinct Range, Manufacturer
FROM SomeTable

Query Two uses the SAVED query
SELECT Range, Count(Manufacturer)
FROM SavedQueryOne
GROUP BY Range

IF your table and field names consist of only letters, numbers, and
underscore characters you can combine that into one

SELECT Range, Count(Manufacturer)
FROM
(
SELECT Distinct Range, Manufacturer
FROM SomeTable
) as UniqueData
GROUP BY Range

If you don't understand the SQL statements above post back and I will try to
outline how to set up the process in the Design view (query grid) using the
two query option.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Many thanks John,

I could do it with 2 queries but was after the SQL to do it in one query so
many thanks for that.

Regards..........Jason
 
Hi John,

WOuld you mind explaining why the subquery method only works with field
names with no spaces. Can't you simply use square brackets around the
field name if it contains a space?

Thanks....Jason
 
Because Access has a strange manner of handling subqueries. It will
make the subquery surrounded by brackets and followed by a period (and
an alias).

The catch is Access will not allow any square brackets in a subquery.
Which means there is no way to handle field names and table names that
require the brackets. It can also mean no parameters in the subquery.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thanks John, it's the little quirks like that that are really useful to
know.

Thanks again...........Jason
 
Back
Top