Can I "flatten" out a table

[Guest]

I have a table with W-2 earnings for employees. For each person, I have one
record for each year that they have W-2 earnings. I would like to create a
new table that would just have one record for each person, and a separate
field for each year's W-2. Not everybody has the same number of W-2 pays, so
I need a null for the year's when a person doesn't have W-2 earninings.

My table looks like this:

SSN Year W2
123456789 2002 45,000.00
123456789 2003 48,000.00
123456789 2004 51,000.00
123456789 2005 54,000.00
987654321 2004 32,000.00
987654321 2005 38,000.00

I want to create a new table that looks like this:

SSN W2-2002 W2-2003 W2-2004 W2-2005
123456789 45,000.00 48,000.00 51,000.00 54,000.00
987654321 32,000.00 38,000.00

Is there a way I can do this with a query or SQL statement?

Thanks in advance for any help you can provide.

 

[Duane Hookom]

You can create the look using a crosstab query. Do you have a good reason to
create a new table rather than just using a query?

 

[Marshall Barton]

I have a table with W-2 earnings for employees. For each person, I have one
record for each year that they have W-2 earnings. I would like to create a
new table that would just have one record for each person, and a separate
field for each year's W-2. Not everybody has the same number of W-2 pays, so
I need a null for the year's when a person doesn't have W-2 earninings.

My table looks like this:

SSN Year W2
123456789 2002 45,000.00
123456789 2003 48,000.00
123456789 2004 51,000.00
123456789 2005 54,000.00
987654321 2004 32,000.00
987654321 2005 38,000.00

I want to create a new table that looks like this:

SSN W2-2002 W2-2003 W2-2004 W2-2005
123456789 45,000.00 48,000.00 51,000.00 54,000.00
987654321 32,000.00 38,000.00

Is there a way I can do this with a query or SQL statement?

You really don't that kind of thing in a table. Whenever
you need to see the data in that kind of arrangement, use a
query. Create a new query using the Crosstab wizard and it
should be fairly obvious how to get those results.