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
|
|||
|
|||
'count' query question
Hello,
I hope someone may help shed light on setting up this query. I have two tables: Incidents, with fields IncidentID (PK), IncidentDate, other fields describing each incident IncidentsConsumers, 1-M table with fields ConsumerID (PK), IncidentID (FK) I'm trying to figure out a good way to get how many consumers have 9 or more incidents during any given period of time, per quarter. I need to have a display like so for example with how many consumers have 9+ incidents: Current Quarter Previous Quarter 2 consumers 0 consumer I can readily find which individual consumers have what counts of incidents per quarter with a crosstab query, but how do I go one step further and just get the COUNT of consumers with number of incidents 8?? I'll keep working at it, I have some ideas that I can work with and kludge up something but I was wondering if there is some query solution that I don't know about. Thank you, Tom |
#2
|
|||
|
|||
'count' query question
The query to identity of the clients with more than 8 incidents in a quarter
looks something like: SELECT DatePart("q",[Incident_Date]) AS Incident_Quarter, tbl_Incident_Clients.Client_ID, Count(tbl_Incident_Clients.Incident_ID) AS Client_Incidents FROM tbl_Incidents INNER JOIN tbl_Incident_Clients ON tbl_Incidents.Incident_ID = tbl_Incident_Clients.Incident_ID WHERE tbl_Incidents.Incident_Date Between [Start Date] and [End Date] GROUP BY DatePart("q",[Incident_Date]), tbl_Incident_Clients.Client_ID HAVING Count(tbl_Incident_Clients.Incident_ID)8 To take this to the next step, you can save this query or build a nested subquery. If you save this as Query1, then the next step would be: SELECT Incident_Quarter, Count(Client_ID) as Frequency FROM Query1 GROUP BY Quarter This will give you the number of people who had more than 8 incidents per quarter. Obviously, this will only work as long as [Start Date] and [End Date] are less than a year apart. If you want these dates to include more than a year, you will need to add a column to Query1 that gives you the incident_year. HTH Dale "tlyczko" wrote: Hello, I hope someone may help shed light on setting up this query. I have two tables: Incidents, with fields IncidentID (PK), IncidentDate, other fields describing each incident IncidentsConsumers, 1-M table with fields ConsumerID (PK), IncidentID (FK) I'm trying to figure out a good way to get how many consumers have 9 or more incidents during any given period of time, per quarter. I need to have a display like so for example with how many consumers have 9+ incidents: Current Quarter Previous Quarter 2 consumers 0 consumer I can readily find which individual consumers have what counts of incidents per quarter with a crosstab query, but how do I go one step further and just get the COUNT of consumers with number of incidents 8?? I'll keep working at it, I have some ideas that I can work with and kludge up something but I was wondering if there is some query solution that I don't know about. Thank you, Tom |
#3
|
|||
|
|||
'count' query question
Thank you, I will try this, I knew that someone had before me solved
something like this, I appreciate your taking time to send such a detailed response. Thank you, Tom Dale Fye wrote: The query to identity of the clients with more than 8 incidents in a quarter looks something like: SELECT DatePart("q",[Incident_Date]) AS Incident_Quarter, tbl_Incident_Clients.Client_ID, Count(tbl_Incident_Clients.Incident_ID) AS Client_Incidents FROM tbl_Incidents INNER JOIN tbl_Incident_Clients ON tbl_Incidents.Incident_ID = tbl_Incident_Clients.Incident_ID WHERE tbl_Incidents.Incident_Date Between [Start Date] and [End Date] GROUP BY DatePart("q",[Incident_Date]), tbl_Incident_Clients.Client_ID HAVING Count(tbl_Incident_Clients.Incident_ID)8 To take this to the next step, you can save this query or build a nested subquery. If you save this as Query1, then the next step would be: SELECT Incident_Quarter, Count(Client_ID) as Frequency FROM Query1 GROUP BY Quarter This will give you the number of people who had more than 8 incidents per quarter. Obviously, this will only work as long as [Start Date] and [End Date] are less than a year apart. If you want these dates to include more than a year, you will need to add a column to Query1 that gives you the incident_year. HTH Dale "tlyczko" wrote: Hello, I hope someone may help shed light on setting up this query. I have two tables: Incidents, with fields IncidentID (PK), IncidentDate, other fields describing each incident IncidentsConsumers, 1-M table with fields ConsumerID (PK), IncidentID (FK) I'm trying to figure out a good way to get how many consumers have 9 or more incidents during any given period of time, per quarter. I need to have a display like so for example with how many consumers have 9+ incidents: Current Quarter Previous Quarter 2 consumers 0 consumer I can readily find which individual consumers have what counts of incidents per quarter with a crosstab query, but how do I go one step further and just get the COUNT of consumers with number of incidents 8?? I'll keep working at it, I have some ideas that I can work with and kludge up something but I was wondering if there is some query solution that I don't know about. Thank you, Tom |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error when running Query, but not Report | Blaze | Running & Setting Up Queries | 29 | September 9th, 2005 05:40 AM |
Survey Results | SAm | Running & Setting Up Queries | 10 | May 17th, 2005 08:32 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Pivot Table Access 2000? | Air-ron | General Discussion | 2 | October 29th, 2004 06:19 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |