Grouping by Years in Columns

[Paige]

I have a database set up like this:

Hospital, year, indicator1, indicator2, indicator3,

I want to see a report like this:
Year1 Year2 Year3
Hospital
Indicator 1
Indicator 2
Indicator 3


It looks simple but I am having a brain blockage on
this. Do I need to define my table differently or my
query?

Help!

 

[Duane Hookom]

You would first need to normalize your table by using a union query:
SELECT Hospital, Year, Indicator1 As TheValue, 1 as Indicator
FROM tblSpreadsheet
UNION ALL
SELECT Hospital, Year, Indicator2 , 2
FROM tblSpreadsheet
UNION ALL
SELECT Hospital, Year, Indicator3 , 3
FROM tblSpreadsheet
UNION ALL
....etc...

You can then create a crosstab based on the union query that sets the column
heading to
ColHead:"Year" & [Year]
Row Heading:
Hospital
Value
TheValue (first or sum or average or count or whatever)