Solved

Select count distinct equivalent

Posted on 2002-06-18
6
236 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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