Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 23rd, 2018, 01:09 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2018
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.
 
Old April 25th, 2018, 10:46 AM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2018
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Create the sub query and then count rows

SELECT Count(*) AS N
FROM (SELECT DISTINCT EQUIP FROM tblskid) AS T;




Similar Threads
Thread Thread Starter Forum Replies Last Post
Count Distinct Jonas Access 2 August 14th, 2006 01:03 PM
count distinct values Chris Cash XSLT 3 June 8th, 2006 04:55 PM
sql to return distinct count pbgurl Access VBA 2 October 29th, 2004 06:29 AM
SQL query for count programmer_kay ADO.NET 1 April 18th, 2004 02:48 AM
SQL query for count programmer_kay SQL Language 1 April 17th, 2004 07:30 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.