Solved

Select count distinct equivalent

Posted on 2002-06-18
6
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

732 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