Adding in Date Range selector to query

[tkbuc]

Hi everyone,



I have the query below and I am trying to add in the date field from my table which is called "Created". I want the query to run exactly as below, I just want to only have it pull records within a certain date range.

I am dragging the field into the query and using the BETWEEN [Start Date] and [End Date] criteria for the field. In the total field, it's set as "Group By". I don't want the dates to show in the query, so I unchecked "Show" box. When I go to run the query, it expands the "Request Type" field instead of having each request type grouped. What am I doing wrong? Any help would be greatly appreciated!


SELECT [Reporting and Metrics Tracker].[Request Type], Count([Reporting and Metrics Tracker].ID) AS [Total Requests], [Snapshot Complete].CountOfStatus AS [Total Completed], Sum([Reporting and Metrics Tracker].[Effort per occurence]) AS [Time (hrs)]
FROM [Reporting and Metrics Tracker] LEFT JOIN [Snapshot Complete] ON [Reporting and Metrics Tracker].[Request Type] = [Snapshot Complete].[Request Type]
GROUP BY [Reporting and Metrics Tracker].[Request Type], [Snapshot Complete].CountOfStatus;

[JoeM]

Something like:

SELECT [Reporting and Metrics Tracker].[Request Type], Count([Reporting and Metrics Tracker].ID) AS [Total Requests], [Snapshot Complete].CountOfStatus AS [Total Completed], Sum([Reporting and Metrics Tracker].[Effort per occurence]) AS [Time (hrs)]
FROM [Reporting and Metrics Tracker] LEFT JOIN [Snapshot Complete] ON [Reporting and Metrics Tracker].[Request Type] = [Snapshot Complete].[Request Type]
WHERE [Table Reference].[Created] Between [Start Date] and [End Date]
GROUP BY [Reporting and Metrics Tracker].[Request Type], [Snapshot Complete].CountOfStatus;

(obviously replacing "Table Reference" with the name of the table that the Created field resides in)