Multiple Offenders of Parking Violations

[Josh B.]

First, I'd like say I'm sorry if this question has been answered before for a
similar situation.

I"m trying to create a Parking Ticket Databaste for a Corporate Headquarters.
One of the things I would like to do is keep track of those with multiple
offenses and run a report based off a query.
I would like to know if it is possible with what I currently have, and if
so, how do I write the query?
Currently, I'm tracking information in a single table. The primary key is
the ticket number. I also track date, last name, first name, Make, Model,
LP#, Area of Violation, Type of Violation

I guessing I have to use multiple tables, but I have no idea how to run
reports or do queries off of more than one table.

any and all help would be appreciated.

 

[Keith Wilby]

First, I'd like say I'm sorry if this question has been answered before
for a
similar situation.

I"m trying to create a Parking Ticket Databaste for a Corporate
Headquarters.
One of the things I would like to do is keep track of those with multiple
offenses and run a report based off a query.
I would like to know if it is possible with what I currently have, and if
so, how do I write the query?
Currently, I'm tracking information in a single table. The primary key is
the ticket number. I also track date, last name, first name, Make, Model,
LP#, Area of Violation, Type of Violation

I guessing I have to use multiple tables, but I have no idea how to run
reports or do queries off of more than one table.

A motorist can have many tickets so there's a 1:M relationship straight
away. Consider having 2 tables, tblMotorist and tblTickets with a 1:M join
between them. To query the tables, just include both of them on the query
grid.

Regards,
Keith.
www.keithwilby.com

 

[Bob Barrows [MVP]]

First, I'd like say I'm sorry if this question has been answered
before for a similar situation.

I"m trying to create a Parking Ticket Databaste for a Corporate
Headquarters. One of the things I would like to do is keep track of
those with multiple offenses and run a report based off a query.
I would like to know if it is possible with what I currently have,
and if so, how do I write the query?
Currently, I'm tracking information in a single table. The primary
key is the ticket number. I also track date, last name, first name,
Make, Model, LP#, Area of Violation, Type of Violation

I guessing I have to use multiple tables, but I have no idea how to
run reports or do queries off of more than one table.

any and all help would be appreciated.

Depending on what you need to report, I see no need for a second table.
You can create a grouping query to get the count per motorist ... as
long as you don't have two people with the same first name, last name,
etc. Here is the sql that would be used:

select LastName,FirstName, count(*) as ticketcount
from tablename
group by LastName,FirstName

Make the corrections to the field and table names, copy/paste the
result into the SQL View of a new query and give it a try

 

[John]

First, I'd like say I'm sorry if this question has been answered before for a
similar situation.

I"m trying to create a Parking Ticket Databaste for a Corporate Headquarters.
One of the things I would like to do is keep track of those with multiple
offenses and run a report based off a query.
I would like to know if it is possible with what I currently have, and if
so, how do I write the query?
Currently, I'm tracking information in a single table. The primary key is
the ticket number. I also track date, last name, first name, Make, Model,
LP#, Area of Violation, Type of Violation

I guessing I have to use multiple tables, but I have no idea how to run
reports or do queries off of more than one table.

any and all help would be appreciated.

You could use 2 tables, or you could just write a report (a query is
not even needed) and in your sorting in the report sort on last+first
name, with a grouping on same, and then do a count in the group footer

Jones, Joe Total 1
Jones, Ralph Total 2
Smith, Ed Total 5
Smith, Sam Total 1

You could also, in the detail for each person, list the ticket(s)

 

[Josh B.]

Thank you very much. That was just what I was looking for.
If you have another moment, or anyone else knows:
How would I run a report to show only those whose ticketcount is equal to or
greater than 3?

 

[Bob Barrows [MVP]]

Add
HAVING ticketcount >= 3

Thank you very much. That was just what I was looking for.
If you have another moment, or anyone else knows:
How would I run a report to show only those whose ticketcount is
equal to or greater than 3?

 

[Josh B.]

What i have is the following.
I'm not sure where to put the HAVING clause, but no matter where I put it,
it seems to give an error. currently when I put it in the following spot, i
get a syntax error.
Any ideas.
Also, I have no knowledge of SQL, and was wondering if you know a decent
place to look information up on it so I can get a better idea of SQL commands.

SELECT Parking_Violations.LastName, Parking_Violations.FirstName, Count(*)
AS ticketcount
FROM Parking_Violations
HAVING ticketcount >= 3
GROUP BY Parking_Violations.LastName, Parking_Violations.FirstName
ORDER BY Count(*) DESC;

I appreciate the help.

 

[Josh B.]

GOT IT!!

Thanks for all of your help.

SELECT Parking_Violations.LastName, Parking_Violations.FirstName, Count(*)
AS ticketcount
FROM Parking_Violations
GROUP BY Parking_Violations.LastName, Parking_Violations.FirstName
HAVING (((Count(*))>=3))
ORDER BY Count(*) DESC;