I have 1 table (spneeds), that has 3 fields: ClientName, VolunteerName and Disability. I need a count of distinct volunteers that serve several disabilities. After many hours of trying I found out that distinct count doesn't work. Count works by itself and distinct works by itself but they dont work together. So I have 2 queries, the first one takes the distinct volunteers, and the other counts how many.

SELECT DISTINCT [spneeds].[VolunteerName]
FROM spneeds
WHERE ([spneeds].[Disability]='Blind') Or ([spneeds].[Disability]='Deaf') Or ([spneeds].[Disability]='Autistic');

and the second one calls the first:

SELECT Count([VolunteerName]) AS vemotional
FROM [query distinct count];

So I get the correct number.

Question:
How do I get it so that I can put it in another table with all the counts... or put it in a variable, so I can insert it into another table. I tried DoCmd.RunSQL (SQL) but it doesn't return a value, it's only for inserting/updating..

Thanks in advance.

If you want the result in a variable, open a recordset on the totals query. If you want the result inserted into a table, use the SELECT from the totals query in an INSERT query:

INSERT INTO MyTable (CountEmotional)
SELECT Count([VolunteerName]) AS vemotional
FROM [query distinct count];



John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

Thanks :D ALOT!
Ugh, now I can go to sleep!