Intro to Access question

HI - I have some duplicate records that I would like to combine. For example:

Fields

Salesman Locations

Joe Smith Virginia
Joe Smith Texas

I want to make this

Salesman Location1 Location2 Location3 etc

Joe Smith Virginia Texas

Thanks

A possible solution is to write a sub which:
after sorting the table by Salesman name, steps from record to record and if the same salesman name is found then steps back to the first occurrence of that name and puts the city name into the corresponding location field.
When running the cycle, you need to use a counter that records the number of occurrence of a particular name to determine:
- the number of records you need to step back to find the first occurrence of that name
- the location field to put the city name into