COUNT DISTINCT

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know if it is possible to do a COUNT DISTINCT within a MS Query.
I can do this in a passthrough query but when I am in the MS Query design
interface I don't know if this can be done.

I have tried putting the following in the field section

Reps: Distinct([assoc])

Under the total field I have it set to Count

Of course this doesn't work.
 
Try this in the design view:

Create new query in design view. Add your table to the grid.

Drag the assoc field onto the field grid. Repeat so that you have this field
there twice.

Click the "totals" icon on the toolbar (the Greek letter sigma).

Under the first assoc field, select "Group By" in the "Totals" box.

Under the second assoc field, select "Count" in the "Totals" box.
 
James said:
Does anyone know if it is possible to do a COUNT DISTINCT within a MS Query.
I can do this in a passthrough query but when I am in the MS Query design
interface I don't know if this can be done.

I have tried putting the following in the field section

Reps: Distinct([assoc])

Under the total field I have it set to Count

Of course this doesn't work.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access/JET doesn't support the Count(Distinct <column>) function. The
usual way to get a Count Distinct is:

SELECT Browsers.Name as Name, Count(S.IP) AS Total
FROM
[ SELECT BrowserID, IP FROM Stats GROUP BY BrowserID, IP ]. As S
INNER JOIN Browsers ON S.BrowserID = Browsers.BrowserID
GROUP BY Browsers.Name
ORDER BY 2

The derived table (the SELECT command in brackets) gets the unique
BrowserID's IPs. The main query then counts those distinct IPs.

There are other examples in the comp.databases.ms-access archives. Go
to Google > Groups > Advanced Groups Search and search for Count
Distinct.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwTczoechKqOuFEgEQJTDACghrBzKgMtRtG+JoT7xeOlN4enk0cAoI1s
5aFLqLX+/HWv3mrMlLRo4AXL
=LOZi
-----END PGP SIGNATURE-----
 
Hi,


Alternatively, you can use:

================
SELECT Browser, COUNT(*)
FROM (SELECT DISTINCT Browser, IP FROM myTable)
GROUP BY Browser
================

to get a count of different IP (not counting an IP more than once) for each
Browser



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top