James, Something like this can be done with some complicated SQL, but the easy way to do it is with a crosstab report. do you always have one and only one compliance value for each combination of fk_id and year? Is compliance a text value? (I suspect so, because of the left alignment.) If it is text, and there is only one you can use a MIN or MAX function in the crosstab specification. If it's integer, you can use SUM or AVG. (If there is only one, the sum or the average are both the same as the value.)
Look in the help for the topic "Designing Crosstab Reports." Bill James Hageman wrote: > I have a dataset as follows: > > ____________________________________ > comp_id fk_id year compliance > 1 185 2003 97 > 2 185 2004 100 > 3 185 2005 100 > 4 186 2003 96 > 5 186 2004 97 > 6 186 2005 98 > 7 187 2003 56 > 8 187 2004 78 > 9 187 2005 98 > ------------------------------------- > > I want to get it into the following format temporarily to show in a report: > > ______________________________________________________________ > fk_id current_year(2005) current-1(2004) current-2(2003) > 185 100 100 97 > 186 98 97 96 > 187 98 78 56 > ----------------------------------------------------------------- > > Can I do this in a VIEW with a SELF-JOIN or oth SQL command or do I need > to run it through some code in a WHILE loop? > > james > > >
