I have a query that goes out and selects all records entered within the last 15 days. In effect, you could get 15 records for an individual route#, one for each day with it's number of complaints for that date.I then created a Report based on the results of the query and group it by route#. I then count the number of times that route# is listed within the group. I get results ranging from route#s showing up 7 or 8 times to route#s only showing up once. My query results may bring in 50 or more records. Plus the only way I can seem to sort it that makes sense is based on Route# and then I may have a route# that shows a total in the grouping of say 5, followed by a bunch of totals of 1 then followed by a route# that is grouped together 8 times.
What I would like to do is create a report that only shows me those individuals listed 4 or more times in the last 15 days sorted by the highest number of occurences down to those with 4 occurences.
Here is the SQL query:
SELECT Multiples.IssueDate, Multiples.RouteNumber, Multiples.StartDate, Multiples.Complaints, Multiples.Reason, Multiples.ActionTaken
FROM Multiples
WHERE (((Multiples.IssueDate)>Date()-15));
Is there any way I can select or report on only those RouteNumbers that are listed 4 or more times and then sort highest to lowest based on number of occurences?
Thanks!