counting the records in a query (2003 SP1)

[Thom_D]

I am sure this is in the lounge some place but I am not wording my search query properly.

This should be simple. I need to run a query based on a date range, that I know how to do

SELECT tblArcLightATSdata.SubmissionDate
FROM tblArcLightATSdata
WHERE (((tblArcLightATSdata.SubmissionDate) Between [Start Date] And [End Date]));

What I need as an out put of the query is simply the total number of records that the query pulled from the data table.

i.e. Start Date = 09-01-2005 End Date = 09-30-2005 Result = 28 because in the month of September we had 28 resumes submitted.

[HansV]

Try

SELECT Count(*) AS NumberOfRecords
FROM tblArcLightATSdata
WHERE (((tblArcLightATSdata.SubmissionDate) Between [Start Date] And [End Date]));

[Thom_D]

Hans, Thanks that worked fine.

How do I get this one to group the media sources (MediaSourceReferal) and give me a count for each category?

SELECT tblArcLightATSdata.MediaSourceReferral, tblArcLightATSdata.SubmissionDate
FROM tblArcLightATSdata
WHERE (((tblArcLightATSdata.SubmissionDate) Between [Start Date] And [End Date]))
ORDER BY tblArcLightATSdata.MediaSourceReferral;

[HansV]

The easiest way to do this is in design view. Select View | Totals to make the Total row in the query design view visible.
Leave the Total option for MediaSourceReferral to the default Group By, and set that for SubmissionDate to Count.
You can omit the sort order for MediaSourceReferral, for a Totals query is automatically sorted on the Group By field(s).
The SQL becomes:

SELECT tblArcLightATSdata.MediaSourceReferral, Count(tblArcLightATSdata.SubmissionDate)
FROM tblArcLightATSdata
WHERE (((tblArcLightATSdata.SubmissionDate) Between [Start Date] And [End Date]))
GROUP BY tblArcLightATSdata.MediaSourceReferral;