Ms Access: How to Count Distinct Value Using Access Query

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.

COUNT DISTINCT MS ACCESS

Generally handled by using a inline view in MSAccess. The inline view gets you the distinct order_status by client and then you can count.

SELECT clientID
FROM (SELECT ClientID, order_status 
      FROM orders 
      WHERE order_status IN("pending", "shipped")  
      GROUP BY clientID, order_status) B
GROUP BY ClientID
HAVING count(*) > 1

Or (because I don't like group by w/o aggregation)

SELECT clientID
FROM (SELECT Distinct ClientID, order_status 
      FROM orders 
      WHERE order_status IN("pending", "shipped")) B
GROUP BY ClientID
HAVING count(*) > 1

Count unique values in Access query

I was going to say, "count distinct," but apparently that's not a thing in Access.

Instead, you'll need to run two queries that are the equivalent of

select StaffID, FirstName, LastName, count(*)
from (
  select distinct StaffID, FirstName, LastName, Student
  from TblStaff stf join TblCaseNote nte on nte.Staff = stf.StaffID
) foo
group by StaffID, FirstName, LastName

The first query should have StaffID, FirstName, LastName, and Student, each set to Group By.

Then create another query that has the first as its source and Group By all columns except Student, which you should count.

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

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)

Access get a list and total count of distinct items

Standard SQL; would be the same in SQL-Server, Oracle, MsAccess (and almost all other SQL dialects); just use:

select State, count(*)
from YourTbl
group by State

MS Access Count unique values of one table appearing in second table which is related to a third table

What you want requires two queries.

Query1:

SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs;

Query2:

SELECT Count(*) AS PatientsPerDept, DepartmentID FROM Query1 GROUP BY DepartmentID;

Nested all in one:

SELECT Count(*) AS PatientsPerDept, DepartmentID FROM (SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs) AS Query1 GROUP BY DepartmentID;

You can include the Departments table in query 2 (or the nested version) to pull in descriptive fields but will have to include those additional fields in the GROUP BY.

Count Distinct in a Group By aggregate function in Access 2007 SQL

I'm not expert in MS Access and it is quite a long time last time I have written anything for it, but this maybe will work:

SELECT cd.DiagCode, Count(cd.CustomerID)
FROM (select distinct DiagCode, CustomerID from CustomerTable) as cd 
Group By cd.DiagCode;

MS Access Count Distinct Multiple Columns

This is much more complicated than in other databases, but you can before joining:

SELECT a.Customer_ID, a.First_Name, p.num_pm, s.num_sn, s.sum_sa
FROM (Table1 as a INNER JOIN
      (SELECT CustomerId, COUNT(*) as num_pm
       FROM (SELECT DISTINCT CustomerId, Payment_Method FROM Table2) as b
       GROUP BY CustomerId
      ) as p
      ON a.CustomerId = p.CustomerId
     ) INNER JOIN
     (SELECT CustomerId, COUNT(*) as num_sn, SUM(sa) as sum_sa
      FROM (SELECT CustomerId, Store_Number, SUM(Sales_Amount) as sum_sa
            FROM Table2
            GROUP BY CustomerId, Store_Number
           ) as b
      GROUP BY CustomerId
     ) as s
     ON s.CustomerId = a.CustomerId
WHERE a.Date_ID > 1234

---

Related Topics