Crosstab Query

[Aoife]

Probably doing something daft, I'm attempting to pull data in the following crosstab so that only one row of data (date) appears.

All good when the data is in one year, problem occurs when data falls in eg both 2014 and 2015.

Is there any way of adapting this query so that it simply groups/totals all dates for each of the Incident types?

 [SIZE=3][FONT=Calibri]TRANSFORM Count(UnderlyingSelectQuery.Incident) AS CountOfIncident[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]SELECT Format([DateOfIncident],"yyyy") AS Expr1[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]FROM UnderlyingSelectQuery[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]GROUP BY Format([DateOfIncident],"yyyy")[/FONT][/SIZE]
 [FONT=Calibri][SIZE=3]PIVOT AnnualRunningTotalBEFOREDateQuery.Incident In ("Type1","Type2","Type3”);[/SIZE][/FONT]

 

[Ranman256]

bring in INCIDENT into the query.

 

[Aoife]

Hi, don't quite follow as incident is in both crosstab and underlying query.

 

[Aoife]

Decided to avoid altering the crosstab and thought I'd approach the issue using a sum function on a form. This appears to work, thanks