Merge multiple records in the same table in access

[cubew00t]

Hello I am dealing with some unfriendly import files which import as:

timestamp position name
001 2 Jon
001 3 Bob
001 1 Ann
001 4 Mike
002 1 Joe
002 2 Sue
003 1 Jeff
004 5 James
004 1 Andy
004 2 Beth
004 4 Mitch
004 3 Chris

And would like to create a new table that displays thusly:

timestamp position1 position2 position3 position4 position5
001 Ann Jon Bob Mike
002 Joe Sue
003 Jeff
004 Andy Beth Chris Mitch James

By browsing this forum the closest I have come to a solution is:

SELECT pos1.timestamp, pos1.name AS position1, pos2.name AS position2
FROM table1 AS pos1 INNER JOIN table1 AS pos2
ON pos1.timestamp = pos2.timestamp
WHERE (((pos1.position)=1) AND ((pos2.position)=2))

I cannot figure out how to expand this to my specs, any help is much
appreciated.


I don't understand generalities (having started on access today). Can you be
specific as to my particular situation and what I need to do the get the
desired output. Thank you.

 

[John Spencer]

Use a crosstab query to get the data in that format. Assumption is that there
is no duplication of the combination of timestamp and position

TRANSFORM First([Name]) as TheName
SELECT Timestamp
FROM YourTable
GROUP BY Timestamp
PIVOT Position

In query design view
== Add your table
== Add Timestamp, Position, and Name fields
== Select Query: Crosstab from the menu
== Change Group by to First under the name field
== Select Value under the name field
== Select Row under timestamp
== Select Column under position


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County