How to Use Distinct in Ms Access

how to use distinct in ms access

Okay.Its working this way.

SELECT DISTINCT Task.Priority, Task.Subject, Task.Status, Task.DueDate, 
Task.Completed, Categories.Category
FROM Task, Categories
WHERE (((Categories.CategoryID)=[Task].[CategoryID]));

MS-Access SQL DISTINCT GROUP BY

This should work:

SELECT DISTINCT GroupNumber, FirstNames
FROM Example AS b

How do I count unique items in field in Access query?

Try this

SELECT Count(*) AS N
FROM
(SELECT DISTINCT Name FROM table1) AS T;

Read this for more info.

SELECT DISTINCT count() in Microsoft Access

MS Access does not support count(distinct). In your case, you can use a subquery. In addition, your query should not work. Perhaps this is what you intend:

SELECT COUNT(*)
FROM (SELECT ApplixCalls.OurRef
      FROM ApplixCalls LEFT JOIN
           Correspondence
           ON ApplixCalls.OurRef = Correspondence.OurRef
      WHERE (((orrespondence.OurRef Is Null) AND (ApplixCalls.Position) <> 'Closed')) OR
              (ApplixCalls.Position <> 'Closed') AND (Correspondence.[SBSUpdate?] = True))
            )
      GROUP BY ApplixCalls.OurRef
    ) as x;

Modifications:

• You have a HAVING clause with no GROUP BY. I think this should be a WHERE (although I am not 100% sure of the logic you intend).
• The SELECT DISTINCT is replaced by SELECT . . . GROUP BY.
• The COUNT(DISTINCT) is now COUNT(*) with a subquery.

EDIT:

Based on the description in your comments:

SELECT COUNT(*)
FROM (SELECT ApplixCalls.OurRef
      FROM ApplixCalls LEFT JOIN
           Correspondence
           ON ApplixCalls.OurRef = Correspondence.OurRef
      WHERE (((orrespondence.OurRef Is Null) AND (ApplixCalls.Position) <> 'Closed')) OR
              (ApplixCalls.Position <> 'Closed') AND (Correspondence.[SBSUpdate?] = True))
            )
      GROUP BY ApplixCalls.OurRef
      HAVING SUM(IIF(Correspondence.[SBSUpdate?] = False, 1, 0)) = 0
    ) as x;

MS Access SQL DISTINCT taking a very long time

I think you'd do well to perform your initial select, and then insert the results into a temp table. From there, processing your DISTINCT command should be super quick. Don't forget to delete / clean-up the temp table when you're done.

How do I select *unique* duplicates in MS Access SQL code from one table?

Since you're using a <> condition, you'll always get duplicate results (x-y and y-x). Instead of using <>, you could arbitrarily decide that the smaller ServiceId should always by in c1:

SELECT DISTINCT 
  c1.ServiceID AS ServiceID1, c1.ServiceDate AS ServiceDate1, 
  c2.ServiceID AS ServiceID2, c2.ServiceDate AS ServiceDate2
FROM MyTable c1 INNER JOIN MyTable c2 
ON c1.GroupID = c2.GroupID 
WHERE (c1.ServiceID < c2.ServiceID AND c1.ServiceDate = c2.ServiceDate); 
-- Here ------------^

SELECT Count Distinct Syntax MS Access SQL

MS Access does not support COUNT(DISTINCT). You can use two aggregations:

select count(*)
from (select distinct [Customer Number]
      from Test 
     ) as t;

Note: This counts NULL values whereas COUNT(DISTINCT) does not. You can filter them out in either the subquery or outer query if that is an issue.

---

Related Topics