Solved

Select count distinct equivalent

Posted on 2002-06-18
6
235 Views
Last Modified: 2008-02-01
In MS SQL I know I can do something like this:

select count( distinct counties ) from mytable

Access doesn't provide this...  Is there a work around?
0
Comment
Question by:veronicas
6 Comments
 
LVL 5

Accepted Solution

by:
bob_online earned 50 total points
ID: 7090924
You can create a query to

select distinct county from mytable

Save the query as qryDistinctCounties (or whatever your naming convention is.

In a second query Select Count(*) from qryDistinctCounties
0
 
LVL 15

Expert Comment

by:dbase118
ID: 7091015
Are you trying to get a count of each counties records or just a count of the number of different counties
0
 
LVL 5

Expert Comment

by:funke
ID: 7091040
or if you want to do it SQL style then

SELECT Count([SOMEALIAS])
FROM
(SELECT WebCategory.ParentID AS [SOMEALIAS]
FROM WebCategory
GROUP BY WebCategory.ParentID)
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Expert Comment

by:funke
ID: 7091050
sorry - used my table to build example -
Here's one with your naming conventions:


SELECT Count([NUMBEROFCOUNTIES])
FROM
(SELECT Counties AS [NUMBEROFCOUNTIES]
FROM mytable
GROUP BY Counites)

0
 
LVL 54

Expert Comment

by:nico5038
ID: 7242241

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: bob_online  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7269215
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now