?
Solved

Select count distinct equivalent

Posted on 2002-06-18
6
Medium Priority
?
246 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 200 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Industry Leaders: 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

807 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