'count' query question

[tlyczko]

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

 

[Guest]

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]

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

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

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