Counting in a query

[cgemmill1]

I want to create a query that will count the number of data submittions over a set time frame. I have a table with organization name, measure id, start date and the data.

I created a query to count start date for the date range 5/1/12-8/30/12. I only get a single count for each. Does this have to be done with multiple queries?

 

[pbaldy]

What is the SQL of your query? Likely you left the date field in the GROUP BY and SELECT clauses (s/b WHERE in design view).

 

[cgemmill1]

Here is my SQL

SELECT tbl_CDS.[Organization Name], tbl_CDS.HRET_MeasureID, tbl_CDS.Project, tbl_CDS.[Start Date], Count(tbl_CDS.[Start Date]) AS [CountOfStart Date]
FROM tbl_CDS
GROUP BY tbl_CDS.[Organization Name], tbl_CDS.HRET_MeasureID, tbl_CDS.Project, tbl_CDS.[Start Date]
HAVING (((tbl_CDS.[Start Date]) Between [start] And [end]));

 

[pbaldy]

So did you try what I mentioned?

SELECT tbl_CDS.[Organization Name], tbl_CDS.HRET_MeasureID, tbl_CDS.Project, Count(tbl_CDS.[Start Date]) AS [CountOfStart Date]
FROM tbl_CDS
WHERE tbl_CDS.[Start Date] Between [start] And [end]
GROUP BY tbl_CDS.[Organization Name], tbl_CDS.HRET_MeasureID, tbl_CDS.Project;

 

[cgemmill1]

Paul, thank you very much. After comparing the SQL, I see where I made my mistakes. This will be very helpfuil in the future.

 

[pbaldy]

Happy to help.

 

[cgemmill1]

Paul, is there an easy way to replace the counts with the dates and all be in one row?

 

[pbaldy]

If I understand correctly, this type of function:

http://www.mvps.org/access/modules/mdl0004.htm