Counting ONE field with date parameters

  • Thread starter Thread starter FJB
  • Start date Start date
F

FJB

I am trying to create a query that will count one field and will allow
us to have a date parameter, i.e. select the date or date period we
want.

The query contains three fields: occupation, Filed date, and a count
field. When it just the occupation field, the count works perfectly.
When I add the date parameter, it counts the occupations by date. In
other words, I dont' have one count for "attorney" but have multiple
counts based on the date. I have pasted the SQL view of the query
below. Any and all help will be appreciated.

SELECT [SAR Filings].Occupation, Count([SAR Filings].Occupation) AS
CountOfOccupation, [SAR Filings].[SAR Filed Date]
FROM [SAR Filings]
GROUP BY [SAR Filings].Occupation, [SAR Filings].[SAR Filed Date]
HAVING ((([SAR Filings].[SAR Filed Date]) Between [Start Date] And [End
Date]))
ORDER BY Count([SAR Filings].Occupation) DESC;
 
Change the having clause to a where clause and drop the FILED DATE field

SELECT [SAR Filings].Occupation,
Count([SAR Filings].Occupation) AS CountOfOccupation
FROM [SAR Filings]
WHERE ((([SAR Filings].[SAR Filed Date])
Between [Start Date] And [End Date]))
GROUP BY [SAR Filings].Occupation
ORDER BY Count([SAR Filings].Occupation) DESC;
 
Back
Top