how to set up a query to give Multiple entries of the same data by month

[Ceadmo]

I run a DB of cars that illegally park in our parking lot, I needed a query to give the license numbers of cars that park illegally more than once. I set up the query using the following script, although it does give me the license numbers correctly it does do not separate the data by date (month). Is the line (highlighted) requesting this wrong? Any help would be appreciated.

SELECT *
FROM [Violation Log]
WHERE ([Violation Log].[License Plate No]) In (SELECT [License Plate No]
FROM [Violation Log]
WHERE TblDate Between [start] And [end]


GROUP BY [License Plate No]
HAVING Count(ID)>= 2)
ORDER BY [Violation Log].[License Plate No];

[June7]

The query is correctly doing what it is designed to do.

For what period do you want to count 'more than once' - weekly, monthly, yearly? If you want a breakdown by month then need to do the count by month.

SELECT [License Plate No], YrMo(Format([TblDate], "yyyymm") AS YrMo FROM [Violation Log] WHERE TblDate BETWEEN [start] AND [end] GROUP [License Plate No], YrMo(Format([TblDate], "yyyymm") HAVING Count(ID)>=2 ORDER BY [License Plate No];

[Ceadmo]

Thanks, I will give it a try and get back to you.

[Ceadmo]

When I use the script you kindly sent me, I got an syntax error, when I click OK, it highlighted "AS". Every time I removed what it highlighted
after clicking OK and re-running the script, it would highlight the next section. I looked at the script and did not see any syntax errors.

[June7]

Sorry, should have been:

SELECT [License Plate No], Format([TblDate], "yyyymm") AS YrMo FROM [Violation Log] WHERE TblDate BETWEEN [start] AND [end] GROUP BY [License Plate No], Format([TblDate], "yyyymm") HAVING Count(ID)>=2 ORDER BY [License Plate No];

[Ceadmo]

Thanks for the correction, although the script now works, it did not give me what I wanted. I wish the query to give me all the license plate numbers that appear in the database more then once during a certain entered time frame (normally by a month or a week,).

[June7]

I don't understand. The query does do a breakdown by month. If you don't want a breakdown then the first query you tried should be correct.

[Ceadmo]

When I ran your script the first thing it ask for was the License Plate No, I wanted the query to tell me the license Plate No. the YrMo Column gave the year and month (201406) that I entered when requested to do by the script. As mentioned in my first post the original script did the license plate listing (including the date the volation was issued) that I needed but ignored the start and end date line and gave me all the entries where the license plate no showed more than once for the entire year (January to June).
All I need is all the entries that occurred in a given month where any license plate shows up more than once.

[June7]

Why would it prompt for the license plate number? Isn't [License Plate No] the name of the field?

What did you input for the [start] and [end] criteria for the original query: 1/1/2014, 6/30/2014?

In my query you would enter: 201401, 201406. There should be only one record for each license for each month.

[Ceadmo]

I resolve the problem of the Plate Number prompt, I misspelled "licence". I have attached the sample database I am playing with. It only has a few entries but they cover more then one month and one plate number is repeated 4 times, 3 times in June and once in Aug. There are two queries, the original one that does everything I want but give the data for only June. It gives me all 4 entries for the plate number. The second query (2) uses the script you gave me. When I run it all I get is the plate number and the date 201406. No mention of how many times the plate number appeared and the date of each appearance.
I do appreciate your help and I hope this message clear the air as to what I wish the query to do.

[June7]

Your original query gives all 4 records because of the outer SELECT.

Sorry, I forgot to include the Count calc in the SELECT part. All you had to do was check the Show box in the query designer so the calc field would display.

SELECT [License Plate No], Format([TblDate], "yyyymm") AS YrMo, Count(ID) AS CountViolations FROM [Violation Log] WHERE TblDate BETWEEN [start] AND [end] GROUP BY [License Plate No], Format([TblDate], "yyyymm") HAVING Count(ID)>=2 ORDER BY [License Plate No];

If you want the date of each appearance then your original query is appropriate.

If you want to show the date of each appearance and the count value for each month, will need two queries and join them.

Query1
SELECT [Violation Log].*, Format([TblDate],"yyyymm") AS YrMo FROM [Violation Log];

Query2 - the one I already suggested

Query3
SELECT Query1.*, [Multipable Violations Query 1].CountOfID
FROM Query1 INNER JOIN [Multipable Violations Query 1] ON (Query1.[Licence Plate No] = [Multipable Violations Query 1].[Licence Plate No]) AND (Query1.YrMo = [Multipable Violations Query 1].YrMo);


Note: Multipable should probably be spelled as Multiple.

How can the same license plate have 3 different vehicle make/model? I know this is just testing data but should be realistic.

[Ceadmo]

I apologize, I know you are doing your best to help me and I do appreciate it, but we seem to be going around in circles.
First I do not need a "count".
You said "If you want the date of each appearance then your original query is appropriate."
If you run the original query you will get 4 entries, 3 for Jun and one for Aug for Plate No EPM 010.
I want the original query to give me only the 3 entries for the month of Jun for Plate No EPM 010.
How do we fix the original entry to show the 3 entries for Jun only. Keeping in mind that the month could be any month(s).

[June7]

Repeat the filter criteria in the outer query.

SELECT [Violation Log].TblDate, *
FROM [Violation Log]
WHERE ((([Violation Log].[Licence Plate No]) In (SELECT [Licence Plate No]
FROM [Violation Log]
WHERE TblDate Between [start] And [end]
GROUP BY [Licence Plate No]
HAVING Count(ID)>= 2)) AND (([Violation Log].TblDate) Between [start] And [end]))
ORDER BY [Violation Log].[Licence Plate No];

[Ceadmo]

It worked perfectly. Thanks again for all your help and time.