Access SQL Count for each day per month

[westerncj]

I'm working on an access query.

I'm pulling by month but I would like to get the count of species per day. When I do a count it totals up the whole month for each species. How can I do a count on that species for that day? If I have two species entered on that day I want the count to say 2 beside that date.... but remember this report shows the whole month. could I do distint in a subquery or something???

Here is my query..

SELECT Format(todaydate, "mm/dd/yy") AS TodayDate, 
       Format(todaydate, "dd")       AS [Day], 
       species, 
       Count(species) 
FROM   tbl_colorchange 
WHERE  Month(todaydate) = Month(Date()) 
GROUP  BY todaydate, 
          species

 

[Golom]

Try

SELECT Format(todaydate, "mm/dd/yy") AS TodayDate, 
       Format(todaydate, "dd")       AS [Day], 
       species, 
       Count(species) As MyCount
FROM   tbl_colorchange 
WHERE  Month(todaydate) = Month(Date()) 
GROUP  BY Format(todaydate, "mm/dd/yy"),
          Format(todaydate, "dd"), species