p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/)
-   Access (http://p2p.wrox.com/access-18/)
-   -   SQL Distinct count sub query problem (http://p2p.wrox.com/access/99852-sql-distinct-count-sub-query-problem.html)

eel3720 April 23rd, 2018 02:09 PM

SQL Distinct count sub query problem
 
Hello, I have a problem where my table tblskid has the following data:

ID, Code, Name, EQUIP
1, 920, Clinton, Jacob, Motor pool
1, 920, Clinton, Jacob, Motor pool
1, 920, Clinton, Jacob, Motor pool
1, 920, Clinton, Jacob, Motor pool


I need the distinct count(EQUIP) TO be 1 see below:

SELECT DISTINCT Count(tblskid.EQUIP) AS CountOfEQUIP, tblskid.Code, tblskid.ID, tblskid.Name
FROM tblskid
GROUP BY tblskid.Code, tblskid.ID, tblskid.Name;


this query above comes back 4, please help me from sending all the data to a function using a recordset that's the only way I can get 1. Sorry for the space formatting.

Thank you for your attention to this issue.

eel3720 April 25th, 2018 11:46 AM

Create the sub query and then count rows
 
SELECT Count(*) AS N
FROM (SELECT DISTINCT EQUIP FROM tblskid) AS T;
[:)]


All times are GMT -4. The time now is 08:44 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
2013 John Wiley & Sons, Inc.