Merge multiple records in the same table in access

Question

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.

Answer 1

Try something like this

TRANSFORM First(Table.[name]) AS FirstOfname
SELECT Table.[timestamp]
FROM [Table]
GROUP BY Table.[timestamp]
PIVOT Table.[position];

I created this using the MS Access Cross Tab Query Wizard.

Also have a look at

What is a CrossTab Query?

Some better explenation.

• Click the Create tab.
• Click the Query Wizard.
• Select Second Option (Crosstab Query Wizard) and hit OK.
• Select the table for your input, and hit next.
• Select Timestamp and click the arrow (single) pointing fromleft to right, and hit next.
• Select position and hit next.
• name will be the remaining field, Select First (default is count), and hit next.
• Hit finish.

It should have saved a query called Table_Crosstab (or something similar). Right click this and select design view. On the view button, select Sql View.

You should see something similar to

TRANSFORM First(Table.name) AS FirstOfname
SELECT Table.timestamp, First(Table.name) AS [Total Of name]
FROM [Table]
GROUP BY Table.timestamp
PIVOT Table.position;

From the second line remove

, First(Table.name) AS [Total Of name]

so that you end up with

TRANSFORM First(Table.name) AS FirstOfname
SELECT Table.timestamp
FROM [Table]
GROUP BY Table.timestamp
PIVOT Table.position;

And that should be it. Save and you are ready.

Answer 2

Your new table is a bad design. What happens when there is a sixth and a seventh person in the incoming data? It will also be difficult to work with when you need to pull out all the data by a specific person as you will then need to query five columns.