Fetching correct record count

[oaklandar]

I have an Access 2000 database that I am using with MX.


My table looks like this:
ID Name StartDate
1 Fred 5/17/2004 3:00PM
2 Bob 5/17/2004 5:00PM
3 Steve 5/17/2004 6:00PM
4 Rich 5/18/2004 1:00PM


I am trying to get a total count of how many names I have per date only.

So for 5/17/2004 my count should show a total of 3 and for 5/18/2004 my count should show a total of 1.

My query attempt:

<cfquery name="qry" datasource="mydatanamehere">
select distinct Format(StartDate, 'mm/dd/yyyy') as myStarter from tableOne
</cfquery>



My results (using [b]#qyr.recordcount#[/b] are not working correctly so I need help on getting this to work.  Please advise.

 

[ecobb]

Try this:

<cfquery name="qry" datasource="mydatanamehere">
select distinct Format(StartDate, 'mm/dd/yyyy') as myStarter, Count(*) AS Records 
from tableOne
</cfquery>

Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[oaklandar]

Thanks I tried and it doesnt seem to be pulling up the correct totals. Please advise.

 

[NorthStarDA]

how about just grouping in the query?

SELECT COUNT(startdate)  AS dates, StartDate
FROM  `tableOne` 
GROUP  BY StartDate

will that work for you?

 

[oaklandar]

Thanks.