select query count

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

Guest

Please HELP: How can I write this SQL String in MS Access Query:
select count(distinct substr(patid,1,3)) as nubpat,
count(distinct patid) as nubtrt

My Data looks like: 001B11, 001B12, 001B13, 002B11, 003B11, 003B12 ...
My Results should be: nubpat=3 nubtrt=6
 
As you've discovered, Access (Jet) does not support COUNT(DISTINCT...).

To work around this, you have to do the DISTINCT separately, and then COUNT
the results. Also, in Access, the equivalent of SUBSTR is Mid.

You might try something like this:

SELECT
A.nubpat,
B.nubtrt
FROM
(SELECT
COUNT(*) AS nubpat
FROM
(SELECT DISTINCT
[Your Table].patid
FROM
[Your Table])) AS A,
(SELECT
COUNT(*) AS nubtrt
FROM
(SELECT DISTINCT
Mid([Your Table].patid,1,3)
FROM
[Your Table])) AS B
 
Back
Top