Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    counting the records in a query (2003 SP1)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting the records in a query (2003 SP1)

    Try

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

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: counting the records in a query (2003 SP1)

    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;

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: counting the records in a query (2003 SP1)

    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •