6

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

Improve this question
1
  • please don't try to use count(distinct col), because MS Access doesn't support count(distinct). thanks for your co-operation. Commented Sep 11, 2009 at 18:23

7 Answers 7

Reset to default
4
+50

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

sorry ignore me, I missed the ACCESS tag
Sorry brushing up on my access skills.... look here blogs.msdn.com/access/archive/2007/09/19/… This shows that you can only do it count(distinct) by using two sub queries... Hope this helps.
Sadat, see my edited version. This should get you where you need to be.
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.

Improve this answer

Comments

2

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;
Improve this answer

1 Comment

I was going to suggest making a view on the Training table complete with your count(*) as needed. Then join on the VIEW version instead of the table directly. This suggestion will do the same thing.
2

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

Improve this answer

Comments

0

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

Improve this answer

1 Comment

Broken Link - 404 Error
0

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
Improve this answer

Comments

-1

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;
Improve this answer

5 Comments

i think you haven't tried it or didn't care about access database. in ms access count(distinct col) is not allowed, it throws syntax error as 'missing operator'
@Sadat, you are right, I'd forgotten how different Access SQL is... Then you will need a subquery instead... I've edited to include that
still this is not working. first of all you must explicitly use 'as' for alias. i have placed that, but still error is there.
@Sadat, I edited to add 'As's, and added an aggregate function around the subquery, But what error r u getting?
AS is not required for table aliases in Jet/ACE SQL, so I don't think that could be the cause of the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.