Select Distinct? Counts??? Help please

[Focusfocs]

Hello,
I am trying to create a report which lists volunteers and their transactions across a speicified date range. My goal is to provide a sum of all volunteers during that timeframe. I have tried Count(ID) but it counts every transaction. I need a way to count DISTINCT folks...evidently CountDistinct() isn't supported in MS Access?? What could be a possible solution. Thank you very much for your time.

 

[DotNetGnat]

try

Count(Distinct ID)

-DNG

 

[Focusfocs]

Invalid syntax

 

[DotNetGnat]

oh...i thought i was posting in SQL Server forums...

Select ID, Count(ID) from mytable
group by ID

-DNG

 

[Focusfocs]

Nope..

 

[PHV]

A starting point:
SELECT D.Field1, D.Field2, Count(*) AS CountDistinct
FROM (SELECT DISTINCT Field1, Field2, ID FROM yourTable) AS D
GROUP BY D.Field1, D.Field2;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Focusfocs]

How would I implement that into this:?
SELECT Volunteers.FirstName, Volunteers.LastName, [LastName] & " " & [FirstName] AS FullName, Volunteers.Region, VolunteerTransactions.Event, VolunteerTransactions.Hours, VolunteerTransactions.Date, VolunteerTransactions.ID
FROM Volunteers INNER JOIN VolunteerTransactions ON Volunteers.ID = VolunteerTransactions.ID
WHERE (((Volunteers.LastName) Is Not Null))
ORDER BY Volunteers.LastName;

 

[PHV]

And what do you want to count ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Focusfocs]

I want to count individual volunteers and have it as a total at the footer of a report. When I try to count the ID it gives me every ID that appears.
Thank you again...

 

[lespaul]

so you want to list each individual (as shown in your query above) and then in the footer show a total number of each individual listed in the report?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Focusfocs]

YES! Can it be done??

 

[Focusfocs]

I FOUND IT!!! thread703-1178919
Thank you all for your time!