Prev: Subform Filter
Next: home
From: cubew00t on 22 Dec 2009 06:22
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.

From: John Spencer on 22 Dec 2009 07:33
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

cubew00t wrote:
> 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.
>
 | 
Pages: 1
Prev: Subform Filter
Next: home