Query to select cetain items during a specific time period

[Ceadmo]

I maintain a smalldatabase that keeps tracks of the date and plate numbers of vehicles that are illegallyparked in our parking lot. There areonly three fields in the database: date, plate # and Violation (reason for theticket).
I want to be able to run asearch that covers a specific time frame (I’m using the BETWEEN command to dothis) and find all plate numbers that appear three or more times during thisperiod along with the violation.


I have tried using thegreater than symbol but it would not work, the Plate # field is not a numericalfield; it is composed of Apha characters and numbers, therefore a text field.
I am not experience inusing code and use the wizards to set up my tasks.
I appreciate any help in preparinga query to do this.

[burrina]

FYI Date is a reserved word in Access and should NOT be used.
Use a Totals query and Count for the Total and >1 for the criteria.

HTH

[Ceadmo]

Thanks Dave, for the correction regarding the "Date" field, I will adjust it. Your suggestion gives me totals of the repeats. What I wish is to see are the records of all the instances when a Plate # appears more than three times during the requested period.

[burrina]

I would suggest you create a new query and add the old one to it and then set the criteria.

HTH

[Ceadmo]

Thanks Dave but how would I add the old query to a new one when both queries have the same fields, would take confuse the query. I may be novice at this but would a single query looking for repeats in a field be easier or is there no way for the program to do this?

[burrina]

Essentially you create a Find Duplicates Query, save it then add it to a new query and average the NumberOfDups with Criteria of >3 and Total as Avg.
Otherwise you may have to upload a DEMO copy for analysis!

HTH

[Ceadmo]

Plate Number Database.zipPlate Number Database.zip

Thanks again Dave, but I wasn't looking for totals and averages, I want the output to be a data listing of those Plate number, Date and Violation of all the vehicles that showup in the db more than 3 times during a certain time frame.
As you suggested I have uploaded a sample db. When the query is run I wish to see only two results from this data Plate Numbers FGB 683 and FHV 284 plus the date and violation for each entry.

[burrina]

It has already been downloaded. Good Luck With Your Project!

[June7]

Try:

SELECT [Plate Numbers].ID, [Plate Numbers].TBLDate, [Plate Numbers].[Plate Number], [Plate Numbers].Vioation, DCount("[Plate Number]","[Plate Numbers]","[Plate Number]='" & [Plate Number] & "'") AS CountViolations
FROM [Plate Numbers];

Now apply filter criteria under TblDate and CountViolations fields.

or
SELECT * FROM [Plate Numbers] WHERE [Plate Number] IN
(SELECT [Plate Number]
FROM [Plate Numbers]
WHERE TBLDate Between [start] And [end]
GROUP BY [Plate Number]
HAVING Count(ID)>=3);

Advise no spaces in naming convention.

Note misspelled Violation as Vioation for field name.

[Ceadmo]

Thanks for your solution to my problem, it took me a little time to understand the solution, but when I did it worked perfectly. Now to apply it to the big Database. Once again, thanks for your help.