Counting unique ID's without duplicates

[JiTS]

Hi,

I have a problem with getting the right result with a query.

To this topic I attached a part of my database including 2 queries. The queries are almost the same, except the first field. In query 1 is the total 'Group By' and in query 2 'Count'.The other fields are parameters, which are the same in both queries.

My problem:
If I run query 1 then the result is 31 rows with unique ID's. When I run query 2 the result is 35, because the query counts 4 duplicates.

The correct result I want is 31. That means count the unique ID's. I am able to show the ID's with query 1, but I am not able to count them correctly. I tried to add 'DISTINCT' in SQL view but that didn't help.
Also other solutions written in this forum didn't work.

Thank you if someone (with more query/SQL experience) is able to help me...

 

[ByteMyzer]

Try this query:

SELECT Count(*) AS Result FROM
 (SELECT DISTINCT tblKC.nr
  FROM tblKC INNER JOIN tblMS ON tblKC.nr = tblMS.nc
  WHERE tblKC.tm='1A'
  AND tblMS.st='Uitgezet'
  AND tblKC.di<CDate('06-17-06')
  AND (tblKC.da Is Null Or tblKC.da>CDate('06-16-06'))
 ) AS Q1

 

[JiTS]

Wow!

ByteMyzer,

That's the one I was looking for... many thanks from Amsterdam!