Pivot Query (1 Viewer)

[tonez90]

Hi all,

I have an accident database that I am trying to write a pivot query for.
the following works:
TRANSFORM Nz(Count([I_BA]),0) AS [The Value]
SELECT TblMonthlyInjurySummary.I_BA
FROM TblMonthlyInjurySummary
WHERE ((TblMonthlyInjurySummary.I_Date) Between #7/1/2000# And #6/30/2011#)
GROUP BY TblMonthlyInjurySummary.I_BA
PIVOT IIf([ReportMonth]<=7,Cstr([ReportYear]-1) & " to " & Cstr([Reportyear]),Cstr([ReportYear]) & " to " & Cstr([ReportYear]+1));

which gives the numbers down the left column and years accross the top.
The I_BA is a number for a breakdown agency (i.e. 1- hit by obect), ReportYear is the year it occurred in (which comes from the I_DAtE when it is entered), I_DAte is actuallu the date the incident occurred.

What I would like is the years down the left and the grouped BA across the top. (The above SQL works but I cant figure out how to rotate the table around.)

PS I am not very good at SQL especially Pivot tables (somebody else helped me with the above query)

Any ideas?

cheers
Tony

 

[stopher]

Try:

TRANSFORM Nz(Count([I_BA]),0) AS [The Value]
SELECT IIf([ReportMonth]<=7,CStr([ReportYear]-1) & " to " & CStr([Reportyear]),CStr([ReportYear]) & " to " & CStr([ReportYear]+1)) AS Period FROM TblMonthlyInjurySummary
WHERE (((TblMonthlyInjurySummary.I_Date) Between #7/1/2000# And #6/30/2011#))
GROUP BY IIf([ReportMonth]<=7,CStr([ReportYear]-1) & " to " & CStr([Reportyear]),CStr([ReportYear]) & " to " & CStr([ReportYear]+1))
PIVOT TblMonthlyInjurySummary.I_BA

I've also added the word Period as a heading for your row.

Have you tried working with the pivot table in the query designer? The designer makes it easy to identify rows and columns without having to worry about the SQL.

Chris

 

[tonez90]

Works like a dream. Thanks for help.

No I havent used the query designer. I am using AC2003 and will soon be upgrading to 2010 version.

Another question related to the same Query.

I use the I_BA codes in this query. I have a reference table which has the desrciption of the I_BA value (i.e. 1 = "Ears") how would I substitute these into the table headers. The ref table is called [ref_BA] and contains [BACode] (which is the number) and [BADesc] which is the descriptor (i.e. if the pivot table column heaer equals 1 I wish to place the "Ears" as the column header.

Also how can I have the user enter the dates. I have tried:
WHERE (((TblMonthlyInjurySummary.I_Date) Between [Enter Start Date] And [Enter End Date]))

But it says it does not recognise [Enter Start Date] as a vali field or expression???? What a I doing wrong here.

Thanks in advance
Tony