CrossTab Query?

  • Thread starter Thread starter knowshowrosegrows
  • Start date Start date
K

knowshowrosegrows

OK my brilliant pretties
Riddle me this

The fields:
IncidentDate
Region
Agency
CareItensity
IncidentCategory
IncidentSubCategory


The dream:
A report that lets me:
Choose a time frame
Gives me monthly total of incidents
By Region/Agency/CareIntensity
For IncidentCategory and IncidentSubCategory

Like this -
January
Region = 1
Agency = Bob's Agency
CareIntensity = Residential
IncidentCategory = Death = Total
IncidentSubCategory = Homicide = Total
 
January
Region 1
Bob's Agency
Residential
Death
Homicide = 164
Suicide = 12
Natural Causes = 408
Serious Crime Alleged
Involving FireArms = 7
Physical Assualt = 91
Sue's Agency
Residential
Missing Client
Not Dangerous = 14
 
You misunderstood. Need table and field names and actual exanmple of data
stored in those fields.
ActionDate Region IncidentType Rating Etc
1/1/08 5 4 3 A
1/2/08 2 S 8 Z
12/23/07 7 X Q 1

Rating --
Rating - Code
9 Serious Crime
2 Not Dangerous

IncidentType --
Type Incident
1 Death
2 Limb Loss
3 Fracture
4 Stiches
5 Band-aid
 
IncidentDate MonitoringRegion Agency CareInt InitialIncidentCategory InitialIncidentSubCategory
IncidentDate Region Agency CareInt Category SubCategory
01/01/00 3 SMHA 2 1 1C
01/02/00 5 CNV 1 2 2B
01/04/00 2 RVS 3 1 Homicide
01/04/00 5 GWMA 3 3 Not dangerous

CareInt
1 Residential
2 Case Management
3 Outpatient

Category
1 Death
2 Crime
3 Missing

Subcategory
1A Homicide
1B Suicide
1C Accident

2A Firearms
2B Assault
2C Other
etc
 
This might do what you want. Check the table and field names.

TRANSFORM Count([Category]) & " - " & Count([Subcategory]) AS Expr1
SELECT Format([IncidentDate],"mmmm yyyy") AS [Year and Month],
knowshowrosegrows.MonitoringRegion, knowshowrosegrows.Agency,
CareInt.CareInt, Count(knowshowrosegrows.InitialIncidentCategory) AS [Total
Of InitialIncidentCategory]
FROM ((knowshowrosegrows INNER JOIN CareInt ON knowshowrosegrows.CareInt =
CareInt.CareID) INNER JOIN Category ON
knowshowrosegrows.InitialIncidentCategory = Category.CategoryID) INNER JOIN
Subcategory ON knowshowrosegrows.InitialIncidentSubCategory =
Subcategory.SubcategoryID
GROUP BY Format([IncidentDate],"yyyy mm"), Format([IncidentDate],"mmmm
yyyy"), knowshowrosegrows.MonitoringRegion, knowshowrosegrows.Agency,
CareInt.CareInt
PIVOT [Category] & " - " & [Subcategory];
 
Back
Top