MS ACCESS: How can i count distinct value using access query?

Question

here is the current complex query given below.

SELECT DISTINCT Evaluation.ETCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.TVenue, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Answer.QCode, Answer.Answer, Count(Answer.Answer) AS [Count], Questions.SL, Questions.Question
FROM ((Evaluation INNER JOIN Training ON Evaluation.ETCode=Training.TCode) INNER JOIN Answer ON Evaluation.ECode=Answer.ECode) INNER JOIN Questions ON Answer.QCode=Questions.QCode
GROUP BY Evaluation.ETCode, Answer.QCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.Tvenue, Answer.Answer, Questions.Question, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Questions.SL
ORDER BY Answer.QCode, Answer.Answer;

There is an another column Training.TCode. I need to count distinct Training.TCode, can anybody help me? If you need more information please let me know

Answer 1

try

select ..., count(distinct Training.Tcode) as ..., ...

EDIT - please now look at this...

Take the following SQL code. The first select is how SQL server would do this and the second query should be access compliant...

declare @t table (eCode int, tcode int)
insert into @t values(1,1)
insert into @t values(1,1)
insert into @t values(1,2)
insert into @t values(1,3)
insert into @t values(2,2)
insert into @t values(2,3)
insert into @t values(3,1)    

select 
    ecode, count(distinct tCode) countof
from
    @t
group by
    ecode

select ecode, count(*)
from
    (select distinct tcode, ecode
    from  @t group by tcode, ecode) t
group by ecode

It returns the following:

ecode tcode
1       3 (there are 3 distinct tcode for ecode of 1)
2       2 (there are 2 distinct tcode for ecode of 2)
3       1 (there is 1 distinct tcode for ecode of 3)

Answer 2

I posted a similar question about a year ago in Google groups. I received an excellent answer:

---

A crosstab can do (from an original proposition from Steve Dassin) as long as you count either the fund, either the subfund:

  TRANSFORM COUNT(*) AS theCell
  SELECT ValDate,
      COUNT(*) AS StandardCount,
      COUNT(theCell) AS DistinctCount
  FROM tableName
  GROUP BY ValDate
  PIVOT fund IN(Null)

which, for each day (group), will return the number of records and the number of different (distinct) funds.

Change

PIVOT fund IN(Null)

to

PIVOT subfund IN(Null)

to get the same, for sub-funds.

Hoping it may help, Vanderghast, Access MVP

---

I don't know if that will work, but here's a link to that post.

Answer 3

Sadat, use a subquery like this:

SELECT DISTINCT Evaluation.ETCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.TVenue, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Answer.QCode, Answer.Answer, Count(Answer.Answer) AS [Count], Questions.SL, Questions.Question,
(SELECT COUNT(*) FROM Training t2 WHERE t2.TCode = Evalution.ETCode) as TCodeCount
FROM ((Evaluation INNER JOIN Training ON Evaluation.ETCode=Training.TCode) INNER JOIN Answer ON Evaluation.ECode=Answer.ECode) INNER JOIN Questions ON Answer.QCode=Questions.QCode
GROUP BY Evaluation.ETCode, Answer.QCode, Training.TTitle, Training.Tcomponent, Training.TImpliment_Partner, Training.Tvenue, Answer.Answer, Questions.Question, Training.TStartDate, Training.TEndDate, Evaluation.EDate, Questions.SL
ORDER BY Answer.QCode, Answer.Answer;

Answer 4

I managed to do a count distinct value in Access by doing the following:

select Job,sum(pp) as number_distinct_fruits

from

(select Job, Fruit, 1 as pp

from Jobtable group by Job, Fruit) t

group by Job

You have to be careful as if there is a blank/null field (in my code fruit field) the group by will count that as a record. A Where clause in the inner select will ignore those though. I've put this on my blog, but am concerned that I've discovered the answer too easily - others here seem to think that you need two sub queries to make this work. Is my solution viable? Distinct groupings in Access