How to Count Unique Items in Field in 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.

How to count unique items in field in Access query and group them by [feildname]?

If you bust it into two groupings you can get the output you want

SELECT t1.Code, Count(t1.Code) AS NumStatus
FROM 
    (SELECT [Sheet1$].Code, [Sheet1$].Status
    FROM [Sheet1$]
    GROUP BY [Sheet1$].Code, [Sheet1$].Status)  AS t1
GROUP BY t1.Code;

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 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.

Count unique values in Access

This is more SQL orientated in the explanation but the underlying premise is the same: In short you need to create a table in your From Clause that you use in your select. You can then use that table to count and sum your totals.

Select Count(ClientAmounts) as ClientTotal, Sum(ClientAmounts) as AmountTotal
From
(
    Select .Client, 
    Sum(.Amount) as ClientAmounts
    From 
    group by .Client
)

How to count unique events in Access?

MS Access does not support count(distinct). One way to work around this limitation is to select distinct in a subquery, then aggregate:

SELECT LOGIN_ID, Count(DETAIL_LOGIN_ID)
FROM (
    SELECT DISTINCT CUSTOMERS.E_CUST_LOGIN_ID AS LOGIN_ID, E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID AS DETAIL_LOGIN_ID
    FROM ((((CUSTOMERS 
    INNER JOIN E_CUSTOMER_DETAIL ON CUSTOMERS.WH_CUST_NO = E_CUSTOMER_DETAIL.WH_CUST_NO) 
    INNER JOIN E_CUST_E_CUST_REL ON CUSTOMERS.E_CUST_LOGIN_ID = E_CUST_E_CUST_REL.E_CUST_LOGIN_ID) 
    INNER JOIN E_CUST_CHNL_USAGE_DETAIL ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUST_CHNL_USAGE_DETAIL.E_CUST_LOGIN_ID) 
    INNER JOIN E_CUSTOMER_DETAIL AS E_CUSTOMER_DETAIL_1 ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)

)
GROUP BY LOGIN_ID;

Distinct Count Multiple Columns MS Access

A simple aggregate query will get the count of Number by name and ID.

SELECT [Unique Number], [Name], ID, Count(*) AS CountNum FROM table GROUP BY [Unique Number], [Name], ID;'

Counting how many unique ID per name requires two queries. First have to do a query that returns DISTINCT IDs for each Name.

SELECT DISTINCT [Unique Number], [Name], ID FROM tablename;

Then use that query to calculate aggregate count.

SELECT [Unique Number], [Name], Count(ID) AS CountID FROM Query1 GROUP BY [Unique Number], [Name];

Can nest the 2 queries.

SELECT [Unique Number], [Name], Count(ID) AS CountID FROM (SELECT DISTINCT [Unique Number], [Name], ID FROM tablename) AS Query1 GROUP BY [Unique Number], [Name];

---

Related Topics