Grouping Horizontally?

[Zippyfrog]

Can you group horizontally in a report instead of vertical? Right now I have a group header, called Worker, then there are years that they have worked, and I want to list the years they work next to their name instead of below. It is sorted first by WorkerID, then by Year.

Example: Right now my report Generates something like this:

Joe Anderson
2000
2001
2002


Can it be made to go:

Joe Anderson 2000 2001 2002

?

Thanks in advance.

 

[Rich]

A crosstab query may be what you're looking for

 

[Zippyfrog]

I tried your suggestion, and it almost works. I can get the cross tab query to list all the years in a row, but I feel like it could be better.

I know that there will be a finite number of years that people can work. So how can I make the first year they work go into the first column, the second field go in the second column, etc.

Right now, there is a column for 1999, and whoever didn't work in 99 has a blank, same with 2000, 2001 etc. And whoever did work that year has the date filled in. But this isn't effective because once 2004 comes along, another column would be added, but I would have to then go to the report and place that new 2004 field on the report.

So is there a way I can make the first year they work go in the first column, regardless of the year it is? Or is the way the cross tab query is designed that it has to be this way?

 

[Zippyfrog]

I'm getting closer. Let me describe more what I am trying to get.

I have a table - tblYearsWorked, with the fields EmployeeID and Year.

So the data in the table looks like this:

EmployeeID Year
1 2000
2 2001
1 2001
1 2002
2 2003
3 2002
3 2003
4 1999


(that is just sample data)

Right now my cross tab query is set up like this:
EmployeeID as row heading, Year as column heading, and year as value.

What is generated is this:

Employee ID___1999_2000_2001_2002_2003
1_________________2000__2001_______2003
2 _______________________2001________2003
3 ___________________________ 2002__2003
4 ___________1999


However, what I want the cross tab query to look like is:

EmployeeID__Year1__Year2_Year3__Year4
1 __________2000___2001__2003
2 __________2001___2003
3 __________2002___2003
4 __________1999___

(I put all those lines there because I can't place a bunch of spaces)
I tried making an expression Year1, Year2 etc. with the field year = Year1, but I a jet error. I hope that makes more sense. I just want to be able to list the years, but I don't want to have a separate field generated being the year that they worked. The maxmimum number of years an employee can work is 4 years, although they need not be consecutive, nor do you have to have exactly 4. So I only want to have 4 fields in all, where all the columns don't have the same value, they are just the first value listed?

What can I do to make my cross tab query look like the bottom as opposed to the top? Do I still need to do a cross tab query or is it something else?