CODEWITHSUNDEEP
sqlms-accesscountdistinct
Sadat
Sadat

Reputation: 3501

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

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

Upvotes: 6

Views: 51807

Answers (7)

SatyaJohnny
SatyaJohnny

Reputation: 1

I would propose 

select R_rep,sum(pp) as number_distinct_Billnos from (select R_rep, Billno, 1 as pp from `Vat_Sales` group by R_rep, Billno) t group by R_rep

Upvotes: 0

DBMarcos99
DBMarcos99

Reputation: 465

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

Upvotes: 2

Gordon Bell
Gordon Bell

Reputation: 13633

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;

Upvotes: 2

iDevlop
iDevlop

Reputation: 25252

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.

Upvotes: 2

Kevin LaBranche
Kevin LaBranche

Reputation: 21078

Have a look at this blog entry, it appears you can do this with subqueries....

http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx

Upvotes: 0

Rippo
Rippo

Reputation: 22424

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)

Upvotes: 4

Charles Bretana
Charles Bretana

Reputation: 146409

try this:

SELECT DISTINCT e.ETCode, t.TTitle, t.Tcomponent, 
      t.TImpliment_Partner, t.TVenue, t.TStartDate, 
      t.TEndDate, e.EDate, a.QCode, a.Answer, 
      q.SL, q.Question,
      Count(a.Answer) AnswerCount,
      Min(Select Count(*) 
       From (Select Distinct TCode From Training) As Z ) TCodeCount    
FROM Evaluation As e 
   JOIN Training AS t ON e.ETCode=t.TCode
   JOIN Answer AS a ON e.ECode=a.ECode
   JOIN Questions AS q ON a.QCode=q.QCode
GROUP BY e.ETCode, a.QCode, t.TTitle, t.Tcomponent, 
     t.TImpliment_Partner, t.Tvenue, a.Answer, q.Question, 
     t.TStartDate, t.TEndDate, Evaluation.EDate, q.SL
ORDER BY a.QCode, a.Answer;

Upvotes: -1

Related Questions