If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Offenders of Parking Violations
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. -- Thanks, Josh B. |
#2
|
|||
|
|||
Multiple Offenders of Parking Violations
"Josh B." wrote in message
... 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 |
#3
|
|||
|
|||
Multiple Offenders of Parking Violations
Josh B. wrote:
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. -- Thanks, Josh B. 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 -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
#4
|
|||
|
|||
Multiple Offenders of Parking Violations
On Wed, 27 Aug 2008 08:27:01 -0700, Josh B.
wrote: 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) |
#5
|
|||
|
|||
Multiple Offenders of Parking Violations
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? -- Thanks, Josh B. "Bob Barrows [MVP]" wrote: Josh B. wrote: 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. -- Thanks, Josh B. 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 -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
#6
|
|||
|
|||
Multiple Offenders of Parking Violations
Add
HAVING ticketcount = 3 Josh B. wrote: 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? -- Thanks, Josh B. "Bob Barrows [MVP]" wrote: Josh B. wrote: 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. -- Thanks, Josh B. 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 -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
#7
|
|||
|
|||
Multiple Offenders of Parking Violations
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. -- Thanks, Josh B. "Bob Barrows [MVP]" wrote: Add HAVING ticketcount = 3 Josh B. wrote: 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? -- Thanks, Josh B. "Bob Barrows [MVP]" wrote: Josh B. wrote: 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. -- Thanks, Josh B. 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 -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
#8
|
|||
|
|||
Multiple Offenders of Parking Violations
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; -- Thanks, Josh B. "Josh B." wrote: 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. -- Thanks, Josh B. "Bob Barrows [MVP]" wrote: Add HAVING ticketcount = 3 Josh B. wrote: 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? -- Thanks, Josh B. "Bob Barrows [MVP]" wrote: Josh B. wrote: 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. -- Thanks, Josh B. 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 -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
Thread Tools | |
Display Modes | |
|
|