Count unique values in a field (2000)

Topic

New Reply

So far I have a query that looks like this:

SELECT tblAppointment.Term, tblAdvisingCode.AdvisingCode, Sum([Duration])/60 AS [Number of Contact Hours], Count(tblAppointment.AID) AS [Number of Contacts], Count(tblAppointment.SID) AS CountOfSID
FROM tblAppointment INNER JOIN tblAdvisingCode ON tblAppointment.AID = tblAdvisingCode.AID
WHERE (((tblAppointment.Term)=[Which semester? eg SP03]))
GROUP BY tblAppointment.Term, tblAdvisingCode.AdvisingCode;

This gives me each advising code, the number of contact hours for each code. What I need is a number of how many unique participants had an appt with that code. For instance, I have 22 academic advising contacts, but there should only be 18 participants (these are in [SID]), some had more than one academic advising contact. How do I get it to count just the distinct values. I tried turning on unique values and that didn’t work. I’ve tried count, tried select distinct. Help! This is making me crazy!

Reply | Quote

Replies

What you have is going to select AdvisingCode by Term. What is Term?
Take out Term and see what you get.
Pat

Reply | Quote

Term is the semester in which the student had the appointment. I need to be able to select which term I want.

Reply | Quote

In that case change the GroupBy for TERM to a WHERE – then the term isn’t actually returned. Otherwise you need to consider a CROSSTAB query or Pivot table if you need to show more than one semester.

Reply | Quote