count distinct and sum by location 2

[topwaya]

I have the following query:

SELECT Month([WOD]) AS [Month], qryFCVisitsPID.WOLoc AS WorkOutLocation, Count(*) AS [Distinct Visits]
FROM qryFCVisitsPID
WHERE (qryFCVisitsPID.WOD) Between Forms![fdlgOps]![txtStart] AND Forms![fdlgOps]![txtEnd]
GROUP BY Month([WOD]), qryFCVisitsPID.WOLoc, qryFCVisitsPID.WOD;

For the Month of January It produces:

Month Work Out Location Distinct Visits
1 Loc1 1
1 Loc1 1
1 Loc2 1
1 Loc2 1
1 Loc2 1
1 Loc3 1


I would like it to produce:

Month Work Out Location Distinct Visits
1 Loc1 2
1 Loc2 3
1 Loc3 1


I can't figure it out, - any help is appreciated, thank you!

 

[DotNetGnat]

why not just say:

SELECT Month, Location, SUM(visits) from mytable
GROUP BY Month, Location

-DNG

 

[lespaul]

maybe:


SELECT Month([WOD]) AS [Month], qryFCVisitsPID.WOLoc AS WorkOutLocation, SUM(*) AS [Distinct Visits]
FROM qryFCVisitsPID
WHERE (qryFCVisitsPID.WOD) Between Forms![fdlgOps]![txtStart] AND Forms![fdlgOps]![txtEnd]
GROUP BY Month([WOD]), qryFCVisitsPID.WOLoc;

Leslie

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

 

[PHV]

Get rid of the following in the GROUP BY clause:
, qryFCVisitsPID.WOD

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

 

[topwaya]

Thank you everyone for your suggestions. I tried PHV's first as it was the easiest - and presto! worked like a charm.
Many Thanks!!!!

 

[lespaul]

that was my solution as well, I just did it for you....


Leslie

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

 

[topwaya]

Thanks lespaul - I really do appreciate all the help. I wasn't able to work on this project again until today and I didn't look closely enough at all the responses at first, I just thought it couldn't be that simple and removed that one field in the Group By. This forum is so great - I would be lost without it!!!
Thanks VERY MUCH!!!!