Represent data with the similar column values to one row

[aalkor]

Hi there,
I am looking for the way to represent data with the similar column value to one row.
A query retrieves 3 records AS IS, but I need one record.
Thanks, for your help

Table:
Name / 1 year / 2 year /3 year

AS IS

Name 1 year 2 year 3 year

1 John Smith A
2 John Smith B


3 John Smith A+
=================================

TO BE

Name 1 year 2 year 3 year

1 John Smith A A+ B

[June7]

Are the numbers 1, 2, 3 associated with each name? If so, a crosstab query could transpose the data. If not, will need VBA code. This tutorial shows how to concatenate the data to one string http://allenbrowne.com/func-concat.html

Otherwise, technique I have used is to write records to a temp table, produce report, then purge the table.

[aalkor]

Thanks June7,
Maybe I was't clear.
I can not use crosstab because it works with numbers only, I need letters.
I should put every mark under separate year.
1 year / 2 year / 3 year
John Smith A / A+ / B

[June7]

Crosstab can work with text data. Use the First() function. Again, does each group have the numbers 1, 2, 3 in a field?

[aalkor]

June7, Thank you. Now it works fine.